Save dictionary to Pandas dataframe with keys as columns and merge indices

Question

I know there are already lots of posts on how to convert a pandas dict to a dataframe, however I could not find one discussing the issue I have. My dictionary looks as follows:

[Out 23]:
{'atmosphere':       0
 2     5
 9     4
 15    1
 26    5
 29    5
 ...  ..
 2621  4
 6419  3
 
 [6934 rows x 1 columns],
 'communication':       0
 13    1
 15    1
 26    1
 2621  2
 3119  5
 ...  ..
 6419  4
 6532  1
 
 [714 rows x 1 columns]

Now, what I want is to create a dataframe out of this dictionary, where the 'atmosphere' and 'communication' are the columns, and the indices of both items are merged, so that the dataframe looks as follows:

index    atmosphere    commmunication
2           5
9           4
13                           1
15          1                1
26          5                1
29          5
2621        4                2
3119                         5
6419        3                4
6532                         1

I already tried pd.DataFrame.from_dict, but it saves all values in one row. Any help is much appreciated!

@jezrael: https://stackoverflow.com/questions/30233982/merge-dataframes-in-a-dictionary — mozway, Apr 05 '22 at 08:10

jezrael · Accepted Answer · 2022-04-05T08:27:14.700

2

Use concat with DataFrame.droplevel for remove second level 0 from MultiIndex in columns:

d = {'atmosphere':pd.DataFrame({0: {2: 5, 9: 4, 15: 1, 26: 5, 29: 5, 
                                    2621: 4, 6419: 3}}),
     'communication':pd.DataFrame({0: {13: 1, 15: 1, 26: 1, 2621: 2,
                                       3119: 5, 6419: 4, 6532: 1}})}

print (d['atmosphere'])
      0
2     5
9     4
15    1
26    5
29    5
2621  4
6419  3

print (d['communication'])
      0
13    1
15    1
26    1
2621  2
3119  5
6419  4
6532  1

df = pd.concat(d, axis=1).droplevel(1, axis=1)
print (df)
      atmosphere  communication
2            5.0            NaN
9            4.0            NaN
13           NaN            1.0
15           1.0            1.0
26           5.0            1.0
29           5.0            NaN
2621         4.0            2.0
3119         NaN            5.0
6419         3.0            4.0
6532         NaN            1.0

Alternative solution:

df = pd.concat({k: v[0] for k, v in d.items()}, axis=1)

edited Apr 05 '22 at 08:27

answered Apr 05 '22 at 08:03

jezrael

822,522
95
1,334
1,252

I tried but I receive "TypeError: cannot concatenate object of type ''; only Series and DataFrame objs are valid" – Freyana Apr 05 '22 at 08:13
@Freyana - in question is dictionary `{'atmosphere': 0 2 5...`, in your real data it is different? – jezrael Apr 05 '22 at 08:16
in my real data I have 10 more keys apart from atmosphere and communication, but the content is of the same type (Index value & integer) – Freyana Apr 05 '22 at 08:19
@Freyana - I add sample data, can you check if your are different? – jezrael Apr 05 '22 at 08:21
my output looks exactly the same ... But as I'm a beginner I don't know whether I might get sth wrong so in case you have time I uploaded my py-file and the data I'm using to this folder: https://1drv.ms/u/s!Arkw7Y7i28Jikyvvs4Z9Ec5rdccR?e=atqNEg – Freyana Apr 05 '22 at 08:28
@Freyana - what is ouput of `print(dct['Vorgesetztenverhalten'])` ? – jezrael Apr 05 '22 at 08:33
[ 0 75 3 90 3 958 3 1133 1 1235 1 ... .. 4530 1 4531 1 4532 4 4533 1 4534 1 [3935 rows x 1 columns]] – Freyana Apr 05 '22 at 08:36
same style as the output of the other keys – Freyana Apr 05 '22 at 08:36
@Freyana - so `print (type((dct['Vorgesetztenverhalten'])))` is list? – jezrael Apr 05 '22 at 08:37
@Freyana - seems used`dct = {k:[v] for k,v in dct.items()}` ? – jezrael Apr 05 '22 at 08:38
Yes it says: – Freyana Apr 05 '22 at 08:38
@Freyana - and `print (len((dct['Vorgesetztenverhalten'])))` ? – jezrael Apr 05 '22 at 08:39
1

Omg, yes, absolutely my mistake!!! Forgot that I ran the above code and changed dct because I tried another solution first. Now it works!! Thank you so much for your time and sorry for my sloppiness!! – Freyana Apr 05 '22 at 08:40

score 0 · Answer 2 · answered Apr 05 '22 at 08:21

You can use pandas.concat on the values and set_axis with the dictionary keys:

out = pd.concat(d.values(), axis=1).set_axis(d, axis=1)

output:

      atmosphere  communication
2            5.0            NaN
9            4.0            NaN
13           NaN            1.0
15           1.0            1.0
26           5.0            1.0
29           5.0            NaN
2621         4.0            2.0
3119         NaN            5.0
6419         3.0            4.0
6532         NaN            1.0

Save dictionary to Pandas dataframe with keys as columns and merge indices

2 Answers2