pandas GroupBy columns with NaN missing values

Question

I have a DataFrame with many missing values in columns which I wish to groupby   import pandas as pd import numpy as np df   pd DataFrame   a     1    2    3     b     4   np NaN   6      In  4   df groupby  b   groups Out 4     4    0    6    2     see that Pandas has dropped the rows with NaN target values   I want to include these rows    Since I need many such operations  many cols have missing values   and use more complicated functions than just medians  typically random forests   I want to avoid writing too complicated pieces of code   Any suggestions  Should I write a function for this or is there a simple solution

User · Answer

This is mentioned in the Missing Data section of the docs:

NA groups in GroupBy are automatically excluded. This behavior is consistent with R

One workaround is to use a placeholder before doing the groupby (e.g. -1):

In [11]: df.fillna(-1)
Out[11]: 
   a   b
0  1   4
1  2  -1
2  3   6

In [12]: df.fillna(-1).groupby('b').sum()
Out[12]: 
    a
b    
-1  2
4   1
6   3

That said, this feels pretty awful hack... perhaps there should be an option to include NaN in groupby (see this github issue - which uses the same placeholder hack).

However, as described in another answer, "from pandas 1.1 you have better control over this behavior, NA values are now allowed in the grouper using dropna=False"

User · Answer

All answers provided thus far result in potentially dangerous behavior as it is quite possible you select a dummy value that is actually part of the dataset  This is increasingly likely as you create groups with many attributes  Simply put  the approach doesn t always generalize well   A less hacky solve is to use pd drop duplicates   to create a unique index of value combinations each with their own ID  and then group on that id  It is more verbose but does get the job done   def safe groupby df  group cols  agg dict         set name of group col to unique value     group id    group id      while group id in df columns          group id     x        get final order of columns     agg col order    group cols   list agg dict keys           create unique index of grouped values     group idx   df group cols  drop duplicates       group idx group id    np arange group idx shape 0         merge unique index on dataframe     df   df merge group idx  on group cols        group dataframe on group id and aggregate values     df agg   df groupby group id  as index True                   agg agg dict        merge grouped value index to results of aggregation     df agg   group idx set index group id  join df agg        rename index     df agg index name   None       return reordered columns     return df agg agg col order    Note that you can now simply do the following   data block    np tile  None   A    3                 np repeat   B    C    3                  1     2   3    col names     col a    col b    value    test df   pd DataFrame data block  index col names  T  grouped df   safe groupby test df    col a    col b                              OrderedDict    value    sum        This will return the successful result without having to worry about overwriting real data that is mistaken as a dummy value

User · Answer

pandas  gt   1 1 From pandas 1 1 you have better control over this behavior  NA values are now allowed in the grouper using dropna False  pd   version      1 1 0 dev0 2004 g8d10bfb6f     Example from the docs df     a    b  c 0  1  2 0  3 1  1  NaN  4 2  2  1 0  3 3  1  2 0  2    without NA  the default  df groupby  b   sum         a  c b         1 0  2  3 2 0  2  5     with NA df groupby  b   dropna False  sum         a  c b         1 0  2  3 2 0  2  5 NaN  1  4

User · Answer

One small point to Andy Hayden s solution     it doesn t work  anymore   because np nan    np nan yields False  so the replace function doesn t actually do anything   What worked for me was this   df  b     df  b   apply lambda x  x if not np isnan x  else -1     At least that s the behavior for Pandas 0 19 2  Sorry to add it as a different answer  I do not have enough reputation to comment

User · Answer

I answered this already  but some reason the answer was converted to a comment   Nevertheless  this is the most efficient solution   Not being able to include  and propagate  NaNs in groups is quite aggravating  Citing R is not convincing  as this behavior is not consistent with a lot of other things  Anyway  the dummy hack is also pretty bad  However  the size  includes NaNs  and the count  ignores NaNs  of a group will differ if there are NaNs   dfgrouped   df groupby   b    a agg   sum   size   count     dfgrouped  sum   dfgrouped  size    dfgrouped  count      None   When these differ  you can set the value back to None for the result of the aggregation function for that group

User · Answer

Ancient topic  if someone still stumbles over this--another workaround is to convert via  astype str  to string before grouping  That will conserve the NaN s  df   pd DataFrame   a     1    2    3     b     4   np NaN   6     df  b     df  b   astype str  df groupby   b    sum        a b    4   1 6   3 nan 2

User · Answer

I am not able to add a comment to M  Kiewisch since I do not have enough reputation points  only have 41 but need more than 50 to comment    Anyway  just want to point out that M  Kiewisch solution does not work as is and may need more tweaking  Consider for example   gt  gt  gt  df   pd DataFrame   a    1  2  3  5    b    4  np NaN  6  4     gt  gt  gt  df    a    b 0  1  4 0 1  2  NaN 2  3  6 0 3  5  4 0  gt  gt  gt  df groupby   b    sum        a b 4 0  6 6 0  3  gt  gt  gt  df astype str  groupby   b    sum         a b 4 0  15 6 0   3 nan   2   which shows that for group b 4 0  the corresponding value is 15 instead of 6  Here it is just concatenating 1 and 5 as strings instead of adding it as numbers

[python] pandas GroupBy columns with NaN (missing) values

Examples related to python

Examples related to pandas

Examples related to group-by

Examples related to pandas-groupby

Examples related to nan