Combining rows and merging columns of multiple Python dictionaries

Question

I have three dictionaries (more actually) in Python that I need to "combine rows and merge columns" into a Pandas dataframe, although merge might not be the correct term here. My question is best illustrated by example. The three input dictionaries are:

a = { 'name': name1, 'one': 123, 'two':456, 'three': 789 }
b = { 'name': name2, 'ten': 1230, 'eleven':4560, 'twelve': 7890 }
c = { 'name': name3, 'fifty': 12300, 'sixty':45600, 'seventy': 78900 }

I need to "combine and merge" these into an output list of dictionaries to looks like:

       one     two    three   ten     eleven  twelve   fifty   sixty   seventy
name1  123     456    789      0      0       0        0       0       0
name2  0       0      0        1230   4560    7890     0       0       0
name3  0       0      0        0      0       0        12300   45600   78900

Are there any Pandas methods to accomplish this "combine and merge"? Or would the best approach be to build a python ordered list of dictionaries and manually manage the columns?

Any point

score 3 · Accepted Answer · answered Nov 05 '21 at 17:22

3

You can simply use the pd.DataFrame constructor:

df = pd.DataFrame([a,b,c]).fillna(0)

>>> df
    name    one    two  three  ...  twelve    fifty    sixty  seventy
0  name1  123.0  456.0  789.0  ...     0.0      0.0      0.0      0.0
1  name2    0.0    0.0    0.0  ...  7890.0      0.0      0.0      0.0
2  name3    0.0    0.0    0.0  ...     0.0  12300.0  45600.0  78900.0

answered Nov 05 '21 at 17:22

not_speshal

22,093
2
15
30

also given input data, would recommend `dtype=int` kwarg – Joshua Voskamp Nov 05 '21 at 17:23
I think that the index should be the "name" column. Just reindex it. – Romero_91 Nov 05 '21 at 17:24

score 1 · Answer 2 · answered Nov 05 '21 at 17:21

You can use pd.json_normalize:

print (pd.json_normalize([a,b,c]).fillna(0))

    name    one    two  three     ten  eleven  twelve    fifty    sixty  seventy
0  name1  123.0  456.0  789.0     0.0     0.0     0.0      0.0      0.0      0.0
1  name2    0.0    0.0    0.0  1230.0  4560.0  7890.0      0.0      0.0      0.0
2  name3    0.0    0.0    0.0     0.0     0.0     0.0  12300.0  45600.0  78900.0

Combining rows and merging columns of multiple Python dictionaries

2 Answers2