CODEWITHSUNDEEP
pythonjsonpandas
Kshitij Marwah
Kshitij Marwah

Reputation: 1151

Add json elements to a pandas data frame

I have a pandas df which I have created. The structure of the df is as follows :-

  A  B  C  D
0  a  b  c  NaN
2  x  y  z  NaN
.
.

Now also have a list list1 which has json as elements like

[{a:1,b:2},{c:1,d:2},....]

I would like to add the elements in my list to the json pandas df so that my df looks like

  A  B  C  D
0  a  b  c  {a:1,b:2}
2  x  y  z  {c:1,d:2}
.
.

when i did

df['D'].iloc[0] = list[0]

it gave me no index named error. What wrong am I doing here?

Upvotes: 1

Views: 5514

Answers (1)

jezrael
jezrael

Reputation: 862551

Solutions if length of list1 is same as length of DataFrame:

You need create Series first with same index as df and then assign to new column:

print (pd.Series(list1, index=df.index))
0    {'b': 2, 'a': 1}
2    {'d': 2, 'c': 1}
dtype: object

df['D'] = pd.Series(list1, index=df.index)
print (df)
  A  B  C                 D
0  a  b  c  {'b': 2, 'a': 1}
2  x  y  z  {'d': 2, 'c': 1}

Another solution with DataFrame.assign:

df = df.assign(D=pd.Series(list1, index=df.index))
print (df)
   A  B  C                 D
0  a  b  c  {'b': 2, 'a': 1}
2  x  y  z  {'d': 2, 'c': 1}

Solution for comment, thank you Nickil Maveli:

df.loc[:, 'D'] = list1

Or better:

df['D'] = list1

print (df)
   A  B  C                 D
0  a  b  c  {'b': 2, 'a': 1}
2  x  y  z  {'d': 2, 'c': 1}

If lenghts are different, it is a bit more complicated - need select by position by length of df.index and by length of list1:

print (df)
   A  B  C   D
0  a  b  c NaN
2  x  y  z NaN
5  y  e  t NaN

list1 = [{'a':1,'b':2},{'c':1,'d':2}]
df['D'] = pd.Series(list1[:len(df.index)], index=df.index[:len(list1)])
print (df)
   A  B  C                 D
0  a  b  c  {'b': 2, 'a': 1}
2  x  y  z  {'d': 2, 'c': 1}
5  y  e  t               NaN

print (df)
   A  B  C   D
0  a  b  c NaN
2  x  y  z NaN
5  y  e  t NaN

list1 = [{'a':1,'b':2},{'c':1,'d':2}, {'a':1,'b':2},{'c':1,'d':2}]
df['D'] = pd.Series(list1[:len(df.index)], index=df.index[:len(list1)])
print (df)
   A  B  C                 D
0  a  b  c  {'b': 2, 'a': 1}
2  x  y  z  {'d': 2, 'c': 1}
5  y  e  t  {'b': 2, 'a': 1}

Upvotes: 2

Related Questions