GroupBy.count() returns the grouping column as both index and column

[jorisvandenbossche]

GroupBy.count() (with the default as_index=True) return the grouping column both as index and as column, while other methods as first and sum keep it only as the index (which is most logical I think). This seems a minor inconsistency to me:

In [41]: data = pd.DataFrame({'name' : ['a', 'a', 'b', 'd'], 'counts' : [3,4,3,2]})
In [42]: data
Out[42]:
   counts name
0       3    a
1       4    a
2       3    b
3       2    d

In [43]: g = data.groupby('name')
In [45]: g.count()
Out[45]:
      counts  name
name
a          2     2
b          1     1
d          1     1

In [46]: g.first()
Out[46]:
      counts
name
a          3
b          3
d          2

In [47]: g.sum()
Out[47]:
      counts
name
a          7
b          3
d          2

[jreback]

hmm....

@hayd @TomAugspurger

these look right? are first/last just different?

In [2]: df= pd.DataFrame({'name' : ['a', 'a', 'b', 'd'], 'counts' : [3,4,3,2]})

In [3]: g = df.groupby('name')

In [4]: g.count()
Out[4]: 
      counts  name
name              
a          2     2
b          1     1
d          1     1

[3 rows x 2 columns]

In [5]: g.first()
Out[5]: 
      counts
name        
a          3
b          3
d          2

[3 rows x 1 columns]

In [6]: g.head()
Out[6]: 
   counts name
0       3    a
1       4    a
2       3    b
3       2    d

[4 rows x 2 columns]

In [7]: g.tail()
Out[7]: 
   counts name
0       3    a
1       4    a
2       3    b
3       2    d

[4 rows x 2 columns]

In [8]: g.last()
Out[8]: 
      counts
name        
a          4
b          3
d          2

[3 rows x 1 columns]

[hayd]

IMO first should do the same as g.nth(0) and last as g.nth(-1), since as mentioned in the larger PR they are not aggregations (I think breaking these are on the roadmap for 0.14?). First and last are implemented as aggs atm.

Original issue is that count includes name, I also don't think it should. Will have a look at this, may be simple fix. Related to cumsum etc. including the grouped by columns (so may be a generic fix in agg).

[jreback]

hmm...though you already redefined first/last to be nth's...oh well...that sounds right to me

[hayd]

duh, count's applying when it should be agg-ing. PR shortly.

[jreback]

http://stackoverflow.com/questions/23352418/unexpected-behavior-in-pandas-mad-with-groupby/23352706#23352706

mad is the same problem (as are prob the non-cythonized calls).

[jreback]

@hayd you are doing a PR for this one? or is it already out there but missing a reference?

[hayd]

apparently I did on my machine, rebased and pushed will see if it passes... mañana

[jreback]

gr8!

[jreback]

These seem completely wrong (I haven't changed anything yet to exlucde the 'A' column
when testing these)

In [1]: df = DataFrame([[1, 2, 'foo'], [1, nan, 'bar',], [3, nan, 'baz']], columns=['A', 'B','C'])

In [2]: df
Out[2]: 
   A   B    C
0  1   2  foo
1  1 NaN  bar
2  3 NaN  baz

[3 rows x 3 columns]

In [3]: df.groupby('A').shift(1)
Out[3]: 
    A   B    C
0 NaN NaN  NaN
1   1   2  foo
2 NaN NaN  NaN

[3 rows x 3 columns]

In [4]: df.groupby('A').fillna(-1)
Out[4]: 
   A  B    C
0  1  2  foo
1  1 -1  bar
2  3 -1  baz

[3 rows x 3 columns]

In [5]: df.groupby('A').apply(lambda x: x.fillna(-1))
Out[5]: 
   A  B    C
0  1  2  foo
1  1 -1  bar
2  3 -1  baz

[3 rows x 3 columns]