creating a new dataframe based on another dataframe with multiple values separated by comma

user_14241181 0
Viewed 28

Assume we have 1 dataframe: df1(5000, 6)
Let's assume it has the following structure (I will only write the first 3 columns since the others should just be copied)

A                                       B                C                                  
'A-0000-ALEX,A-00030-PAUL'             1              '1112-PPAI 12.00: First Name\n4554-ALGF 09:00 Groceries\n'

and I want to create a dataframe that for every time a row has multiple values in a row to create extra rows that separates them as follows

A                                       B                C                
'A-0000-ALEX'                           1              '1112-PPAI 12.00: First Name'
'A-00030-PAUL'                          1              '4554-ALGF 09:00 Groceries'

Thus in A column new rows should be created, in column B just copy the same value that already existed. For column C the values should also be splitted in the new rows and for the rest of the columns (D,E,F) they should just be copied in the new rows that will be created

Edit : Just realized that it is not a 1-1 match between the values of A and C. Some rows have only 1 value in A column and some others rows have the same number of values in A column like they have in column C

filter python
1 Answers

One solution here is to create a list of the wanted values for all your columns instead of a string by splitting the string:

df['A'] = df['A'].apply(lambda x: x.split(","))
df['C'] = df['C'].apply(lambda x: x.split('\n')[:-1])

Note that the [:-1] is used here because split will create a list of three strings for column C because you have a '\n' at the end of the string as well. Then you can use the function pd.explode to explode your lists into rows:

df = df.explode(['A','C'])
user_88142290
Related