Split a string column into multiple columns and dynamically name columns

Question

This question is similar to this one pandas: split a string column into multiple columns and dynamically name columns. I modified the data as below

df = pd.DataFrame.from_dict({'study_id': {0: 'study1',
  1: 'study2',
  2: 'study3',
  3: 'study4',
  4: 'study5'},
 'fuzzy_market': {0: '[Age: 18-67], [Country of Birth: Austria], [Country of Birth: Germany], [Country: Austria], [Country: Germany], [Language: German]',
  1: '[Country: Germany], [Management experience: Yes]',
  2: '[Country: United Kingdom], [Language: English]',
  3: '[Age: 18-67], [Country of Birth: Austria], [Country of Birth: Germany], [Country: Austria], [Country: Germany], [Language: German]',
  4: '[Age: 48-99]'}})

I would like the output to be as below.

study_id    Age     Country of Birth    Country         Language      Management experience
study1     18-67    Austria             Austria         German        None
study1     18-67    Germany             Germany         German        None
study2     None     None                Germany         None          Yes
study3     None     None                United Kingdom  English       None

How should I modify these codes. Thank you.

p = df['fuzzy_market'].str.findall(r'([^:\[]+): ([^\]]+)')
df[['study_id']].join(pd.DataFrame(map(dict, p)))

ziying35 · Accepted Answer · 2022-11-15 03:15:59Z

0

try this:

data = [*df.pop('fuzzy_market').str.findall(r'([^:\[]+): ([^\]]+)').map(dict)]
res = df.join(pd.DataFrame(data, index=df.index))
print(res)

answered Nov 15, 2022 at 3:15

ziying35

1,3155 silver badges6 bronze badges

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

1 Comment

Jason Over a year ago

this does not work as there should be two rows for study1.

Collectives™ on Stack Overflow

Split a string column into multiple columns and dynamically name columns

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related