Pandas join two dataframes with different timeframes for each row

Question

I am trying to calculate how many times a particular company appeared on a news within one year of its earnings date and compare the count against others for the same time frame. I have two pandas dataframes, one with earnings dates and the other with news. My method is slow. Is there a better pandas/numpy way?

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 looks like

    CompanyName EarningsDate
0   A           2013-01-15
1   B           2015-03-25
2   C           2017-05-03

news looks like

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

How can I rewrite this? This works but it is very slow as each dataframe is > 500k rows.

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)

Final result, companies looks like

    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

Try this: https://stackoverflow.com/questions/22391433/count-the-frequency-that-a-value-occurs-in-a-dataframe-column — SajidSalim, Aug 29 '17 at 13:42
`value_counts() ` does not work here. I have to join two dataframes with different windows to aggregate. — E.K., Aug 29 '17 at 13:44

score 3 · Accepted Answer · edited Aug 29 '17 at 23:13

Ok, here goes.

For getting the 12MonCompanyNewsCount, you can use merge_asof, which is really neat:

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

Which works about twice as quickly as your current implementation (and will scale better)

For 12MonOtherNewsCount, I couldn't really work out a way of doing it without looping through things. I guess this is a bit more terse though:

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
)

And it does seem a bit faster.

Great, `merge_asof` is exactly what I was looking for. It looks like it is new in version 0.19.0. I upgraded my pandas and I am good to go! Thank you so much! — E.K., Aug 29 '17 at 16:09
`merge_asof` was introduced to me recently for a similar problem. It really is a lifesaver! — greg_data, Aug 29 '17 at 16:15

score 1 · Answer 2 · answered Aug 29 '17 at 15:14

I can't find a way to not iterate over the companies rows. However, you can set a start date column for companies, iterate over the rows of companies and create boolean indices for the date and company names of news that fit your criterion. Then just perform a boolean and operation and sum the resulting boolean array.

I swear it make more sense when you see the code.

# 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

Pandas join two dataframes with different timeframes for each row

2 Answers2