I am new to pandas and looking a way to find missing values in columns 'a' from column 'b'. How to get the following result?
MWE
import numpy as np
import pandas as pd
df = pd.DataFrame({'a': [[1,2,3],[10,20,30]],
'b': [[1,2],[20]]})
df['required'] = [[3],[10,30]]
df
a b required
0 [1, 2, 3] [1, 2] [3]
1 [10, 20, 30] [20] [10, 30]
The column 'c' should have all the elements from column 'a' that are not present in column 'b'.
I am looking for a vectorized way something like 'np.setdiff1d' but I could not find a way to do it. I can do it by using for loop but that would be too inefficient.
update
- there are no duplicates
- order does not matter
CodePudding user response：
You can use np.setdiff1d, just inside of apply:
df['c'] = df.apply(lambda row: np.setdiff1d(row['a'], row['b']), axis=1)
df
a b c
0 [1, 2, 3] [1, 2] [3]
1 [10, 20, 30] [20] [10, 30]
CodePudding user response：
I would use np.setdiff1d too but because your data doesn't have duplicates I would set assume_unique = True which speeds up computation.
From docs:
assume_unique : bool
If True, the input arrays are both assumed to be unique, which can speed up the calculation. Default is False.
df['c'] = df.apply(lambda x: np.setdiff1d(x['a'], x['b'], assume_unique=True), axis=1)
Alternatively with list comprehension (which should be fast too):
df['c'] = df.apply(lambda x: [e for e in x['a'] if e not in x['b']], axis=1)
CodePudding user response：
here is one way to do it, by making use of the sets.
df['diff']=df.apply(lambda x: list(set(x['a']).difference(set(x['b']))), axis=1)
df
a b required diff
0 [1, 2, 3] [1, 2] [3] [3]
1 [10, 20, 30] [20] [10, 30] [10, 30]
CodePudding user response：
I came up with this answer:
df['c'] = df['a'].apply(set) - df['b'].apply(set)
a b required c
0 [1, 2, 3] [1, 2] [3] {3}
1 [10, 20, 30] [20] [10, 30] {10, 30}
CodePudding user response：
# Make them sets, not lists:
df = df.applymap(set)
# Subtract a from b:
df['required'] = df.a.sub(df.b)
print(df)
Output:
a b required
0 {1, 2, 3} {1, 2} {3}
1 {10, 20, 30} {20} {10, 30}
Another approach using np.setdiff1d would be to "vectorize" it. (It's kinda a fake vectorization I believe, but may be nice if you want to do this a lot)
setdiff2d = np.vectorize(np.setdiff1d, otypes=[list])
df['required'] = setdiff2d(df['a'], df['b'])
# Output
a b required
0 [1, 2, 3] [1, 2] [3]
1 [10, 20, 30] [20] [10, 30]