Pandas create new rows based on column values

Cummins070 0
Viewed 29
df_current = pd.DataFrame({'Date':['2022-09-16', '2022-09-17', '2022-09-18'],'Name': ['Bob Jones', 'Mike Smith', 'Adam Smith'],
               'Items Sold':[1, 3, 2], 'Ticket Type':['1 x GA', '2 x VIP, 1 x GA', '1 x GA, 1 x VIP']})

Date    Name    Items Sold  Ticket Type
0   2022-09-16  Bob Jones   1   1 x GA
1   2022-09-17  Mike Smith  3   2 x VIP, 1 x GA
2   2022-09-18  Adam Smith  2   1 x GA, 1 x VIP

Hi there. I have the above dataframe, and what I'm after is new rows, with the ticket type and number of tickets sold split out such as below:

df_desired =  pd.DataFrame({'Date':['2022-09-16', '2022-09-17', '2022-09-17', '2022-09-18', '2022-09-18'],
                        'Name': ['Bob Jones', 'Mike Smith', 'Mike Smith', 'Adam Smith', 'Adam Smith'],
               'Items Sold':[1, 2, 1, 1, 1], 'Ticket Type':['GA', 'VIP', 'GA', 'GA', 'VIP']})

Any help would be greatly appreciated!

dataframe pandas python
1 Answers
#create df2, by splitting df['ticket type'] on "," and then explode to create rows
df2=df.assign(tt=df['Ticket Type'].str.split(',')).explode('tt')

# split again at 'x'
df2[['Items Sold','Ticket Type']]=df2['tt'].str.split('x', expand=True)

#drop the temp column
df2.drop(columns="tt", inplace=True)
df2


Date    Name    Items Sold  Ticket Type
0   2022-09-16  Bob Jones   1   GA
1   2022-09-17  Mike Smith  2   VIP
1   2022-09-17  Mike Smith  1   GA
2   2022-09-18  Adam Smith  1   GA
2   2022-09-18  Adam Smith  1   VIP
userCaFe0
Related