Select rows with specific condition from other dataframe

Question

I have dataframe df which has one column:

colors
red
blue
green
black
pink

And another dataframe df1 which has many columns (~300 columns):

colorName col1 col2 ... colN
pink 1 0 1 ... 0
white 0 1 1 ... 1
blue 1 0 0 ... 0
yellow 0 0 0 ... 0

What I need is to return the rows of df in which it exists in df1.colorName and have at least value 1 in any of the columns (col1 ... colN)

So, from the above; the output should be:

blue
pink

I'm starting with this but I'm sure it needs an additional condition for (checking have at least value 1 in any of the columns (col1 ... colN))

newDF = df[df.colors.isin(df1.colorName) && ]

Correct me if I'm wrong and any help would be appreciated.

jezrael · Accepted Answer · 2019-04-17 09:08:08Z

2

Use boolean indexing with select columns by DataFrame.iloc - all columns without first with DataFrame.any for at least one True per rows and DataFrame.loc for column colorName first and pass it to filtering by Series.isin:

c = df1.loc[df1.iloc[:, 1:].any(axis=1), 'colorName']
#alternative
#c = df1.loc[df1.drop('colorName', axis=1).any(axis=1), 'colorName']
newDF = df[df. colors.isin(c)]
print (newDF)
  colors
1   blue
4   pink

Detail:

print (c)
0     pink
1    white
2     blue
Name: colorName, dtype: object

answered Apr 17, 2019 at 9:08

jezrael

867k102 gold badges1.4k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

Select rows with specific condition from other dataframe

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related