Pandas Groupby, MultiIndex, Multiple Columns

Question

I just worked on creating some columns using .transform() to count some entries. I used this reference.

For example:

          userID deviceName  POWER_DOWN  USER  LOW_RSSI  NONE  CMD_SUCCESS  
0         24      IR_00          85     0        39     0            0   
1         24      IR_00          85     0        39     0            0   
2         24      IR_00          85     0        39     0            0   
3         24      IR_00          85     0        39     0            0   
4         25     BED_08           0   109        78     0            0   
5         25     BED_08           0   109        78     0            0   
6         25     BED_08           0   109        78     0            0   
7         24      IR_00          85     0        39     0            0   
8         23      IR_09           2     0         0     0            0   
9         23     V33_17           3     0         2     0          134   
10        23     V33_17           3     0         2     0          134   
11        23     V33_17           3     0         2     0          134   
12        23     V33_17           3     0         2     0          134

I want to group them by userID and deviceName? So that it would look like:

          userID deviceName  POWER_DOWN  USER  LOW_RSSI  NONE  CMD_SUCCESS  
0         23      IR_09           2     0         0     0            0
1                V33_17           3     0         2     0          134
2         24      IR_00          85     0        39     0            0
3         25     BED_08           0   109        78     0            0

I also want them to be sorted by userID and maybe make userID and deviceName as multi-index.

I tried the df = df.groupby(['userID', 'deviceName']) but returned a <pandas.core.groupby.DataFrameGroupBy object at0x00000249BBB13DD8>. not the dataframe.

By the way, Im sorry. I dont know how to copy a Jupyter notebook In and Out.

jezrael · Accepted Answer · 2018-07-31T06:35:04.877

I believe need drop_duplicates with sort_values:

df1 = df.drop_duplicates(['userID', 'deviceName']).sort_values('userID')
print (df1)
   userID deviceName  POWER_DOWN  USER  LOW_RSSI  NONE  CMD_SUCCESS
8      23      IR_09           2     0         0     0            0
9      23     V33_17           3     0         2     0          134
0      24      IR_00          85     0        39     0            0
4      25     BED_08           0   109        78     0            0

If want create MultiIndex add set_index:

df1 = (df.drop_duplicates(['userID', 'deviceName'])
         .sort_values('userID')
         .set_index(['userID', 'deviceName']))
print (df1)
                   POWER_DOWN  USER  LOW_RSSI  NONE  CMD_SUCCESS
userID deviceName                                               
23     IR_09                2     0         0     0            0
       V33_17               3     0         2     0          134
24     IR_00               85     0        39     0            0
25     BED_08               0   109        78     0            0

I would also add `df.reset_index()` so that the index has consecutive values. — sophros, Jul 31 '18 at 06:34
@sophros - Yes, if not `MultiIndex` necessary `.set_index()` should be omit. — jezrael, Jul 31 '18 at 06:37

Pandas Groupby, MultiIndex, Multiple Columns

1 Answers1