How to append Python dictionary to Pandas DataFrame, matching the key to the column name

user_14500576 0
Viewed 2173

I've looked through old questions and they solve similar problems but not the one I'm trying to figure out. As quick background, I'm ingesting an API that sometimes returns slightly different objects, but I'd like to append them all to the same table.

If the value is not in one of the dictionaries I'd like for that to be left blank in the dataframe

df = pd.DataFrame(columns=['C1','C2','C3','C4'])

r1 = {'C1':20,'C2':15,'C3':10,'C4':53}
r2 = {'C1':47,'C3':26,'C4':17}
r3 = {'C2':31,'C3':64,'C4':29}
r4 = {'C1':64,'C2':17}
r5 = {'C1':45,'C2':24,'C3':71,'C4':63}

How do I loop over r1-5 and append them to the DataFrame like this?

 C1    C2    C3    C4
 20    15    10    53
 47   None   26    17
None   31    64    26
 64    17   None  None
 45    24    71    63

Thanks in advance!

EDIT: To add some additional information, here's something I've tried so far.

I've turned each dictionary into a list of dictionaries and then tried to loop over each of those key:value pairs and use the Key in the Loc function

As an example:

r1 = [{'C1':20},{'C2':15},{'C3':10},{'C4':53}]
pandas dictionary python
1 Answers

You could use loc or append both to do this.

If you want to append each row one by one, then you can do this

df.loc[len(df)] = r1
df.loc[len(df)] = r2
df.loc[len(df)] = r3
df.loc[len(df)] = r4
df.loc[len(df)] = r5

or if you want to append all the rows together, which would be more efficient, you could do this:

rows = [r1,r2,r3,r4,r5]
df = df.append(rows)

Both give you the following output

enter image description here

Edit: based on comment from starmandeluxe
Since .append() is now deprecated you can just use the pandas constructor to create a DataFrame and then use .concat() to append it to your existing database

rows = [r1,r2,r3,r4,r5]
df = pd.concat([df,pd.DataFrame(rows)])
user_51709930
Related