Pandas merging monthly data from one dataframe with daily data in another

Question

I have a csv file (say A.csv) with an index from 1980-01-01 to 2018-02-28 (increased by a day) and one data column (say everyday's stock price).

I have another csv file (say B.csv) with an index from 1980-01 to 2018-02 (increased by a month) and one data column (say monthly trade balance).

In such case, how do merge B.csv to A.csv (by maintaining daily index)? i.e., daily index + one column for daily stock price + another column for monthly trade balance (I need to expand monthly trade balance to daily trade balance by maintaining the same trade balance values for each days in a month).

jpp jpp · Accepted Answer · 2018-03-17T02:03:10

You can do this with pandas.

One way to do this is to convert both date columns to datetime objects, and use pd.Series.map to perform the mapping from one table to the other.

Since day is not specified for your monthly data, for our mapping we normalise to the first day of the month.

import pandas as pd

# first read in the 2 tables into dataframes
# df_daily = pd.read_csv('daily.csv')
# df_monthly = pd.read_csv('monthly.csv')

df_daily = pd.DataFrame({'Date': ['1980-01-01', '1980-01-02', '1980-01-03'],
                         'Value': [1, 2, 3]})

df_monthly = pd.DataFrame({'Month': ['1979-12', '1980-01', '1980-03'],
                           'Value': [100, 200, 300]})

# convert to datetime objects
df_daily['Date'] = pd.to_datetime(df_daily['Date'])
df_monthly['Month'] = pd.to_datetime(df_monthly['Month']+'-01')

# perform mapping after normalising to first day of month
df_daily['MonthValue'] = df_daily['Date'].map(lambda x: x.replace(day=1))\
                                         .map(df_monthly.set_index('Month')['Value'])

#         Date  Value  MonthValue
# 0 1980-01-01      1         200
# 1 1980-01-02      2         200
# 2 1980-01-03      3         200

Pandas merging monthly data from one dataframe with daily data in another

1 Answers