Delete pandas cells values not included in list

user_11181726 0
Viewed 86

Given the following list:

a = ['1', '2', '12', '5']

I am trying to delete all values from the Dataframes cells which don't match the elements in the list. I know for sure that every value from the list appears only once in each row of the Dataframe.

test = pd.DataFrame({'0' : ['1','4','5','5'],
           '1' : ['4','1','12','10'],
           '2' : ['10','12','4','2'],
           '3' : ['2','10','10','4'],
           '4' : ['5','2','2','1'],
           '5' : ['12','5','1','12']})


    0   1   2   3   4   5
0   1   4   10  2   5   12
1   4   1   12  10  2   5
2   5   12  4   10  2   1
3   5   10  2   4   1   12

The important aspect is to maintain the order of the matching numbers from the list in the Dataframe.

The result should look like this:

    0   1   2   3
0   1   2   5   12
1   1   12  2   5
2   5   12  2   1
3   5   2   1   12

Thanks in advance!

pandas python-3.x
3 Answers
df.apply(lambda x: pd.Series([i for i in list(x) if i in ' '.join(a)]), axis=1)

Output

   0   1  2   3
0  1   2  5  12
1  1  12  2   5
2  5  12  2   1
3  5   2  1  12
user_81237100

You could use numpy.in1d in a python generator:

import numpy as np

# For latest version of pandas
pd.DataFrame(x[np.in1d(x, a)] for x in test.to_numpy())

# Otherwise use
pd.DataFrame(x[np.in1d(x, a)] for x in test.values)

[out]

   0   1  2   3
0  1   2  5  12
1  1  12  2   5
2  5  12  2   1
3  5   2  1  12

This method should also be faster than DataFrame.apply

Benchmarking

%%timeit
pd.DataFrame(x[np.in1d(x, a)] for x in test.to_numpy())

712 µs ± 19.3 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)


%%timeit    
test.apply(lambda x: pd.Series(x[x.isin(a)].to_numpy()), axis=1)

4.58 ms ± 185 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
user_102015800

Use Series.isin with DataFrame.apply:

#pandas 0.24+
df = test.apply(lambda x: pd.Series(x[x.isin(a)].to_numpy()), axis=1)
#pandas below
df = test.apply(lambda x: pd.Series(x[x.isin(a)].values), axis=1)
print (df)
   0   1  2   3
0  1   2  5  12
1  1  12  2   5
2  5  12  2   1
3  5   2  1  12
user_29010020
Related