Groupby pandas dataframe based on custom list of possible values

user_847773 0
Viewed 117

I want to groupby values in two columns. I know all the possible values in the columns. In some data examples certain values in the columns a are not present. I would still like the output of groupby say the len of the group is zero.

          s      a  
0  Michaels     FS
1  Michaels     FS
2  Michaels    fds
3  Michaels   fnfe
4  Rogers       FS
5  Rogers      fds
6  Rogers      fds
7  Rogers      ssn

I want to groupby both s and a.

df.groupby(by=["s", "a"]).size()

If you look at the data-sample fnfe and ssn are not present for both Michaels and Rogers. So there will be no output for Michaels-ssn and Rogers-fnfe.

I found a way to get around this by:

df.groupby(by=["s", "a"]).size().unstack().fillna(0).stack()

But then I figured that there is a possibility that there will be several values of a that will be in neither group, but I still them in the output with their value set to zero. The s column does not have such requirements.

Let's say there is another value for the "a" column "adg", that is not present in any example. The desired output would be:

s         a   
Michaels  FS      2
          fds     1
          fnfe    1
          ssn     0
          adg     0
Rogers    FS      1
          fds     2
          ssn     1
          fnfe    0
          adg     0
pandas-groupby pandas python
1 Answers

Use with Series.reindex with MultiIndex.from_product and add values to lists with unique values:

out = df.groupby(by=["s", "a"]).size()

s = df['s'].unique()
a = df['a'].unique().tolist() + ['adg']

out = out.reindex(pd.MultiIndex.from_product([s, a], names=['s','a']), fill_value=0)

print (out)
s         a   
Michaels  FS      2
          fds     1
          fnfe    1
          ssn     0
          adg     0
Rogers    FS      1
          fds     2
          fnfe    0
          ssn     1
          adg     0
dtype: int64

Your solution:

a = df['a'].unique().tolist() + ['adg']

out = (df.groupby(by=["s", "a"]).size()
         .unstack(fill_value=0)
         .reindex(a, fill_value=0, axis=1)
         .stack())

print (out)
s         a   
Michaels  FS      2
          fds     1
          fnfe    1
          ssn     0
          adg     0
Rogers    FS      1
          fds     2
          fnfe    0
          ssn     1
          adg     0
dtype: int64

Another idea is use Categorical:

df['a'] = pd.Categorical(df['a'], categories=df['a'].unique().tolist() + ['adg'])

out = df.groupby(by=["s", "a"]).size()
print (out)
s         a   
Michaels  FS      2
          fds     1
          fnfe    1
          ssn     0
          adg     0
Rogers    FS      1
          fds     2
          fnfe    0
          ssn     1
          adg     0
dtype: int64
user_29010020
Related