Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Pivot dataframe
#1
		                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)
Reply
#2
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)
Reply
#3
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
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  pivot table (excel vs python) Bric 1 1,641 Feb-02-2022, 11:04 PM
Last Post: Bric
  Resampling and regrouping using pivot table karlito 4 3,265 Apr-25-2020, 04:03 PM
Last Post: karlito
  Python and a pivot table stilwellj 0 2,258 Nov-04-2019, 01:00 AM
Last Post: stilwellj
  Pivot Table hash problem stilwellj 1 2,162 Oct-30-2019, 08:14 AM
Last Post: Larz60+

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020