Using Pandas to extract data from multiple columns to create a new column

Question

I want to create 3 new columns which takes the spend for each channel type for a specific date. Channel column can either be Display, search or social

this is the output I would like to achieve but now sure how to go about it (do not want to do this manually)

output

Answer 1

The question is honestly confusing. But based on your desired output I am going to assume that you want to pivot your dataframe so that the channels are columns and the dates are the index. If for each channel, each date appears only once, this should work:

df.set_index(['Channel', 'Date'])['Spend'].unstack('Channel')

If that was not the case, and for example you wanted to add all the spends in the same channel and date, I would do something like this:

df.groupby(['Channel', 'Date'])['Spend'].sum().unstack('Channel')