python - Pandas how to copy a column to another dataframe with similar index

Question

I have one Pandas dataframe like below. I used pd.to_datetime(df['date']).dt.normalize() to get the date2 column to show just the date and ignore the time. Wasn't sure how to have it be just YYYY-MM-DD format.

                       date2  count  compound_mean
0  2021-01-01 00:00:00+00:00     18       0.188411
1  2021-01-02 00:00:00+00:00      9       0.470400
2  2021-01-03 00:00:00+00:00     10       0.008190
3  2021-01-04 00:00:00+00:00     58       0.187510
4  2021-01-05 00:00:00+00:00    150       0.176173

Another dataframe with the following format.

Date          Average
2021-01-04    18.200001
2021-01-05    22.080000
2021-01-06    22.250000
2021-01-07    22.260000
2021-01-08    21.629999

I want to have the Average column show up in the first dataframe by matching the dates and then forward-filling any blank values. From 01-01 to 01-03 there will be nothing to forward fill, so I guess it will end up being zero. I'm having trouble finding the right Pandas functions to do this, looking for some guidance. Thank you.

question from:https://stackoverflow.com/questions/65839391/pandas-how-to-copy-a-column-to-another-dataframe-with-similar-index

Answer 1

I assume your first dataframe to be df1 and second dataframe to be df2.

Firstly, you need to change the name of the date2 column of df1 to Date so that it matches with your Date column of df2.

df1['Date'] = pd.to_datetime(df1['date2']).dt.date

You can then remove the date2 column of df1 as

df1.drop("date2",inplace=True, axis=1)

You also need to change the column type of Date of df2 so that it matches with type of df1's Date column

df2['Date'] = pd.to_datetime(df2['Date']).dt.date

Then make a new dataframe which will contain both dataframe columns based on Date column.

main_df = pd.merge(df1,df2,on="Date", how="left")
df1['Average'] = main_df['Average']
df1 = pd.DataFrame(df1, columns = ['Date', 'count','compound_mean','Average'])

You can then fill the null values by ffill and also the first 3 null values by 0

df1.fillna(method='ffill', inplace=True)
df1.fillna(0, inplace=True)

Your first dataframe will look what you wanted