2

Input:

import pandas as pd
data = pd.DataFrame(data={'date':[pd.Timestamp('2016-02-15')]*3, 
    'time':[pd.Timedelta(x) for x in ('07:30:00','10:10:00','11:10:00')],'name':['A']*3, 'N':[1,2,3]}
).set_index(['date','time','name']).sort_index()
data = data[ data.index.get_level_values('time')>=pd.to_timedelta('09:30:00') ]
dataGB = data['N'].groupby(['date','name'])
print(data)
print('Number of groups:',len(dataGB))
print(dataGB.sum())
print(pd.__version__)

Output:

>>> print(data)
                          N
date       time     name   
2016-02-15 10:10:00 A     2
           11:10:00 A     3
>>> print('Number of groups:',len(dataGB))
Number of groups: 2
>>> print(dataGB.sum())
date    2
name    3
Name: N, dtype: int64
>>> print(pd.__version__)
0.24.1

Questions:

  1. Why do I get 2 groups while clearly there should be only one?
  2. Why do I get garbage from dataGB.sum() and what to do to get expected (below) result?

Expected result of dataGB.sum():

>>> dataGB.sum()
date        name
2016-02-15  A       5
Name: N, dtype: int64

Thank you for your help!

S.V
  • 2,149
  • 2
  • 18
  • 41
  • 2
    you should access the column you want _after_ the `groupby` --> `data.groupby(['date', 'name'])['N'].sum() ` – gold_cy Mar 12 '19 at 19:42
  • Could you please explain why it matters? – S.V Mar 12 '19 at 19:43
  • because in this instance you are specifying you want to `sum` on the `N` column which you are not doing when you try and access the column beforehand – gold_cy Mar 12 '19 at 19:47
  • @aws_apprentice If I access the column beforehand, I essentially do `groupby` on a `pd.Series`, while if do it after, I select the column on which the subsequent operation should be applied. It is not clear to me why the former way to do it is incorrect: it seems that the results should be identical. – S.V Mar 12 '19 at 19:54
  • 1
    I think this is a bug with multiindex and grouping on index labels for pd.Series.groupby. This statement works if you use double brackets around 'N' and covert it to a dataframe instead of a series. – Scott Boston Mar 12 '19 at 19:57
  • 1
    Also, this statement works if you group by using the `level` paramenter instead of index level names. – Scott Boston Mar 12 '19 at 19:58
  • @ScottBoston According to [Grouping DataFrame with Index Levels and Columns](http://pandas.pydata.org/pandas-docs/stable/user_guide/groupby.html#grouping-dataframe-with-index-levels-and-columns) : *Index level names may be specified as keys directly to groupby* (starting with version 0.20). So, what I did should be correct. – S.V Mar 12 '19 at 20:07
  • I agree, this maybe a bug with pd.Series.groupby that is not present in pd.DataFrame.groupby. Note: when you use data[['N']] vs data['N'] it works as expected. – Scott Boston Mar 12 '19 at 20:07

2 Answers2

2

This may be a bug with pd.Series.groupby, I will submit a bug report in pandas for this case.

Work Around #1 use a pd.DataFrame instead of pd.Series 

data[['N']].groupby(['date','name']).sum()

Output:

                 N
date       name   
2016-02-15 A     5

Work Around #2 use the level parameter in groupby

data['N'].groupby(level=[0,2]).sum()

Output:

date        name
2016-02-15  A       5
Name: N, dtype: int64

Work Around #3 use a dataframe with an aggregator column:

 data.groupby(['date', 'name'])['N'].sum()

Output:

date        name
2016-02-15  A       5
Name: N, dtype: int64
Scott Boston
  • 147,308
  • 15
  • 139
  • 187
  • 1
    Work Around #3 was point out above in comments by @aws_apprentice, i.e. `data.groupby(['date', 'name'])['N'].sum()` – S.V Mar 12 '19 at 20:11
  • Yes, workaround #3 is using a dataframe also. Thanks I will add this to the solution. – Scott Boston Mar 12 '19 at 20:13
  • I did not see a corresponding potential bug submission on pandas github, so I have just submitted one: [Pontential bug in Series groupby #25704](https://github.com/pandas-dev/pandas/issues/25704) – S.V Mar 13 '19 at 12:52
1

According to this post, Python Pandas - how to do group by on a multiindex, grouping on a multiindex should be done like this

dataGB = data['N'].groupby(level=[0,2])
mortysporty
  • 2,749
  • 6
  • 28
  • 51