Dictionary to CSV from api

Question

I am pulling data from an API and looks like this.

{'results': {'payroll_report': {'1437252': {'user_id': 1437252,
    'client_id': '606152',
    'start_date': '2021-07-13',
    'end_date': '2021-07-13',
    'total_re_seconds': 14340,
    'total_pto_seconds': 0,
    'total_work_seconds': 14340,
    'total_paid_break_seconds': 0,
    'total_unpaid_break_seconds': 0,
    'pto_seconds': {},
    'overtime_seconds': {'1.5': 0, '2': 0},
    'timesheet_count': 2},
   '1413446': {'user_id': 1413446,
    'client_id': '606152',
    'start_date': '2021-07-13',
    'end_date': '2021-07-13',
    'total_re_seconds': 14160,
    'total_pto_seconds': 0,
    'total_work_seconds': 14160,
    'total_paid_break_seconds': 0,
    'total_unpaid_break_seconds': 0,
    'pto_seconds': {},
    'overtime_seconds': {'1.5': 0, '2': 0},

I want to convert it from this format to a CSV.

response = requests.request("POST", url, data=payload, headers=headers)

df = json.loads(response.text)

is my current code, I cannot get this to work!

user_id,'client_id','start_date','end_date',
    'total_re_seconds',
    'total_pto_seconds',
    'total_work_seconds',
    'total_paid_break_seconds',
    'total_unpaid_break_seconds',
    'pto_seconds',
    'overtime_seconds',
    'timesheet_count'

To be my column headers! Any help would be awesome tia!

user_16431106 · Answer

The values you want is a nested dictionary so you have to specify the on you want from the parent dictionary to be converted to a pandas df

mydict = {'results': {'payroll_report': {'1437252': {'user_id': 1437252, 'client_id': '606152', 'start_date': '2021-07-13', 'end_date': '2021-07-13', 'total_re_seconds': 14340, 'total_pto_seconds': 0, 'total_work_seconds': 14340, 'total_paid_break_seconds': 0, 'total_unpaid_break_seconds': 0, 'pto_seconds': {}, 'overtime_seconds': {'1.5': 0, '2': 0}, 'timesheet_count': 2}, '1413446': {'user_id': 1413446, 'client_id': '606152', 'start_date': '2021-07-13', 'end_date': '2021-07-13', 'total_re_seconds': 14160, 'total_pto_seconds': 0, 'total_work_seconds': 14160, 'total_paid_break_seconds': 0, 'total_unpaid_break_seconds': 0, 'pto_seconds': {}, 'overtime_seconds': {'1.5': 0, '2': 0}}}}}

df = pd.DataFrame(mydict['results']['payroll_report']['1437252'])

print(df)

df.to_csv(r'Path where you want to store the exported CSV file\File Name.csv', index = False)

Dictionary to CSV from api

1 Answers