Pandas - Extract string using a specific pattern in column

Question

I have a column in my Dataframe that has data in the below format:

id, value
10001, "[{'self': 'https://www.weburl.com', 'value': 'Value 1', 'id': '101'}, 
   {'self': 'https://www.weburl.com', 'value': 'Value 2', 'id': '102'}]"
10002, "[{'self': 'https://www.weburl.com', 'value': 'Value 1', 'id': '101'}, 
   {'self': 'https://www.weburl.com', 'value': 'Value 2', 'id': '102'}, 
   {'self': 'https://www.weburl.com', 'value': 'Value 4', 'id': '104'}]"

I am trying to extract data such that I get the below output (extract all values corresponding to value field) as a new column:

id, new_value
10001, Value 1, Value 2
10002, Value 1, Value 2, Value 4

Henry Yik · Accepted Answer · 2020-07-27 09:33:19Z

3

If your values are valid json, you could use ast.literal_eval with list comprehension:

df["value"] = df["value"].apply(literal_eval)
df["value"] = [", ".join(k["value"] for k in i) for i in df["value"]]

print (df)

      id                      value
0  10001           Value 1, Value 2
1  10002  Value 1, Value 2, Value 4

answered Jul 27, 2020 at 9:33

Henry Yik

22.6k5 gold badges21 silver badges44 bronze badges

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

1 Comment

Kevin Nash Over a year ago

thanks it worked well. Just trying to figure a way out to handle null in between...

Collectives™ on Stack Overflow

Pandas - Extract string using a specific pattern in column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related