Hi I have a table where it's like this:
| date | conversions |
|---|---|
| 2022-05-01 | [{"action_type":"app_custom_event.LOGIN","value":"25"}] |
| 2022-05-02 | [{"action_type":"subscribe_mobile_app","value":"1"},{"action_type":"subscribe_total","value":"1"},{"action_type":"app_custom_event.LOGIN","value":"237"}] |
| 2022-05-03 | [{"action_type":"app_custom_event.LOGIN","value":"21"}] |
What I want is to break the conversions event into columns. The end result is
| date | conversions type | conversions value |
|---|---|---|
| 2022-05-01 | app_custom_event.LOGIN | 25 |
| 2022-05-02 | subscribe_mobile_app | 1 |
| 2022-05-02 | subscribe_total | 1 |
| 2022-05-02 | app_custom_event.LOGIN | 237 |
| 2022-05-03 | app_custom_event.LOGIN | 21 |
Right now I am stuck at
campaign_data_df[['conversions type','conversions value']]= campaign_data_df['conversions'].str.split(",", n = 2, expand = True)
campaign_data_df['conversions value']=campaign_data_df['conversions value'].str.extract('(\d )')
campaign_data_df['conversions type']=campaign_data_df['conversions type'].str.replace('[^a-zA-Z0-9]', ' ')
campaign_data_df= campaign_data_df.drop(columns=['conversions'])
campaign_data_df.to_csv('data/facebook/fb.csv', mode="w", header=True,index=False)
However this will work only with 2 types. What should I do?
CodePudding user response：
First evaluate the strings in conversions as python dictionaries then explode the column then create new dataframe from the exploded column and join it with the date column
from ast import literal_eval
s = df['conversions'].map(literal_eval).explode()
df[['date']].join(pd.DataFrame([*s], index=s.index))
date action_type value
0 2022-05-01 app_custom_event.LOGIN 25
1 2022-05-02 subscribe_mobile_app 1
1 2022-05-02 subscribe_total 1
1 2022-05-02 app_custom_event.LOGIN 237
2 2022-05-03 app_custom_event.LOGIN 21