How to split and duplicate value in column pandas

user_18833814 0
Viewed 38

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?

pandas python
1 Answers

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
user_128331660
Related