Determining unique usage in a Pandas array

user_12906895 0
Viewed 46

I have a large dataset (1M+ rows, and 50 columns). I've created a fake dataset below to highlight my issue. I'm trying to determine the parts that are used ONLY used on Ford OR VW (and not on other car brands), but am not sure how to approach this.

In reality, I have about 500 'car brands', and may want to look for 30+ of these, therefore if I can search by isin(list), that would be perfect.

data = { 'part': ['A','A','B','B','B','C','C','D','D','D','D','E','F','F','G'],'make':  ['VW','Ford','VW','Nissan','Ford','VW','Nissan','Ford','Mazda','VW','Ford','VW','Ford','Mazda','Ford'],}
df = pd.DataFrame(data)

For Ford or VW, the result should be A,E and G. (As A has only Ford and VW, and E has only VW, and G has only Ford)

pandas dataframe filter python-3.x
3 Answers

CODE:

make = ['Ford', 'VW']
mask = df['make'].isin(make).groupby(df['part']).all()
parts = mask[mask].index

DETAILS:

Create a boolean mask using Series.isin which represent the condition where the column make contains the brands from the predefined make list:

>>> df['make'].isin(make)

0      True
1      True
2      True
3     False
4      True
5      True
6     False
7      True
8     False
9      True
10     True
11     True
12     True
13    False
14     True
Name: make, dtype: bool

Group the above boolean mask on column part and aggregate using all to create a new boolean mask representing the condition where all the boolean values in the above mask are True per part:

>>> mask

part
A     True
B    False
C    False
D    False
E     True
F    False
G     True
Name: make, dtype: bool

Finally use boolean indexing to filter out the index values in the above boolean mask to get the list of parts that are only used by the brands in the predefined make list:

>>> parts

Index(['A', 'E', 'G'], dtype='object', name='part')
user_128331660
  • for efficiency, calculate mask once
  • get list of parts used in Ford or VW
  • get list of parts used in other makes
  • finally a mask to get required parts
data = {'part': ['A','A','B','B','B','C','C','D','D','D','D','E','F','F'],'make': 
['VW','Ford','VW','Nissan','Ford','VW','Nissan','Ford','Mazda','VW','Ford','VW','Ford','Mazda'],}
df = pd.DataFrame(data)

mask = df.make.isin(["VW","Ford"])
used = np.unique(df.loc[mask,["part"]])
otherused = np.unique(df.loc[~mask,["part"]])

pd.Series(used)[lambda s: ~s.isin(otherused)]

output

0    A
4    E
dtype: object
user_94414040

This might work:

# parts used for only one make
part_usage = df.assign(grouped_make = lambda x: x['make'].replace({'VW': 'custom_make', 'Ford': 'custom_make'})).groupby('part')['grouped_make'].nunique()

part_usage = part_usage[part_usage.eq(1)]

print(part_usage.index)
user_106935960
Related