How to add a yearly amount to daily data in Pandas

Question

I have two DataFrames in pandas. One of them has data every month, the other one has data every year. I need to do some computation where the yearly value is added to the monthly value.

Something like this:

df1, monthly:

    2013-01-01         1
    2013-02-01         1
            ...
    2014-01-01         1
    2014-02-01         1
            ...
    2015-01-01         1

df2, yearly:

    2013-01-01         1
    2014-01-01         2
    2015-01-01         3

And I want to produce something like this:

    2013-01-01         (1+1) = 2
    2013-02-01         (1+1) = 2
            ...
    2014-01-01         (1+2) = 3
    2014-02-01         (1+2) = 3
            ...
    2015-01-01         (1+3) = 4

Where the value of the monthly data is added to the value of the yearly data depending on the year (first value in the parenthesis is the monthly data, second value is the yearly data).

Answer 1

Assuming your "month" column is called date in the Dataframe df, then you can obtain the year by using the dt member:

pd.to_datetime(df.date).dt.year

Add a column like that to your month DataFrame, and call it year. (See this for an explanation).

Now do the same to the year DataFrame.

Do a merge on the month and year DataFrames, specifying how=left.

In the resulting DataFrame, you will have both columns. Now just add them.

---

Example

month_df = pd.DataFrame({
    'date': ['2013-01-01', '2013-02-01', '2014-02-01'],
    'amount': [1, 2, 3]})
year_df = pd.DataFrame({
    'date': ['2013-01-01', '2014-02-01', '2015-01-01'],
    'amount': [7, 8, 9]})

month_df['year'] = pd.to_datetime(month_df.date).dt.year
year_df['year'] = pd.to_datetime(year_df.date).dt.year

>>> pd.merge(
    month_df, 
    year_df, 
    left_on='year', 
    right_on='year',
    how='left')
    amount_x    date_x  year    amount_y    date_y
0   1   2013-01-01  2013    7   2013-01-01
1   2   2013-02-01  2013    7   2013-01-01
2   3   2014-02-01  2014    8   2014-02-01