CODEWITHSUNDEEP
pythonpython-3.xpandas
Mihir
Mihir

Reputation: 603

Python: Modify the json type column in pandas dataframe

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?

Upvotes: 1

Views: 2701

Answers (1)

YOLO
YOLO

Reputation: 21709

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)

Upvotes: 1

Related Questions