Using a DataFrame filter on a different DataFrame

Question

I have two dataframes:

df1:
ID var1
1 Foo
2 Foo
3 Foo
4 Bar

df2:
ID var1
2 Foo
3 Bar
4 Bar
5 Foo    
6 Bar

I have created a filter for df1 where var1 = 'Foo':

foo_filter=df1['var1']=='Foo'

Which when applied to df1 correctly returns:

df1_filtered=df1[foo_filter]

print(df1_filtered)
ID var1
1  Foo
2  Foo
3  Foo

I would like to then apply the same filter to the df2 with an output of this:

print(df2_filtered)
ID var1
2  Foo
3  Bar

However I am presented with this error:

df2_filtered=df2[foo_filter]
IndexingError: Unalignable boolean Series provided as indexer (index of the boolean Series and of the indexed object do not match

Is there any way to do this with filters or do I need a different method?

I want the rows in df2 with the same ID as those where var1 = 'Foo' in df1 — William Wade
– William Wade, Commented Sep 26, 2019 at 14:34
They have different indexes and different lengths - how would this work? :) — rafaelc
– rafaelc, Commented Sep 26, 2019 at 14:36
Well thats what I'm asking, I'm working on quite a cumbersome work around, but I wanted to know if I was missing something simple that would make it work — William Wade
– William Wade, Commented Sep 26, 2019 at 14:39

ansev · Accepted Answer · 2019-09-26 14:49:00Z

1

Only you need:

if ID is the index, putting ID as column:

df1.reset_index(inplace=True) #if ID is the index
df2.reset_index(inplace=True) #if ID is the index
filtered_df1=df1[df1['var1'].eq('Foo')]
print(filtered_df1)

    ID var1
0   1  Foo
1   2  Foo
2   3  Foo

df2.loc[df2['ID'].isin(filtered_df1['ID'])]

   ID var1
0   2  Foo
1   3  Bar

edited Sep 26, 2019 at 14:49

answered Sep 26, 2019 at 14:43

ansev

31k5 gold badges21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

vb_rises · Accepted Answer · 2019-09-26 14:37:18Z

0

df1 = pd.DataFrame({"id":[1,2,3,4], "var1":['Foo', 'Foo', 'Foo', 'Bar']})
df2 = pd.DataFrame({"id":[2,3,4,5, 6], "var1":['Foo','Bar','Bar','Foo', 'Bar']})

def filter(x):
    return x == 'Foo'

df1[df1['var1'].apply(filter)]
    id  var1
0   1   Foo
1   2   Foo
2   3   Foo



df2[df2['var1'].apply(filter)]
    id  var1
0   2   Foo
3   5   Foo

answered Sep 26, 2019 at 14:37

vb_rises

1,9071 gold badge11 silver badges14 bronze badges

1 Comment

William Wade Over a year ago

Thanks for taking the time but this does not produce what I want. I am looking for the the rows from df2 that have the same id as rows in df1 where var1='Foo' rather than the rows from df2 where var1='Foo'

Collectives™ on Stack Overflow

Using a DataFrame filter on a different DataFrame

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related