Closed brandonkane closed 7 years ago
Just a note. The problem isn't that two functions are being passed. It's something to do with np.size being aggregated in a non-obvious way.
In [32]: expected = crosstab(rows=[df['A'],df['B']], cols=[df['C']], margins=True, values=df[
'D'], aggfunc=len)
In [33]: expected
Out[33]:
C bar foo All
A B
one A 2 2 4
B 2 2 4
C 2 2 4
three A 2 NaN 2
B NaN 2 2
C 2 NaN 2
two A NaN 2 2
B 2 NaN 2
C NaN 2 2
All 12 12 24
In [34]: odd = crosstab(rows=[df['A'],df['B']], cols=[df['C']], margins=True, values=df['D'],
aggfunc=np.size)
In [37]: odd
Out[37]:
C bar foo All
A B
one A 2 2 4
B 2 2 4
C 2 2 4
three A 2 NaN 2
B NaN 2 2
C 2 NaN 2
two A NaN 2 2
B 2 NaN 2
C NaN 2 2
All 24 24 24
OK, I'm leaning toward this being the correct behavior. The relevant line is
row_margin = data[cols + values].groupby(cols).agg(aggfunc)
We're working with the following groups:
bar C __dummy__
3 bar 1.523030
4 bar -0.234153
5 bar -0.234137
9 bar 0.542560
10 bar -0.463418
11 bar -0.465730
15 bar -0.562288
16 bar -1.012831
17 bar 0.314247
21 bar -0.225776
22 bar 0.067528
23 bar -1.424748
foo C __dummy__
0 foo 0.496714
1 foo -0.138264
2 foo 0.647689
6 foo 1.579213
7 foo 0.767435
8 foo -0.469474
12 foo 0.241962
13 foo -1.913280
14 foo -1.724918
18 foo -0.908024
19 foo -1.412304
20 foo 1.465649
With aggfunc = len
, it aggregates to 12 and 12 for foo
and bar
, since each DataFrame
is 12 items long.
With aggfunc = np.size
, it aggregates to 24 and 24 since each DataFrame
is 12x2.
So the behavior is not necessarily intuitive, but it is correct.
I guess it's also worth pointing out why they both get the same values for the All
total. That bit of code is operating on just the column passed as values
, which makes it a Series
. So len
and np.size
will both give the same answer in this case.
Tom, makes sense, thanks for investigating. I guess my intuition would have been that in the case of the 12x2 dataframe, it should have excluded one of the columns for each margin. The margins should be an aggregation of the data above it, and in this case they aren't. I would be curious to see what others would expect in this scenario.
I'm fine just using len as the aggfunc instead of size, so this isn't a high priority thing.
I just ran into this issue myself using the df.pivot_table(..., margins=True). I always felt it was somehow wrong to use np.size to count the number of rows in the groupings and now I have a reason to avoid it. In my case I had both the row and column sums doubled. Interestingly doing the pivot table on the above DataFrame does not appear to cause the problem?
df.pivot_table(values='D', rows=['A', 'B'], cols='C', aggfunc=np.size, fill_value=0, margins=True)
Out[14]:
C bar foo All
A B
one A 2 2 4
B 2 2 4
C 2 2 4
three A 2 0 2
B 0 2 2
C 2 0 2
two A 0 2 2
B 2 0 2
C 0 2 2
All 12 12 24
df.pivot_table(values='D', rows=['A', 'B'], cols='C', aggfunc=len, fill_value=0, margins=True)
Out[15]:
C bar foo All
A B
one A 2 2 4
B 2 2 4
C 2 2 4
three A 2 0 2
B 0 2 2
C 2 0 2
two A 0 2 2
B 2 0 2
C 0 2 2
All 12 12 24
@nickeubank can you confirm this is still an issue?
@jreback Looks fixed to me --
pd.crosstab(index=[df['A'],df['B']], columns=[df['C']], margins=True, aggfunc=[np.size, np.mean], values=df['D'])
Out[243]:
size mean
C bar foo All bar foo All
A B
one A 2 2 4 1.152823 0.455740 0.804282
B 2 2 4 -0.033598 0.037608 0.002005
C 2 2 4 0.081817 -0.449494 -0.183838
three A 2 NaN 2 -0.146240 NaN -0.146240
B NaN 2 2 NaN -0.269695 -0.269695
C 2 NaN 2 0.063828 NaN 0.063828
two A NaN 2 2 NaN -0.496059 -0.496059
B 2 NaN 2 -0.394447 NaN -0.394447
C NaN 2 2 NaN 0.863030 0.863030
All 12 12 24 0.120697 0.023522 0.072109
See 12, 12, 24 along bottom row?
hmm, ok. so if you want to do a PR for a confirming test would be great.
if you want to see if you can figure out where this was originally fixed we can reference.
See http://stackoverflow.com/questions/17236852/pandas-crosstab-double-counting-when-using-two-aggregate-functions for discussion.
To reproduce: Create a test dataframe:
Crosstab gives the expected results:
However, if you try to get mean and count in the same crosstab, specifying values for the mean, the crosstab will double count the elements when calculating the margin total: