DataFrame: New column based on other column with string dictionary/json

user_4697646 0
Viewed 57

I have following pandas DataFrame with (I guess?) a string as a nested JSON in column "bucket".

    id      name    bucket
0   11324   Soaps   {"page":{"url":"https://www.url.com", "color":"white"} ...}
0   11325   Cans    {"page":{"url":"https://www.url.com", "color":"black"} ...}
0   11326   Boxes   {"page":{"url":"https://www.url.com", "color":"red"} ...}

Here are the dtypes:

dtypes
id                           int64
name                         object
bucket                       object

I wonder how I could access the values in column "bucket", so that I can create a new column with the respective value:

    id      name    bucket.                                                          color
0   11324   Soaps   {"web":{"project":"https://www.url1.com", "color":"white"} ...}  white
0   11325   Cans    {"web":{"project":"https://www.url2.com", "color":"white"} ...}  black
0   11326   Boxes   {"web":{"project":"https://www.url3.com", "color":"white"} ...}  red
pandas dictionary python json string
2 Answers

1. Series.str.extract

We can use extract with regex pattern if the values in bucket column are of type string

df['color'] = df['bucket'].str.extract(r'"color":"([^"]+)"', expand=False)

2. Series.str.get

Otherwise we can use str.get if the values in column bucket are of type dict

df['color'] = df['bucket'].str.get('page').str.get('color')

>>> df

      id   name                                                   bucket  color
0  11324  Soaps  {"page":{"url":"https://www.url.com", "color":"white"}}  white
0  11325   Cans  {"page":{"url":"https://www.url.com", "color":"black"}}  black
0  11326  Boxes    {"page":{"url":"https://www.url.com", "color":"red"}}    red
user_128331660

Just use eval() method and apply() method:

df['bucket']=df['bucket'].apply(lambda x:eval(x))

Now Finally:

df['color']=df['bucket'].apply(lambda x:list(x.values())[0]['color'])

Now If you print df you will get your desired output:

    id      name    bucket.                                                          color
0   11324   Soaps   {"web":{"project":"https://www.url1.com", "color":"white"} ...}  white
0   11325   Cans    {"web":{"project":"https://www.url2.com", "color":"white"} ...}  black
0   11326   Boxes   {"web":{"project":"https://www.url3.com", "color":"white"} ...}  red
user_142898920
Related