0

I have two DataFrames (df1, df2), both with a DateTime index type:

print(type(df1.index))  =>  pandas.core.indexes.datetimes.DatetimeIndex
print(type(df2.index))  =>  pandas.core.indexes.datetimes.DatetimeIndex

Both have just one column with some values:

print(df1):

Sample Date  Value df1     
1992-01-02   430.0
1992-01-03   436.0
1992-01-04   439.0
1992-01-05   432.0
1992-01-06   427.0
           ...
2003-12-26   300.0
2003-12-27   306.0
2003-12-28   319.0
2003-12-29   321.0
2003-12-30   310.0

[4381 rows x 1 columns]

print(df2):

Sample Date   Value df2    
1992-02-12    15.0
1992-04-11    24.0
1992-09-12    14.0
1992-11-18    26.0
1992-11-25    14.0
           ...
2003-10-09    43.0
2003-10-22    12.0
2003-12-02     4.0
2003-12-03    18.0
2003-12-08    44.0

[424 rows x 1 columns]

I want to get a new DF with those two columns (Value df1 and Value df2) just with the rows where the data is available in both data frames. For simplicity df1 is a DF without missing dates, whereas df2 has missing dates.

The results should look something like this:

Sample Date   Value df1    Value df2    
1992-02-12    350.5        15.0
1992-04-11    420.2        24.0
1992-09-12    400.0        14.0
1992-11-18    380.5        26.0
1992-11-25    395.9        14.0
               ...         ...
2003-10-09    500.5        43.0
2003-10-22    480.9        12.0
2003-12-02    500.8        4.0
2003-12-03    350.0        18.0
2003-12-08    370.8        44.0

I tried to create a new df like this:

df = df1[df1.index.isin([df2.index])]

but the result is an empty DF. If I print df1.index.isin([df2.index]) I do get something like this: array([False, False, False, ..., False, False, False]).

Any ideas how can I solve this issue?

Thanks in advance.

jei L
  • 33
  • 6
  • I can't tell you exactly how to do it, but it involves [DataFrame.join](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.join.html) with the `how` parameter set to `"inner"` – Kenny Jun 08 '22 at 08:46
  • Thanks @Kenny! I tried it and it work. Something like this: df1.join(df2, how='inner') – jei L Jun 08 '22 at 08:57
  • Merge by index: https://stackoverflow.com/questions/40468069/merge-two-dataframes-by-index – Beta Jun 08 '22 at 09:00
  • Or: reset_index() both df. And then merge by date column. – Beta Jun 08 '22 at 09:01

0 Answers0