3

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}]
9769953
  • 10,344
  • 3
  • 26
  • 37
ortunoa
  • 345
  • 4
  • 11
  • Check `pd.DataFrame([r1, r2, r3, r4, r5])` – Shubham Sharma Apr 29 '21 at 13:54
  • Thanks, the only issue is that sometimes I'll only get one response that doesn't have all columns. And what I'm doing with that DataFrame is writing it into SQL. For example if I were to only write r2, how can I still "force" it to have all C1,C2,C3 & C4? – ortunoa Apr 29 '21 at 13:58
  • `df = df.append(r2, ignore_index=True)`. – 9769953 Apr 29 '21 at 13:59
  • Does this answer your question? [append dictionary to data frame](https://stackoverflow.com/questions/51774826/append-dictionary-to-data-frame) – 9769953 Apr 29 '21 at 14:00
  • 1
    You can pass the columns explicitly `pd.DataFrame([r2], columns=['C1','C2','C3','C4'])` or you can `append` `r2` to the existing empty dataframe created with the required columns. – Shubham Sharma Apr 29 '21 at 14:01

1 Answers1

2

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)])
dracarys
  • 1,071
  • 1
  • 15
  • 31