How to convert python dictionary to pandas dataframe

Question

I have the following data that I want convert into pandas dataframe Input

my_dict = {'table_1': [{'columns_1': 148989, 'columns_2': 437643}], 'table_2': [{'columns_1': 3344343, 'columns_2': 9897833}]}

Expected Output

   table_name      columns_1      columns_2  
      table_1         148989         437643      
      table_2        3344343        9897833

I tried below way but due to the loop, i can only get the last value

def convert_to_df():
  for key, value in my_dict.items():
    df = pd.DataFrame.from_dict(value, orient='columns')
    df['table_name'] = key
    
  return df

What I'm I missing?

The thing you're "missing" is that you're overwriting the value of df on each iteration of the loop. In any case, the loop is not necessary, as shown in the answer already posted. — Dave Costa
– Dave Costa, Commented Apr 5, 2022 at 13:35

Sunderam Dubey · Accepted Answer · 2022-05-30 16:13:09Z

4

Just get rid of those lists and you can feed directly to the DataFrame constructor:

pd.DataFrame({k: v[0] for k,v in my_dict.items()}).T

output:

         columns_1  columns_2
table_1     148989     437643
table_2    3344343    9897833

With the index as column:

(pd.DataFrame({k: v[0] for k,v in my_dict.items()})
   .T
   .rename_axis('table_name')
   .reset_index()
)

output:

  table_name  columns_1  columns_2
0    table_1     148989     437643
1    table_2    3344343    9897833

edited May 30, 2022 at 16:13

Sunderam Dubey

8,83512 gold badges25 silver badges43 bronze badges

answered Apr 5, 2022 at 13:30

mozway

267k13 gold badges56 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Niels Henkens · Accepted Answer · 2022-04-05 13:35:58Z

1

Not the nicest way imho (mozway's method is nicer), but to continue on the road you tried, you need to add the output of your for loop to a list and then concat that into 1 dataframe.

def convert_to_df():
    df_list = []  #Add a list where the output of every loop is added to
    for key, value in my_dict.items()
        df = pd.DataFrame.from_dict(value, orient='columns')
        df['table_name'] = key
        df_list.append(df)  #Append to the list
    df = pd.concat(df_list)  # Concat list into dataframe
    return df

df = convert_to_df()

answered Apr 5, 2022 at 13:35

Niels Henkens

2,7161 gold badge14 silver badges29 bronze badges

1 Comment

mozway Over a year ago

Shorter variant: pd.concat([pd.DataFrame.from_dict(value, orient='columns').assign(table_name=key) for key, value in my_dict.items()])

Collectives™ on Stack Overflow

How to convert python dictionary to pandas dataframe

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related