大熊猫每行加入两个不同时间范围的数据帧
我想计算某个公司在其收入日期的一年内在新闻中出现的次数,并在同一时间框架内比较其他人的次数。我有两个熊猫数据框,一个是收益日期,另一个是新闻。我的方法很慢。有更好的熊猫/ numpy方式吗?大熊猫每行加入两个不同时间范围的数据帧
import pandas as pd
companies = pd.DataFrame({'CompanyName': ['A', 'B', 'C'], 'EarningsDate': ['2013/01/15', '2015/03/25', '2017/05/03']})
companies['EarningsDate'] = pd.to_datetime(companies.EarningsDate)
news = pd.DataFrame({'CompanyName': ['A', 'A', 'A', 'B', 'B', 'C'],
'NewsDate': ['2012/02/01', '2013/01/10', '2015/05/13' , '2012/05/23', '2013/01/03', '2017/05/01']})
news['NewsDate'] = pd.to_datetime(news.NewsDate)
companies
看起来像
CompanyName EarningsDate
0 A 2013-01-15
1 B 2015-03-25
2 C 2017-05-03
news
看起来像
CompanyName NewsDate
0 A 2012-02-01
1 A 2013-01-10
2 A 2015-05-13
3 B 2012-05-23
4 B 2013-01-03
5 C 2017-05-01
我如何改写呢?这有效,但是每个数据帧大于500k行非常慢。
company_count = []
other_count = []
for _, company in companies.iterrows():
end_date = company.EarningsDate
start_date = end_date - pd.DateOffset(years=1)
subset = news[(news.NewsDate > start_date) & (news.NewsDate < end_date)]
mask = subset.CompanyName==company.CompanyName
company_count.append(subset[mask].shape[0])
other_count.append(subset[~mask].groupby('CompanyName').size().mean())
companies['12MonCompanyNewsCount'] = pd.Series(company_count)
companies['12MonOtherNewsCount'] = pd.Series(other_count).fillna(0)
最终结果,companies
看起来像
CompanyName EarningsDate 12MonCompanyNewsCount 12MonOtherNewsCount
0 A 2013-01-15 2 2
1 B 2015-03-25 0 0
2 C 2017-05-03 1 0
好吧,在这里。
用于获取12MonCompanyNewsCount
,您可以使用merge_asof,这实在是整洁:
companies['12MonCompanyNewsCount'] = pd.merge_asof(
news,
companies,
by='CompanyName',
left_on='NewsDate',
right_on='EarningsDate',
tolerance=pd.Timedelta('365D'),
direction='forward'
).groupby('CompanyName').count().NewsDate
其中作为当前执行的两倍快的工作(并会变得更好)
对于12MonOtherNewsCount
,我如果不循环事物,就无法真正找到一种方法。我想这是更简洁:
companies['12MonOtherNewsCount'] = companies.apply(
lambda x: len(
news[
(news.NewsDate.between(x.EarningsDate-pd.Timedelta('365D'), x.EarningsDate, inclusive=False))
&(news.CompanyName!=x.CompanyName)
]
),
axis=1
)
而且它确实看起来更快一点。
好的,'merge_asof'正是我在找的东西。它看起来在版本0.19.0中是新的。我升级了我的熊猫,我很高兴去!非常感谢! –
'merge_asof'最近介绍给我一个类似的问题。这真的是一个救星! –
我不能找到一种方法,不是在companies
行迭代。但是,您可以设置companies
的开始日期列,遍历companies
的行并为符合您的标准的日期和公司名称news
创建布尔指数。然后只需执行一个布尔值and
操作并对得到的布尔数组进行求和。
我发誓,它看起来更有意义的代码。
# create the start date column and the 12 month columns,
# fill the 12 month columns with zeros for now
companies['startdate'] = companies.EarningsDate - pd.DateOffset(years=1)
companies['12MonCompanyNewsCount'] = 0
companies['12MonOtherNewsCount'] = 0
# iterate the rows of companies and hold the index
for i, row in companies.iterrows():
# create a boolean index when the news date is after the start date
# and when the news date is before the end date
# and when the company names match
ix_start = news.NewsDate >= row.startdate
ix_end = news.NewsDate <= row.EarningsDate
ix_samename = news.CompanyName == row.CompanyName
# set the news count value for the current row of `companies` using
# boolean `and` operations on the indices. first when the names match
# and again when the names don't match.
companies.loc[i,'12MonCompanyNewsCount'] = (ix_start & ix_end & ix_samename).sum()
companies.loc[i,'12MonOtherNewsCount'] = (ix_start & ix_end & ~ix_samename).sum()
companies
#returns:
CompanyName EarningsDate startdate 12MonCompanyNewsCount \
0 A 2013-01-15 2012-01-15 1
1 B 2015-03-25 2014-03-25 0
2 C 2017-05-03 2016-05-03 1
12MonOtherNewsCount
0 2
1 1
2 0
试试这个:https://*.com/questions/22391433/count-the-frequency-that-a-value-occurs-in-a-dataframe-column – RetardedJoker
'value_counts()'在这里不起作用。我必须加入两个不同窗口的数据框来进行聚合。 –