Filter one data frame using the multi-index of another data frame
I have the following two data frames DF1 and DF2. I would like to filter DF1 based on DF2 multi-index.
DF1:
Value
Date ID Name
2014-04-30 1001 n1 1
2014-05-31 1002 n2 2
2014-06-30 1003 n3 3
2014-07-31 1004 n4 4
DF2 (index = Date, ID, Name):
Date ID Name
2014-05-31 1002 n2
2014-06-30 1003 n3
What i would like is this:
Value
Date ID Name
2014-05-31 1002 n2 2
2014-06-30 1003 n3 3
To do this, I simply use:
f_df = df1.ix[df2.index]
However, when doing this, I get this (note the index of the tuple)
Value
(2014-05-31, 1002, n2) 2
(2014-06-31, 1003, n3) 4
How can I achieve what I am looking for? which is the resulting data core without the tuple index?
+3
source to share
1 answer
In Pandas version 0.14, you can use df1.loc[df2.index]
:
import io
import pandas as pd
print(pd.__version__)
# 0.14.0
df1 = io.BytesIO('''\
Date ID Name Value
2014-04-30 1001 n1 1
2014-05-31 1002 n2 2
2014-06-30 1003 n3 3
2014-07-31 1004 n4 4
''')
df2 = io.BytesIO('''\
Date ID Name Value
2014-05-31 1002 n2 2
2014-06-30 1003 n3 3
''')
df1 = pd.read_table(df1, sep='\s+').set_index(['Date', 'ID', 'Name'])
df2 = pd.read_table(df2, sep='\s+').set_index(['Date', 'ID', 'Name'])
print(df1.loc[df2.index])
gives
Value
Date ID Name
2014-05-31 1002 n2 2
2014-06-30 1003 n3 3
I believe this is due to the fact that since version 0.14 it df.loc
can accept a list of labels , but df2.index
this is a list:
In [88]: list(df2.index)
Out[88]: [('2014-05-31', 1002L, 'n2'), ('2014-06-30', 1003L, 'n3')]
+2
source to share