Python pandas: convert dictionary to DataFrame with keys as row

Question

I want to convert the following dictionary to dataframe:

x = {'a': {'list1': [1, 2, 3, 4], 
           'list2': [5, 6, 7, 8]
          },
     'b': {'list1': [6, 7, 8, 9], 
           'list2': [4, 4, 3, 4]
          },
     'c': {'list1': [6, 45, 234, 4], 
           'list2': [213, 87, 243, 4]
          }
    }

the output dataframe should look like this:

letter   list1             list2
a        [1, 2, 3, 4]      [5, 6, 7, 8]
b        [6, 7, 8, 9]      [4, 4, 3, 4]
c        [6, 45, 234, 4]   [213, 87, 243, 4]

I have tried these methods:

import pandas as pd
pd.DataFrame(x)
                  a             b                  c
list1  [1, 2, 3, 4]  [6, 7, 8, 9]    [6, 45, 234, 4]
list2  [5, 6, 7, 8]  [4, 4, 3, 4]  [213, 87, 243, 4]

Secondly, I tried:

pd.concat({k: pd.Series(v) for k, v in x.items()}).reset_index()

  level_0 level_1                  0
0       a   list1       [1, 2, 3, 4]
1       a   list2       [5, 6, 7, 8]
2       b   list1       [6, 7, 8, 9]
3       b   list2       [4, 4, 3, 4]
4       c   list1    [6, 45, 234, 4]
5       c   list2  [213, 87, 243, 4]

The second way is close but then each "letter" have 2 rows. How do I convert it correctly?

jezrael · Accepted Answer · 2020-06-07 05:42:32Z

5

You can transpose by DataFrame.T with rename index name and convert index by DataFrame.rename_axis to column letter by DataFrame.reset_index:

df = pd.DataFrame(x).T.rename_axis('letter').reset_index()
print (df)
  letter            list1              list2
0      a     [1, 2, 3, 4]       [5, 6, 7, 8]
1      b     [6, 7, 8, 9]       [4, 4, 3, 4]
2      c  [6, 45, 234, 4]  [213, 87, 243, 4]

Or solution with DataFrame.from_dict:

df = pd.DataFrame.from_dict(x, orient='index').rename_axis('letter').reset_index()

answered Jun 7, 2020 at 5:42

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Comments

Tom Ron · Accepted Answer · 2020-06-07 05:44:13Z

3

You can use the orient argument, see here -

pd.DataFrame.from_dict(x, orient='index').rename_axis('letter').reset_index()

  letter            list1              list2
0      a     [1, 2, 3, 4]       [5, 6, 7, 8]
1      b     [6, 7, 8, 9]       [4, 4, 3, 4]
2      c  [6, 45, 234, 4]  [213, 87, 243, 4]

answered Jun 7, 2020 at 5:44

Tom Ron

6,2413 gold badges28 silver badges43 bronze badges

Collectives™ on Stack Overflow

Python pandas: convert dictionary to DataFrame with keys as row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related