Extract a value from a JSON string stored in a pandas data frame column

Question

I have a pandas dataframe with a column named json2 which contains a json string coming from an API call:

"{'obj': [{'timestp': '2022-12-03', 'followers': 281475, 'avg_likes_per_post': 7557, 'avg_comments_per_post': 182, 'avg_views_per_post': 57148, 'engagement_rate': 2.6848}, {'timestp': '2022-12-02', 'followers': 281475, 'avg_likes_per_post': 7557, 'avg_comments_per_post': 182, 'avg_views_per_post': 57148, 'engagement_rate': 2.6848}]}"

I want to make a function that iterates over the column and extracts the number of followers if the timestp matches with a given date

def get_followers(x):
    if x['obj']['timestp']=='2022-12-03':
        return x['obj']['followers']

df['date'] = df['json2'].apply(get_followers)

I should get 281475 as value in the column date but I got an error: "list indices must be integers or slices, not str"

What I'm doing wrong? Thank you in advance

Bushmaster · Accepted Answer · 2022-12-03 22:14:46Z

1

The key named obj occurs in list of dictionaries. Before you define another key, you must also specify the index of the list element.

import ast
df['json2']=df['json2'].apply(ast.literal_eval) #if dictionary's type is string, convert to dictionary.

def get_followers(x):
    if x['obj'][0]['timestp']=='2022-12-03':
        return x['obj'][0]['followers']

df['date'] = df['json2'].apply(get_followers)

Also you can use this too. This does the same job as the function you are using:

df['date'] = df['json2'].apply(lambda x: x['obj'][0]['followers'] if x['obj'][0]['timestp']=='2022-12-03' else None)

for list of dicts:

def get_followers(x):
    for i in x['obj']:
        if i['timestp'] == '2022-12-03':
            return i['followers']
            break
    
df['date'] = df['json2'].apply(get_followers)

edited Dec 3, 2022 at 22:14

answered Dec 3, 2022 at 21:47

Bushmaster

4,6364 gold badges11 silver badges34 bronze badges

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

3 Comments

Isabel Lopez Over a year ago

Thank you so much! It is not working, I get "None" as result, but I presume this is because the date is not in the first element of the list of dictionaries.

Bushmaster Over a year ago

if there are multiple dictionaries, a for loop can be created to search all dictionaries. Do you want me to edit the answer accordingly?

Isabel Lopez Over a year ago

Your solution works perfectly! Big kudos!

Collectives™ on Stack Overflow

Extract a value from a JSON string stored in a pandas data frame column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related