python - How to create a pivot table using a datetime column in pandas

Question

I have a datetime column and value column which I would like to pivot. The goal is to create a column for each month and a row that shows the mean value for each month.

import pandas as pd
import numpy as np
import calendar

d = dict(enumerate(calendar.month_abbr))

rng = pd.date_range('2019-01-01', periods=365, freq='D')
df= pd.DataFrame({'Date': rng, 'Val': np.random.randint(10, size=365)}) 
df.set_index('Date', inplace=True)

df = df.resample('1M').mean().reset_index()
df['Month'] = df['Date'].apply(lambda x: d[x.month])

df.pivot(columns='Month', values='Val')

The output should be 12 columns Jan, Feb, Mar, etc... and 1 row that is the mean for each month.

标签： pythonpandas

Answer 1

请改用 pd.pivot_table ：

import pandas as pd
import numpy as np
import calendar

d = dict(enumerate(calendar.month_abbr))

rng = pd.date_range('2019-01-01', periods=365, freq='D')
df= pd.DataFrame({'Date': rng, 'Val': np.random.randint(10, size=365)}) 
df.set_index('Date', inplace=True)

df = df.resample('1M').mean().reset_index()
df['Month'] = df['Date'].apply(lambda x: d[x.month])

pd.pivot_table(data=df,columns='Month', values='Val', aggfunc=np.mean)

输出：

Month  Apr       Aug       Dec       Feb       Jan       Jul       Jun  \
Val    3.2  4.419355  4.548387  5.857143  5.322581  4.354839  5.033333   

Month       Mar       May       Nov       Oct  Sep  
Val    4.645161  4.193548  4.966667  3.645161  3.7