How do I convert a list/dictionary into a Dataframe?

Chris B. Published at Dev

Chris B.

I have a JSON response (sample below) that I'm trying to convert into a DataFrame. I've had several issues with the data being listed as columns (1 x 346), etc. I only need the 5 columns listed below:

area_name,
date,
month,
unemployment_rate,
year

Here's my code:

edd_ca_df = pd.DataFrame.from_dict(edd_ca, orient="index", 
                                   columns=["area_name", "month", "date", "year", "unemployment_rate"])

and here's a sample of the JSON response:

[[{'area_name': 'California',
   'area_type': 'State',
   'date': '1990-01-01T00:00:00.000',
   'employment': '14099700',
   'labor_force': '14953900',
   'month': 'January',
   'seasonally_adjusted_y_n': 'N',
   'status_preliminary_final': 'Final',
   'unemployment': '854200',
   'unemployment_rate': '5.7',
   'year': '1990'},

 {'area_name': 'California',
   'area_type': 'State',
   'date': '1990-02-01T00:00:00.000',
   'employment': '14206700',
   'labor_force': '15049400',
   'month': 'February',
   'seasonally_adjusted_y_n': 'N',
   'status_preliminary_final': 'Final',
   'unemployment': '842800',
   'unemployment_rate': '5.6',
   'year': '1990'},

Any help would be greatly appreciated.

b-fg

Since you have a list of dictionaries, this is as simple as passing all the data to a new DataFrame and specifying what columns you want to keep:

import pandas as pd

all_data = [{'area_name': 'California',
   'area_type': 'State',
   'date': '1990-01-01T00:00:00.000',
   'employment': '14099700',
   'labor_force': '14953900',
   'month': 'January',
   'seasonally_adjusted_y_n': 'N',
   'status_preliminary_final': 'Final',
   'unemployment': '854200',
   'unemployment_rate': '5.7',
   'year': '1990'},

 {'area_name': 'California',
   'area_type': 'State',
   'date': '1990-02-01T00:00:00.000',
   'employment': '14206700',
   'labor_force': '15049400',
   'month': 'February',
   'seasonally_adjusted_y_n': 'N',
   'status_preliminary_final': 'Final',
   'unemployment': '842800',
   'unemployment_rate': '5.6',
   'year': '1990'}]

keep_columns = ['area_name','date','month','unemployment_rate','year']
df = pd.DataFrame(columns=keep_columns, data=all_data)

print(df)

Output

    area_name                     date     month unemployment_rate  year
0  California  1990-01-01T00:00:00.000   January               5.7  1990
1  California  1990-02-01T00:00:00.000  February               5.6  1990

Collected from the Internet

Please contact javaer1[email protected] to delete if infringement.