I have two pandas series with DateTimeIndex. I'd like to join these two series such that the resulting DataFrame uses the index of the first series and "matches" the values from the second series accordingly (using a linear interpolation in the second series).
First Series:
2020-03-01 1
2020-03-03 2
2020-03-05 3
2020-03-07 4
Second Series:
2020-03-01 20
2020-03-02 22
2020-03-05 25
2020-03-06 35
2020-03-07 36
2020-03-08 45
Desired Output:
2020-03-01 1 20
2020-03-03 2 23
2020-03-05 3 25
2020-03-07 4 36
Code for generating the input data:
import pandas as pd
import datetime as dt
s1 = pd.Series([1, 2, 3, 4])
s1.index = pd.to_datetime([dt.date(2020, 3, 1), dt.date(2020, 3, 3), dt.date(2020, 3, 5), dt.date(2020, 3, 7)])
s2 = pd.Series([20, 22, 25, 35, 36, 45])
s2.index = pd.to_datetime([dt.date(2020, 3, 1), dt.date(2020, 3, 2), dt.date(2020, 3, 5), dt.date(2020, 3, 6), dt.date(2020, 3, 7), dt.date(2020, 3, 8)])
Use concat with inner join:
df = pd.concat([s1, s2], axis=1, keys=('s1','s2'), join='inner')
print (df)
s1 s2
2020-03-01 1 20
2020-03-05 3 25
2020-03-07 4 36
Solution with interpolate of s2 Series and then removed rows with missing values:
df = (pd.concat([s1, s2], axis=1, keys=('s1','s2'))
.assign(s2 = lambda x: x.s2.interpolate('index'))
.dropna())
print (df)
s1 s2
2020-03-01 1.0 20.0
2020-03-03 2.0 23.0
2020-03-05 3.0 25.0
2020-03-07 4.0 36.0