大 pandas :根据开始/结束日期进行汇总 [英] pandas: Aggregate based on start/end date
本文介绍了大 pandas :根据开始/结束日期进行汇总的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这实际上是一种分解聚合,因为我有一个这样构造的数据集:
It is actually a de-aggregation because I have a dataset structured this way:
id type first_year last_year
A t1 2009 2014
A t1 2010 2015
B t1 2007 2009
B t2 2008 2011
但是我需要按ID/年份进行汇总,并且有重叠的开始/结束条目.
But I need to aggregate by id/year and have overlapping start/end entries.
数据位于熊猫数据框中,如下所示:
Data is in a pandas data frame like so:
test_frame = pd.DataFrame([['A','t1',2009,2014],
['A','t1',2010,2015],
['B','t1',2007,2009],
['B','t2',2008,2011]],
columns = ['id','type','first_year','last_year'])
我希望以几种不同的方式返回数据:
I'm hoping to get data returned in a few different ways:
id year count
A 2009 1
A 2010 2
A 2011 2
...
B 2007 1
B 2008 2
B 2009 1
也许像这样:
id year type count
A 2009 t1 1
A 2010 t1 2
A 2011 t1 2
...
B 2007 t1 1
B 2008 t1 1
B 2008 t2 1
B 2009 t2 1
B 2010 t2 1
这基本上适用于第一种方法,但是您可以想象对大型数据集使用itertuples相当慢.还有其他熊猫方式吗?
This basically works for the 1st method, but as you can imagine it is pretty slow using itertuples for a large dataset. Any more pandas-ic way?
out_frame = pd.DataFrame(columns = ['id','type','year'])
for rows in test_frame.itertuples():
for year in range(int(rows[3]),int(rows[4])):
d2 = pd.DataFrame({'id': [rows[1]],'year': [year]},columns = ['id','year'])
out_frame = out_frame.append(d2)
output1 = out_frame.groupby(['id','year'])['year'].count()
output1
推荐答案
您可以使用 resample
:
You can use stack
and resample
:
import pandas as pd
test_frame = pd.DataFrame([['A','t1',2009,2014],
['A','t1',2010,2015],
['B','t1',2007,2009],
['B','t2',2008,2011]],
columns = ['id','type','first_year','last_year'])
print test_frame
id type first_year last_year
0 A t1 2009 2014
1 A t1 2010 2015
2 B t1 2007 2009
3 B t2 2008 2011
#stack df, drop and rename column year
test_frame = test_frame.set_index(['id','type'], append=True).stack().reset_index(level=[1,2,3])
test_frame = test_frame.drop('level_3', axis=1).rename(columns={0:'year'})
#convert year to datetime
test_frame['year'] = pd.to_datetime(test_frame['year'], format="%Y")
print test_frame
id type year
0 A t1 2009-01-01
0 A t1 2014-01-01
1 A t1 2010-01-01
1 A t1 2015-01-01
2 B t1 2007-01-01
2 B t1 2009-01-01
3 B t2 2008-01-01
3 B t2 2011-01-01
#resample and fill missing data
out_frame = test_frame.groupby(test_frame.index).apply(lambda x: x.set_index('year').resample('1AS', how='first',fill_method='ffill')).reset_index(level=1)
print out_frame
year id type
0 2009-01-01 A t1
0 2010-01-01 A t1
0 2011-01-01 A t1
0 2012-01-01 A t1
0 2013-01-01 A t1
0 2014-01-01 A t1
1 2010-01-01 A t1
1 2011-01-01 A t1
1 2012-01-01 A t1
1 2013-01-01 A t1
1 2014-01-01 A t1
1 2015-01-01 A t1
2 2007-01-01 B t1
2 2008-01-01 B t1
2 2009-01-01 B t1
3 2008-01-01 B t2
3 2009-01-01 B t2
3 2010-01-01 B t2
3 2011-01-01 B t2
#convert to year
out_frame['year'] = out_frame['year'].dt.year
output1 = out_frame.groupby(['id','year', 'type'])['year'].count().reset_index(name='count')
print output1
id year type count
0 A 2009 t1 1
1 A 2010 t1 2
2 A 2011 t1 2
3 A 2012 t1 2
4 A 2013 t1 2
5 A 2014 t1 2
6 A 2015 t1 1
7 B 2007 t1 1
8 B 2008 t1 1
9 B 2008 t2 1
10 B 2009 t1 1
11 B 2009 t2 1
12 B 2010 t2 1
13 B 2011 t2 1
output2 = out_frame.groupby(['id','year'])['year'].count().reset_index(name='count')
print output2
id year count
0 A 2009 1
1 A 2010 2
2 A 2011 2
3 A 2012 2
4 A 2013 2
5 A 2014 2
6 A 2015 1
7 B 2007 1
8 B 2008 2
9 B 2009 2
10 B 2010 1
11 B 2011 1
这篇关于大 pandas :根据开始/结束日期进行汇总的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文