大 pandas pivot_table日期 [英] Pandas pivot_table on date
问题描述
我有一个大熊猫 DataFrame
与一个日期列。它不是一个索引。
我想在数据框上使用每个位置的每月的累计汇总数据框。
数据如下所示:
['INDEX'] DATE LOCATION COUNT
0 2009-01-02 00:00: 00 AAH 1
1 2009-01-03 00:00:00 ABH 1
2 2009-01-03 00:00:00 AAH 1
3 2009-01-03 00:00 :00 ABH 1
4 2009-01-04 00:00:00 ACH 1
我用过:
pivot_table(cdiff,values ='COUNT',rows = ['DATE','LOCATION'],aggfunc = np.sum)
p>
来转动值。我需要一种方式将cdiff.DATE转换为一个月而不是一个日期。
我希望得到如下结果:
数据如下所示:
MONTH LOCATION COUNT
1月AAH 2
1月ABH 2
1月ACH 1
我尝试过各种各样的 strftime
方法在cdiff.DATE没有成功。它想要应用到字符串,而不是串行对象。
我会建议:
months = cdiff.DATE.map(lambda x:x.month)
pivot_table(cdiff,values ='COUNT',rows = [months, LOCATION'],
aggfunc = np.sum)
要获取月份名称,不同的功能或使用内置的 calendar.month_name
。要获取所需格式的数据,您应该在结果上调用 reset_index
,或者也可以执行以下操作:
cdiff.groupby([months,'LOCATION'],as_index = False).sum()
I have a pandas DataFrame
with a date column. It is not an index.
I want to make a pivot_table on the dataframe using counting aggregate per month for each location.
The data look like this:
['INDEX'] DATE LOCATION COUNT 0 2009-01-02 00:00:00 AAH 1 1 2009-01-03 00:00:00 ABH 1 2 2009-01-03 00:00:00 AAH 1 3 2009-01-03 00:00:00 ABH 1 4 2009-01-04 00:00:00 ACH 1
I used:
pivot_table(cdiff, values='COUNT', rows=['DATE','LOCATION'], aggfunc=np.sum)
to pivot the values. I need a way to convert cdiff.DATE to a month rather than a date. I hope to end up with something like: The data look like this:
MONTH LOCATION COUNT January AAH 2 January ABH 2 January ACH 1
I tried all manner of strftime
methods on cdiff.DATE with no success. It wants to apply the to strings, not series object.
I would suggest:
months = cdiff.DATE.map(lambda x: x.month)
pivot_table(cdiff, values='COUNT', rows=[months, 'LOCATION'],
aggfunc=np.sum)
To get a month name, pass a different function or use the built-in calendar.month_name
. To get the data in the format you want, you should call reset_index
on the result, or you could also do:
cdiff.groupby([months, 'LOCATION'], as_index=False).sum()
这篇关于大 pandas pivot_table日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!