Home Outer merge two dataframe in python where the left dataframe is larger than the first

Questions

Outer merge two dataframe in python where the left dataframe is larger than the first

August 6, 2023

I am trying to perform an outer join (union) of two time series in python.
The left time series is larger than the first.

An example:

Right time series – df_1

Time	Series 1
3	1
4	2
5	3

Left time series – df_2

Time	Series 2
0	10
1	11
2	12
3	13
4	14
5	15
6	16

I simply tried:

pd.merge([df_1, df_2], how = "outer", on = "Time")

I expected to get something like this:

Time	Series 1	Series 2
0	nan	10
1	nan	11
2	nan	12
3	1	13
4	2	14
5	3	15
6	nan	16

Instead I got this:

Time	Series 1	Series 2
3	1	13
4	2	14
5	3	15
6	nan	16

It is crucial for the exercise to keep the time series in the correct sequence, I do not want to switch the order of the merge.

>Solution :

instead of using ‘outer’ in how argument you can use ‘right’.
Below is the sample code:

import pandas as pd
import numpy as np

df1 = pd.DataFrame({'Time': ['time 3', 'time 4', 'time 5'],
     'Series 1': [1, 2, 3]})
df2 = pd.DataFrame({'Time': ['time 0', 'time 1', 'time 2', 'time 3', 'time 4', 'time 5', 'time 6'],'Series 2': [10, 11, 12, 13, 14, 15, 16]})

merged_df = pd.merge(df1, df2, on='Time', how='right')
merged_df = merged_df.sort_values('Time')
merged_df = merged_df.reset_index(drop=True)
merged_df['Series 1'] = merged_df['Series 1'].fillna(np.nan)

I hope this will solve your problem.