Missing column after pandas groupby

Question

I have a pandas dataframe df . I group it into 3 columns and count the results. When I do this, I lose some information, in particular, the name column. This column displays 1: 1 with the desk_id column. Do I need to include both in my last framework?

here is the data frame:

  shift_id shift_start_time shift_end_time name end_time desk_id shift_hour 0 37423064 2014-01-17 08:00:00 2014-01-17 12:00:00 Adam Scott 2014-01-17 10:16:41.040000 15557987 2 1 37423064 2014-01-17 08:00:00 2014-01-17 12:00:00 Adam Scott 2014-01-17 10:16:41.096000 15557987 2 2 37423064 2014-01-17 08:00:00 2014-01-17 12:00:00 Adam Scott 2014-01-17 10:52:17.402000 15557987 2 3 37423064 2014-01-17 08:00:00 2014-01-17 12:00:00 Adam Scott 2014-01-17 11:06:59.083000 15557987 3 4 37423064 2014-01-17 08:00:00 2014-01-17 12:00:00 Adam Scott 2014-01-17 08:27:57.998000 15557987 0 

I group it like this:

 grouped = df.groupby(['desk_id', 'shift_id', 'shift_hour']).size() grouped = grouped.reset_index() 

And here is the result: the name column is missing.

  desk_id shift_id shift_hour 0 0 14468690 37729081 0 7 1 14468690 37729081 1 3 2 14468690 37729081 2 6 3 14468690 37729081 3 5 4 14468690 37729082 0 5 

Also, to rename the count column as 'count' instead of '0'?

Answer 1

You need to include 'name' in groupby by group:

 In [43]: grouped = df.groupby(['desk_id', 'shift_id', 'shift_hour', 'name']).size() grouped = grouped.reset_index() grouped.columns=np.where(grouped.columns==0, 'count', grouped.columns) #replace the default 0 to 'count' print grouped desk_id shift_id shift_hour name count 0 15557987 37423064 0 Adam Scott 1 1 15557987 37423064 2 Adam Scott 3 2 15557987 37423064 3 Adam Scott 1 

If the name-to-id relationship is a multi-valued type, let's say we have a pete scott for the same dataset, the result will look like this:

  desk_id shift_id shift_hour name count 0 15557987 37423064 0 Adam Scott 1 1 15557987 37423064 0 Pete Scott 1 2 15557987 37423064 2 Adam Scott 3 3 15557987 37423064 2 Pete Scott 3 4 15557987 37423064 3 Adam Scott 1 5 15557987 37423064 3 Pete Scott 1