Below is a screenshot from Excel, but let's suppose we have a Pandas dataframe that looks like the table on the left where we have dates in a column. Is there an easy way to transform it into a dataframe that looks like the table on the right so that the dates become new columns?
Using df.pivot
import pandas as pd
df = df.pivot(index='Name', columns='Date', values='Candies')
df = df.reset_index().rename_axis(None, axis=1)
df
Output:
Name 4/01/20 4/02/20 4/03/20 4/04/20 4/05/20 4/06/20 4/07/20 4/08/20 4/09/20
0 Alice 1 2 3 4 5 6 7 8 9
1 Bob 2 4 2 4 3 2 1 3 0
You can read the .xlsx as @victor proposed in above (change parameter as fits).
df=pd.read_excel('my_excel.xlsx')
Group the data frame by the 'name' and 'date' columns and get the sum of the candles.
df_grouped=df.groupby(['name','date'])['candles'].agg('sum').reset_index()
Now use 'pivot_table' pandas method to pivot the grouped results.
df_pivot=df_grouped.pivot_table('candles', 'name', 'date')
