CODEWITHSUNDEEP
pandas
Landmaster
Landmaster

Reputation: 1053

How to add a yearly amount to daily data in Pandas

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).

Upvotes: 1

Views: 916

Answers (1)

Ami Tavory
Ami Tavory

Reputation: 76406

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

Upvotes: 1

Related Questions