Pandas - Extract value from dictionary in a df from a column containing list of dictionaries-CodePudding

I have the below that I've created a df with:

data_response = [{'name': 'impressions', 'values': [{'value': 7352}], 'id': '179286/insights/impressions/lifetime'}, {'name': 'reach', 'values': [{'value': 7352}], 'id': '179286/insights/reach/lifetime'}, {'name': 'taps_forward', 'values': [{'value': 6280}], 'id': '179286/insights/taps_forward/lifetime'}, {'name': 'taps_back', 'values': [{'value': 134}], 'id': '179286/insights/taps_back/lifetime'}, {'name': 'exits', 'values': [{'value': 610}], 'id': '179286/insights/exits/lifetime'}, {'name': 'replies', 'values': [{'value': 0}], 'id': '179286/insights/replies/lifetime'}]


df = pd.DataFrame(data_response)

I've tried this but its only extracts the dictionary and I want to get the numerical value only for ex: 7352:

df = df.explode('values').reset_index(drop=True)

CodePudding user response：

You can explode and apply pd.Series constructor:

df['values'] = df['values'].explode().apply(pd.Series)

or apply a function that gets the value of value key in each cell in values column:

df["values"] = df['values'].apply(lambda x: x[0]["value"])

Output:

           name  values                                     id
0   impressions    7352   179286/insights/impressions/lifetime
1         reach    7352         179286/insights/reach/lifetime
2  taps_forward    6280  179286/insights/taps_forward/lifetime
3     taps_back     134     179286/insights/taps_back/lifetime
4         exits     610         179286/insights/exits/lifetime
5       replies       0       179286/insights/replies/lifetime

CodePudding user response：

I changed values column to value when using apply as values returns a Numpy representation of the DataFrame.

df.rename(columns={"values": "value"},inplace=True)
df["value"] = df.apply(lambda x: x.value[0]["value"],axis =1)


    name           value  id
0   impressions    7352   179286/insights/impressions/lifetime
1   reach          7352   179286/insights/reach/lifetime
2   taps_forward   6280   179286/insights/taps_forward/lifetime
3   taps_back      134    179286/insights/taps_back/lifetime
4   exits          610    179286/insights/exits/lifetime
5   replies        0      179286/insights/replies/lifetime

If you need to change column name back:

df.rename(columns={"value": "values"},inplace=True)

CodePudding user response：

We can just try

df['values'] = df['values'].str[0].str.get('value')
0    7352
1    7352
2    6280
3     134
4     610
5       0
Name: values, dtype: int64