looping an object array with pandas-CodePudding

I would like to find another way to loop in an array of objects for this I use pandas to generate my Excel file response.text

{"Header":{"Time":"2022-08-01T01:55:41-07:00","ReportName":"TransactionListByCustomer","StartPeriod":"2016-06-01","EndPeriod":"2016-07-31","Currency":"USD","Option":[{"Name":"NoReportData","Value":"true"}]},"Columns":{"Column":[{"ColTitle":"Date","MetaData":[{"Name":"ColKey","Value":"tx_date"}]},{"ColTitle":"Transaction Type","MetaData":[{"Name":"ColKey","Value":"txn_type"}]},{"ColTitle":"Num","MetaData":[{"Name":"ColKey","Value":"doc_num"}]},{"ColTitle":"Posting","MetaData":[{"Name":"ColKey","Value":"is_no_post"}]},{"ColTitle":"Memo/Description","MetaData":[{"Name":"ColKey","Value":"memo"}]},{"ColTitle":"Account","MetaData":[{"Name":"ColKey","Value":"account_name"}]},{"ColTitle":"Amount ","MetaData":[{"Name":"ColKey","Value":"amount"}]}]},"Rows":{}}

here is my code

response = requests.request("GET", url, headers=headers, data=payload)
list = pd.read_json(response.text)
        
df = pd.DataFrame({
    list['Columns']['Column'][0]['ColTitle']: list['Columns']['Column'][0]['MetaData'][0]['Value'],
    list['Columns']['Column'][1]['ColTitle']: list['Columns']['Column'][1]['MetaData'][0]['Value'], 
    list['Columns']['Column'][2]['ColTitle']: list['Columns']['Column'][2]['MetaData'][0]['Value'],
    list['Columns']['Column'][3]['ColTitle']: list['Columns']['Column'][3]['MetaData'][0]['Value'],
    list['Columns']['Column'][4]['ColTitle']: list['Columns']['Column'][4]['MetaData'][0]['Value'],
    list['Columns']['Column'][5]['ColTitle']: list['Columns']['Column'][5]['MetaData'][0]['Value'],
    list['Columns']['Column'][6]['ColTitle']: list['Columns']['Column'][6]['MetaData'][0]['Value']
    }, index=[0])
df.to_excel('TransactionList.xlsx')

here is the result enter image description here

CodePudding user response：

You can try pd.json_normalize

import json
data = json.loads(response.text)

df = (pd.json_normalize(data['Columns']['Column'], record_path='MetaData', meta='ColTitle')
      .drop(columns='Name')
      .set_index('ColTitle')
      .T)

print(df)

ColTitle     Date Transaction Type      Num     Posting Memo/Description       Account Amount
Value     tx_date         txn_type  doc_num  is_no_post             memo  account_name  amount