Pivot DataFrame

Question

I have a data file in this format:

datafile

I want the columns to be grouped by month in a pivot table. When I pivot the data a column for each day is being created.

df = ex.read_excel("C:\\ExportReport.xlsx", "ExportReport")
table = pd.pivot_table(df, values='Forecast Qty', rows='Part', cols='Due Date', aggfunc=np.sum, fill_value=0)

Is there a way to tell pandas to group the columns by month?

A file like this is stored on my network every week with updated data. I want to create a cron job to run that will do a bunch of things with the data and create a new report. I currently have a macro in VBA that does everything but I can't easily run this on a schedule — Ripster
– Ripster, Commented Apr 8, 2014 at 17:31
thanks, I go back and forth between the two, and just trying to see how others view the benefits/drawbacks — chucklukowski
– chucklukowski, Commented Apr 8, 2014 at 17:37

Ripster · Accepted Answer · 2014-04-08 19:35:37Z

2

Need to have a field that calculates the month. If this is going to span multiple years, will need to combine into one field.

df['YYYY-MM'] = df['Due Date'].apply(lambda x: x.strftime("%Y-%m"))

Then try yours, but change to the monthly field...

table = pd.pivot_table(df, values='Forecast Qty', rows='Part', cols='YYYY-MM', aggfunc=np.sum, fill_value=0)

edited Apr 8, 2014 at 19:35

Ripster

3,6152 gold badges21 silver badges28 bronze badges

answered Apr 8, 2014 at 17:31

chucklukowski

2,0342 gold badges14 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pivot DataFrame

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related