2016-06-06 2016-06-07 2016-06-08 2016-06-09 2016-06-10 2016-06-11 2016-06-12
Group1
BHA
Voltage 1
VGT
Power 0 1
Group2
BNA
Voltage 1
Volume 1
PAR
ANM 0
Group3
KAL
AAA 0
I use the below, but its not pivoting as I required;
Pivot table rules:
Rowlable: Group, Name,Item,
Column lable is Date, values are Value
import pandas as pd
import numpy as np
input=r'D:\\pivotdata.xlsx'
sheet_name='Sheet1'
# csv new (create) file
df=pd.read_excel(input,sheetname=sheet_name)
df_new=pd.pivot_table(df,index=["Group","Name","Item"],values=["Date","Value"],aggfunc=[np.max],fill_value=0)
Probably need the original sheet of data too ?
Reading the doc perhaps its more :
import pandas as pd
import numpy as np
input=r'D:\\pivotdata.xlsx'
sheet_name='Sheet1'
# csv new (create) file
df=pd.read_excel(input,sheetname=sheet_name)
df_new=pd.pivot_table(df,index=["Group","Name","Item"],values="Value",columns="Date",aggfunc=[np.max],fill_value=0)
The original data is below:
Group Name Date Item Value
Group1 VGT 2016-06-06 Power 0
Group1 BHA 2016-06-07 Voltage 1
Group2 BNA 2016-06-08 Volume 1
Group3 KAL 2016-06-09 AAA 0
Group2 PAR 2016-06-10 ANM 0
Group1 VGT 2016-06-11 Power 1
Group2 BNA 2016-06-12 Voltage 1