Menu
I have two pandas DataFrames df1 and df2 and I want to transform them in order that they keep values only for the index that are common to the 2 dataframes.
df1
values 1
0
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
01/12/2000 0.012749
04/12/2000 0.113892
df2
values 2
24/11/2000 -0.004808
27/11/2000 -0.001812
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
become
df1
value 1
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
df2
value 2
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
374
To find the common rows between two DataFrames with merge(), use the parameter “how” as “inner” since it works like SQL Inner Join and this is what we want to achieve.
Intersection of Two data frames in Pandas can be easily calculated by using the pre-defined function merge() . This function takes both the data frames as argument and returns the intersection between them.
You can use Index.intersection + DataFrame.loc:
idx = df1.index.intersection(df2.index)
print (idx)
Index(['28/11/2000', '29/11/2000', '30/11/2000'], dtype='object')
Alternative solution with numpy.intersect1d:
idx = np.intersect1d(df1.index, df2.index)
print (idx)
['28/11/2000' '29/11/2000' '30/11/2000']
df1 = df1.loc[idx]
print (df1)
values 1
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
df2 = df2.loc[idx]
In [352]: common = df1.index.intersection(df2.index)
In [353]: df1.loc[common]
Out[353]:
values1
0
28/11/2000 -0.055276
29/11/2000 0.027427
30/11/2000 0.066009
In [354]: df2.loc[common]
Out[354]:
values2
0
28/11/2000 -0.026316
29/11/2000 0.015222
30/11/2000 -0.024480
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
