1

I am using python3 and pandas version 0.25. I have a JSON datatype in postgresql table. I am using pandas.io.sql to fetch the data from the table.

import pandas.io.sql as psql
df = psql.read_sql(sql,con,params=params)

So I am getting the dataframe from DB call as above.

When I check the output of the df (using IDE), I see the dataframe with following: DF Output

Now, when I try to see the dtypes of the columns I see "object" for both the columns.

I want to update the columns and append value of the json, so it becomes: DF modified

I am unable to do the same.

I tried doing the below:

df.loc[:, 'col2_data'] = df.apply(lambda row: row['col2_data'].append({'multiplier':'2'}), axis=1)

But after the above statement, it is giving None for col2_data. i.e. is it not working.

Can someone help here?

Mihir
  • 531
  • 2
  • 10
  • 35

1 Answers1

1

Since each value is a list, we can use .update method of a dictionary to add new value. This might work in your case:

Method 1

df.loc['col2_data'] = df.apply(lambda row: [x.update({'multiplier':'2'}) for x in row['col2_data']], axis=1)

Method 2

df.loc['col2_data'] = df.apply(lambda row: [{**x, **{'multiplier':'2'}} for x in row['col2_data']], axis=1)
YOLO
  • 20,181
  • 5
  • 20
  • 40
  • It is strange. So, when I tried exactly as above it didn't work. In a sense it updated the value of col2_data as [None] (row 1) and [None,None] (row2). However, when I tried below (just tried to create the new column: df.loc['col2_data_new'] = df.apply(lambda row: [x.update({'multiplier':'2'}) for x in row['col2_data']], axis=1) The new column, col2_data_new is exactly like above i.e. [None] (row 1) and [None,None] (row2). However, old columnd col2_data now had the new attribute added somehow!! Again, I am not sure, is it because .update or .append method does not work on dtype object – Mihir Jan 28 '20 at 20:54
  • `.update` method returns none and updates the dictionary in place. – YOLO Jan 28 '20 at 21:00
  • Thanks @YOLO. Method 2 worked for me. Can you please explain usage here, i.e. double asterik's use or point to documentation for reference? – Mihir Jan 28 '20 at 21:13
  • glad to know, here you can read: https://stackoverflow.com/questions/5710391/converting-python-dict-to-kwargs – YOLO Jan 28 '20 at 21:16
  • Thanks a lot. I have some related question, I have asked the separate question. If you can help, it will be great, thanks. https://stackoverflow.com/questions/59969590/pandas-groupby-and-aggregate-multiple-columns-of-type-json-float – Mihir Jan 29 '20 at 14:45