Pandas: Using group by, combine multiple column values as one distinct group within the groupby

Question

I have a data-frame which I'm using the pandas.groupby on a specific column and then running aggregate statistics on the produced groups (mean, median, count). I want to treat certain column values as members of the same group produced by the groupby rather than a distinct group per distinct value in the column which was used for the grouping. I was looking how I would accomplish such a thing.

For example:

>> my_df
ID    SUB_NUM     ELAPSED_TIME
1     1           1.7
2     2           1.4
3     2           2.1 
4     4           3.0
5     6           1.8
6     6           1.2

So instead of the typical behavior:

>> my_df.groupby([SUB_NUM]).agg([count])
ID    SUB_NUM     Count
1     1           1
2     2           2
4     4           1
5     6           2 

I want certain values (SUB_NUM in [1, 2]) to be computed as one group so instead something like below is produced:

>> # Some mystery pandas function calls
ID    SUB_NUM     Count
1     1, 2        3
4     4           1
5     6           2

Any help would be much appreciated, thanks!

Answer 1

For me works:

#for join values convert values to string
df['SUB_NUM'] = df['SUB_NUM'].astype(str)
#create mapping dict by dict comprehension
L = ['1','2']
d = {x: ','.join(L) for x in L}
print (d)
{'2': '1,2', '1': '1,2'}

#replace values by dict
a = df['SUB_NUM'].replace(d)
print (a)
0    1,2
1    1,2
2    1,2
3      4
4      6
5      6
Name: SUB_NUM, dtype: object


#groupby by mapping column and aggregating `first` and `size`
print (df.groupby(a)
         .agg({'ID':'first', 'ELAPSED_TIME':'size'})
         .rename(columns={'ELAPSED_TIME':'Count'})
         .reset_index())

  SUB_NUM  ID  Count
0     1,2   1      3
1       4   4      1
2       6   5      2

What is the difference between size and count in pandas?

Answer 2

You can create another column mapping the SUB_NUM values to actual groups and then group by it.

my_df['SUB_GROUP'] = my_df['SUB_NUM'].apply(lambda x: 1 if x < 3 else x)
my_df.groupby(['SUB_GROUP']).agg([count])