select the rows of a table according to an id that is in a JSON in a column of the table

user_14584317 0
Viewed 121

I need to select the rows of a table according to the id In a JSON In one of the columns using Pandas.

example :

column_a column_b column_c
aaaa bbbbb {'id' : cc, 'name' : xx ...}
xxxx yyyy {'id' : ff, 'name' : gg ...}

so I want to select all the rows where the id of the JSON in the column_c is equal to 'cc', So the result will be :

column_a column_b column_c
aaaa bbbbb {'id' : cc, 'name' : xx ...}
pandas dataframe python json
2 Answers

If you have already loaded this into pandas it is probably now a dictionary which can be accessed by key similar to the dataframe so you are looking to filter like this:

df[df['column_c']['id'] == 'cc']
user_148517000

Try this:

import json

def func(x): #x is a string of json format
    s=json.loads(x)
    return s['id']=='cc'

And filter the dataframe by applying the above function to column_c:

df[df['column_c'].apply(lambda x: func(x)]

If this doesn't work, it means that column_c is of a different type. In that case, please provide the type of this column so that i will adjust the solution

user_82285580
Related