aggregate pandas groupby all

l a s 0
Viewed 59

i have a dataframe that looks like the one given below.

item_id length height shape color
a       True
a       True
a              False
a              True
a                     True
b       True
b              True
b                     False
b                           True

i want to do something like

data_df.groupby('item_id').all().reset_index()

to convert the dataframe to

item_id length height shape color
a       True   False  True  False
b       True   True   False True

my problem is with color for item a which should be false as it does not exist but it comes out as true.

dataframe pandas python-3.x python
1 Answers

You can do a logical AND between the the usual df.groupby('item_id').all() frame and another frame representing whether each column has at least a non-NaN value for each group.

df.groupby('item_id').all() & ~df.iloc[:,1:].isnull().groupby(df['item_id']).all()

Output

    length  height  shape   color
item_id             
a   True    False   True    False
b   True    True    False   True

In terms of speed, I did a little benchmark on a dataframe with 10k rows.

%%timeit -r 4 -n 100
df.groupby('item_id').all()

%%timeit -r 4 -n 100
df.groupby('item_id').all() & ~df.iloc[:,1:].isnull().groupby(df['item_id']).all()

When there are 9997 different groups (~1 row per group)

Incorrect solution: 31.1 ms ± 910 µs per loop (mean ± std. dev. of 4 runs, 100 loops each)
Correct solution: 45.8 ms ± 1.22 ms per loop (mean ± std. dev. of 4 runs, 100 loops each)

When there are 625 different groups (~16 rows per group)

Incorrect solution: 24 ms ± 211 µs per loop (mean ± std. dev. of 4 runs, 100 loops each)
Correct solution: 30.7 ms ± 417 µs per loop (mean ± std. dev. of 4 runs, 100 loops each)

So the speed of the correct solution should be on the same order as that of the incorrect one.

bui0
Related