Combine two dataframes according to a common column with missing values

Question

I have two dataframes that share a common column named date.

df1 = pd.DataFrame({'date': ["2021-06-01 08:00:00", "2021-06-01 08:01:00", "2021-06-01 08:02:00", "2021-06-01 08:03:00", "2021-06-01 08:04:00"], 'x1': [1, 2, 3, 4, 5]})

                  date  x1
0  2021-06-01 08:00:00   1
1  2021-06-01 08:01:00   2
2  2021-06-01 08:02:00   3
3  2021-06-01 08:03:00   4
4  2021-06-01 08:04:00   5

df2 = pd.DataFrame({'date': ["2021-06-01 08:00:00", "2021-06-01 08:01:00", "2021-06-01 08:04:00"], 'x2': [11, 22, 55]})

                  date  x2
0  2021-06-01 08:00:00  11
1  2021-06-01 08:01:00  22
2  2021-06-01 08:04:00  55

First, I need to mention that df1 does not contain missing date, only df2. Now, I want to add df2 x2 to df1 according to the date column. For missing values, I need to add something silly like string MISSING or NaN. How should I achieve this in python?

For example, dates "2021-06-01 08:02:00", "2021-06-01 08:03:00" are missing in df2. So, resulting df1:

df1 = pd.DataFrame({'date': ["2021-06-01 08:00:00", "2021-06-01 08:01:00", "2021-06-01 08:02:00", "2021-06-01 08:03:00", "2021-06-01 08:04:00"], 'x1': [1, 2, 3, 4, 5], 'x2': [11, 22, np.nan, np.nan, 55]})

                  date  x1    x2
0  2021-06-01 08:00:00   1  11.0
1  2021-06-01 08:01:00   2  22.0
2  2021-06-01 08:02:00   3   NaN
3  2021-06-01 08:03:00   4   NaN
4  2021-06-01 08:04:00   5  55.0

score 2 · Accepted Answer · answered Jun 30 '22 at 20:02

Are you looking for something like this?

import pandas as pd

pd.merge(df1,df2, how='left', on='date')

 date  x1    x2
0  2021-06-01 08:00:00   1  11.0
1  2021-06-01 08:01:00   2  22.0
2  2021-06-01 08:02:00   3   NaN
3  2021-06-01 08:03:00   4   NaN
4  2021-06-01 08:04:00   5  55.0

score 0 · Answer 2 · answered Jun 30 '22 at 19:58

out = df1.merge(df2, how='outer') # or how='left' if you're sure df1 as all dates.
print(out)

Output:

                  date  x1    x2
0  2021-06-01 08:00:00   1  11.0
1  2021-06-01 08:01:00   2  22.0
2  2021-06-01 08:02:00   3   NaN
3  2021-06-01 08:03:00   4   NaN
4  2021-06-01 08:04:00   5  55.0

Combine two dataframes according to a common column with missing values

2 Answers2