Pandas create new rows based on column values

Question

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!

Naveed · Accepted Answer · 2022-09-23 21:40:06Z

4

#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

answered Sep 23, 2022 at 21:40

Naveed

11.7k2 gold badges16 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

Pandas create new rows based on column values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related