CODEWITHSUNDEEP
pythonpandasdataframeapply
Nobel
Nobel

Reputation: 1555

Compute if value exists in a column on lists in pandas dataframe

I have 2 columns in my dataframe

  1. product ID purchased by the customer "p"

  2. list of products IDs purchased by similar customers "p_list"

    df = pd.DataFrame({'p': [12, 4, 5, 6, 7, 7, 6,5],'p_list':[[12,1,5], [3,1],[8,9,11], [6,7,9], [7,1,2],[12,9,8], [6,1,15],[6,8,9,11]]})

I want to check if "p" exists on "p_list" or not, so I applied this code

df["exist"]= df.apply(lambda r: 1 if r["p"] in r["p_list"] else 0, axis=1)

The problem is that I have around 50 million rows in this dataframe, so it takes very long time to execute.

Is there more efficient way to compute this column?

Thanks.

Upvotes: 4

Views: 3265

Answers (1)

jezrael
jezrael

Reputation: 862681

You can use list comprehension, last cast True, False values to int:

df["exist"] = [r[0] in r[1]  for r in zip(df["p"], df["p_list"])]
df["exist"] = df["exist"].astype(int)
print (df)
    p         p_list  exist
0  12     [12, 1, 5]      1
1   4         [3, 1]      0
2   5     [8, 9, 11]      0
3   6      [6, 7, 9]      1
4   7      [7, 1, 2]      1
5   7     [12, 9, 8]      0
6   6     [6, 1, 15]      1
7   5  [6, 8, 9, 11]      0

df["exist"] = [int(r[0] in r[1])  for r in zip(df["p"], df["p_list"])]
print (df)
    p         p_list  exist
0  12     [12, 1, 5]      1
1   4         [3, 1]      0
2   5     [8, 9, 11]      0
3   6      [6, 7, 9]      1
4   7      [7, 1, 2]      1
5   7     [12, 9, 8]      0
6   6     [6, 1, 15]      1
7   5  [6, 8, 9, 11]      0

Timings:

#[8000 rows x 2 columns]
df = pd.concat([df]*1000).reset_index(drop=True)
print (df)

In [89]: %%timeit
    ...: df["exist2"] = [r[0] in r[1]  for r in zip(df["p"], df["p_list"])]
    ...: df["exist2"] = df["exist2"].astype(int)
    ...: 
100 loops, best of 3: 6.07 ms per loop

In [90]: %%timeit
    ...: df["exist"] = [1 if r[0] in r[1] else 0  for r in zip(df["p"], df["p_list"])]
    ...: 
100 loops, best of 3: 7.16 ms per loop

In [91]: %%timeit
    ...: df["exist"] = [int(r[0] in r[1])  for r in zip(df["p"], df["p_list"])]
    ...: 
100 loops, best of 3: 9.23 ms per loop

In [92]: %%timeit
    ...: df['exist1'] = df.apply(lambda x: x.p in x.p_list, axis=1).astype(int)
    ...: 
1 loop, best of 3: 370 ms per loop

In [93]: %%timeit
    ...: df["exist"]= df.apply(lambda r: 1 if r["p"] in r["p_list"] else 0, axis=1)
1 loop, best of 3: 310 ms per loop

Upvotes: 6

Related Questions