Python: Modify the json type column in pandas dataframe

Question

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:

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:

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?

YOLO · Accepted Answer · 2020-01-28 20:59:26Z

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)

edited Jan 28, 2020 at 20:59

answered Jan 28, 2020 at 20:24

YOLO

22k5 gold badges25 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mihir Over a year ago

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

YOLO Over a year ago

.update method returns none and updates the dictionary in place.

Mihir Over a year ago

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?

YOLO Over a year ago

glad to know, here you can read: stackoverflow.com/questions/5710391/…

Mihir Over a year ago

Thanks a lot. I have some related question, I have asked the separate question. If you can help, it will be great, thanks. stackoverflow.com/questions/59969590/…

Collectives™ on Stack Overflow

Python: Modify the json type column in pandas dataframe

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related