Pandas Grouping and Transform Ignoring NaN

user_202375 0
Viewed 476

I'm facing an issue with grouping and transforming on non-NA values in my dataframe.

So my dataframe is something like this:

Name Value
A 1
A 2
A NaN
B 3
B 7
B 9
B NaN

Final output I want:

Name Value Weight 1 Weight 2
A 1 0.33 0.5
A 2 0.33 0.5
A NaN 0.33 NaN
B 3 0.25 0.33
B 7 0.25 0.33
B 9 0.25 0.33
B NaN 0.25 NaN

I know this may sound trivial but I'm not able to get the Weight 2 working perfectly across different grouping categories of column Name.

Here's how I get column Weight 1:

df['Weight 1'] = df.groupby(['Name']).transform(lambda x: 1/len(x))

So far I tried following on Weight 2, but raises DivisionByZero warning. Output is incorrect.

df['Weight 2'] = df.groupby(['Name']).transform(lambda x: 1/np.sum(~np.isnan(x)))

Any help is appreciated.

pandas-groupby pandas dataframe numpy python
2 Answers

You can use GroupBy.count to count Non-NaN values in each group. Then use pd.Series.map with pd.Series.mask

mapping = (1 / df.groupby('Name')['Value'].count()).squeeze()
df['Weight 2'] = df['Name'].map(mapping).mask(df['Value'].isna())

  Name  Value  Weight 2
0    A    1.0  0.500000
1    A    2.0  0.500000
2    A    NaN       NaN
3    B    3.0  0.333333
4    B    7.0  0.333333
5    B    9.0  0.333333
6    B    NaN       NaN
user_124164530

count ignores nulls when counting, size does not:

grouper = df.groupby('Name')
(df.assign(Weight1 = 1/grouper.Value.transform('size'), 
          Weight2 = 1/grouper.Value.transform('count'))
   .assign(Weight2 = lambda df: np.where(df.Value.notna(), 
                                        df.Weight2, np.nan))
 
  Name  Value   Weight1   Weight2
0    A    1.0  0.333333  0.500000
1    A    2.0  0.333333  0.500000
2    A    NaN  0.333333       NaN
3    B    3.0  0.250000  0.333333
4    B    7.0  0.250000  0.333333
5    B    9.0  0.250000  0.333333
6    B    NaN  0.250000       NaN
user_71757130
Related