CODEWITHSUNDEEP
pythonpandasdataframe
yin
yin

Reputation: 117

Transform Dates in Dataframe Columns into New Columns

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?

enter image description here

Upvotes: 1

Views: 67

Answers (2)

Sudeepa Nadeeshan
Sudeepa Nadeeshan

Reputation: 182

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')

Upvotes: 0

deadshot
deadshot

Reputation: 9061

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

Upvotes: 1

Related Questions