Pandas DataFrame check if column value exists in a group of columns

Question

I have a DataFrame like this (simplified example)

id  v0  v1  v2  v3  v4
1   10  5   10  22  50
2   22  23  55  60  50
3   8   2   40  80  110
4   15  15  25  100 101

And would like to create an additional column that is either 1 or 0. 1 if v0 value is in the values of v1 to v4, and 0 if it's not. So, in this example for id 1 then the value should be 1 (since v2 = 10) and for id 2 value should be 0 since 22 is not in v1 thru v4.

In reality the table is way bigger (around 100,000 rows and variables go from v1 to v99).

user3483203 · Accepted Answer · 2018-09-18T20:43:16.147

You can use the underlying numpy arrays for performance:

Setup

a = df.v0.values
b = df.iloc[:, 2:].values

df.assign(out=(a[:, None]==b).any(1).astype(int))

   id  v0  v1  v2   v3   v4  out
0   1  10   5  10   22   50    1
1   2  22  23  55   60   50    0
2   3   8   2  40   80  110    0
3   4  15  15  25  100  101    1

This solution leverages broadcasting to allow for pairwise comparison:

First, we broadcast a:

>>> a[:, None]
array([[10],
       [22],
       [ 8],
       [15]], dtype=int64)

Which allows for pairwise comparison with b:

>>> a[:, None] == b
array([[False,  True, False, False],
       [False, False, False, False],
       [False, False, False, False],
       [ True, False, False, False]])

We then simply check for any True results along the first axis, and convert to integer.

Performance

Functions

def user_chris(df):
    a = df.v0.values
    b = df.iloc[:, 2:].values
    return (a[:, None]==b).any(1).astype(int)

def rahlf23(df):
    df = df.set_index('id')
    return df.drop('v0', 1).isin(df['v0']).any(1).astype(int)

def chris_a(df):
    return df.loc[:, "v1":].eq(df['v0'], 0).any(1).astype(int)

def chris(df):
    return df.apply(lambda x: int(x['v0'] in x.values[2:]), axis=1)

def anton_vbr(df):
    df.set_index('id', inplace=True)
    return df.isin(df.pop('v0')).any(1).astype(int)

Setup

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from timeit import timeit

res = pd.DataFrame(
       index=['user_chris', 'rahlf23', 'chris_a', 'chris', 'anton_vbr'],
       columns=[10, 50, 100, 500, 1000, 5000],
       dtype=float
)

for f in res.index:
    for c in res.columns:
        vals = np.random.randint(1, 100, (c, c))
        vals = np.column_stack((np.arange(vals.shape[0]), vals))
        df = pd.DataFrame(vals, columns=['id'] + [f'v{i}' for i in range(0, vals.shape[0])])
        stmt = '{}(df)'.format(f)
        setp = 'from __main__ import df, {}'.format(f)
        res.at[f, c] = timeit(stmt, setp, number=50)

ax = res.div(res.min()).T.plot(loglog=True)
ax.set_xlabel("N");
ax.set_ylabel("time (relative)");

plt.show()

Output

Incredible effort, +1 for sure. Sure are a lot of Chris’ in here :) — Chris Adams, Sep 18 '18 at 21:02
Thank you, not sure why your answer was being downvoted, it's the fastest (and clearest) pandas solution, +1 — user3483203, Sep 18 '18 at 21:05

Chris Adams · Answer 2 · 2018-09-18T20:04:02.743

3

How about:

df['new_col'] = df.loc[:, "v1":].eq(df['v0'],0).any(1).astype(int)

[out]

   id  v0  v1  v2   v3   v4  new_col
0   1  10   5  10   22   50        1
1   2  22  23  55   60   50        0
2   3   8   2  40   80  110        0
3   4  15  15  25  100  101        1

edited Sep 18 '18 at 20:04

answered Sep 18 '18 at 19:58

Chris Adams

18,389
4
22
39

4

What's wrong with this answer? Maybe I'm missing something, but this is what I was planning to do. – DSM Sep 18 '18 at 20:08
2

@DSM seconded. I just posted the same and deleted when I saw this. – piRSquared Sep 18 '18 at 21:13

score 3 · Answer 3 · answered Sep 18 '18 at 20:27

Another take, most likely the smallest syntax:

df['new'] = df.isin(df.pop('v0')).any(1).astype(int)

Full proof:

import pandas as pd

data = '''\
id  v0  v1  v2  v3  v4
1   10  5   10  22  50
2   22  23  55  60  50
3   8   2   40  80  110
4   15  15  25  100 101'''

df = pd.read_csv(pd.compat.StringIO(data), sep='\s+')
df.set_index('id', inplace=True)
df['new'] = df.isin(df.pop('v0')).any(1).astype(int)
print(df)

Returns:

    v1  v2   v3   v4  new
id                       
1    5  10   22   50    1
2   23  55   60   50    0
3    2  40   80  110    0
4   15  25  100  101    1

rahlf23 · Answer 4 · 2018-09-18T20:23:46.197

2

I'm assuming here that id is set to be your dataframe index here:

df = df.set_index('id')

Then the following should work (similar answer here):

df['New'] = df.drop('v0', 1).isin(df['v0']).any(1).astype(int)

Gives:

    v0  v1  v2   v3   v4  New
id                           
1   10   5  10   22   50    1
2   22  23  55   60   50    0
3    8   2  40   80  110    0
4   15  15  25  100  101    1

edited Sep 18 '18 at 20:23

answered Sep 18 '18 at 20:00

rahlf23

8,869
4
24
54

@Wen: really? For me `pd.DataFrame({"A": [1,2,3]}).isin([3,2,1])` gives Trues, but `pd.DataFrame({"A": [1,2,3]}).isin(pd.Series([3,2,1]))` gives False, True, False, which makes it seem like it _is_ respecting the index. – DSM Sep 18 '18 at 20:17
@DSM yes you are right :-) my leak of knowledge :-( – BENY Sep 18 '18 at 20:19
1

@rahlf23: because you're only dropping `v0` in principle you could get yourself into trouble if an id value collides with something in the v columns. – DSM Sep 18 '18 at 20:20
I just noticed that as well, thank you for pointing that out. Let me account for that. – rahlf23 Sep 18 '18 at 20:20

score 2 · Answer 5 · answered Sep 18 '18 at 20:01

You can also use a lambda function:

df['newCol'] = df.apply(lambda x: int(x['v0'] in x.values[2:]), axis=1)

    id  v0  v1  v2  v3  v4  newCol
0   1   10  5   10  22  50  1
1   2   22  23  55  60  50  0
2   3   8   2   40  80  110 0
3   4   15  15  25  100 101 1

Pandas DataFrame check if column value exists in a group of columns

5 Answers5

Linked