Add normalization to crosstab
State: closed

Closes #12569

Note does NOT address #12577


Labels:
name: Enhancement
name: Reshaping
description: Concat, Merge/Join, Stack/Unstack, Explode

Comments:
Thanks for the PR:)

As pointed in #12569, I prefer adding new `aggfunc` to new option (`normalize`). For example, isn't `agfunc="std"` and `normalize=True` meaningless?

@sinhrks Yes, combining an `aggfunc` with normalize would be a problem, which is why I added an exception for that. 

(Or rather, I added two exceptions -- one for passing `aggfunc` without `values` which I think should raise an exception since when that happens `aggfunc` is ignored silently, and one for passing `normalize` with `values`. Together, they ensure no one tries to combine `normalize` with `values` or an `aggfunc`)

Since I can't think of an actual function you could pass to `aggfunc` that would do row or column normalization (unless I'm mistaken?), I'm not in favor of it being treated like it's just a special `aggfunc`. Since it's doing something you can't do with an `aggfunc`, seems like it should stand alone. 

@nickeubank, no you simply allow string args to `aggfunc='normalize'`or whatever, its less confusing (and you can of course always pass an actual function)

what @sinhrks suggests prob makes the most sense

`pct, pct_row, pct_col`; pretty clear what they mean (and the doc-string will reinforce these). these are convience aggregators.

@jreback @sinhrks ok, will update!

@sinhrks little better?

I think it is nice if `pct_xxx` can normalize sum of values if `values` specified (and all of them are positive). 

```
a = np.array([1, 1, 2, 2])
b = np.array([1, 2, 1, 2])
c = np.array([10, 20, 40, 80])
pd.crosstab(a, b, c, aggfunc=np.sum)
# col_0   1   2
# row_0        
# 1      10  20
# 2      40  80

pd.crosstab(a, b, c, aggfunc='pct_cols')
# col_0   1    2
# row_0        
# 1     0.2  0.2
# 2     0.8  0.8
```

I am slightly -1 on adding this functionality to `aggfunc`:
- First, having both the possibility of passing a func or a string (that not maps directly to an existing function) seems a bit unpythonic, and it is really about two different cases: how to aggregate multiple values, or whether or not to normalize the final result.
- Currently, you can already pass strings to `aggfunc` to specify the aggregation function (like in groupby.agg), so the current implementation checking for those specific strings will break that functionality. 
  Eg something like `pd.crosstab(index, columns, values, aggfunc='sum')`
- `value_counts` already has the `normalize` keyword, so there is a precedent for this (and I think it is an equivalent functionality, so would be more consistent to use that?)
- as @sinhrks just mentions, you may also want to normalize the result of an aggregation (eg `pd.crosstab(index, columns, values, aggfunc='sum', normalize=True`). That would not be possible with the current proposed API.

Thanks @jorvisvandenbosse -- I think that's well said. Perhaps we should solicit a few more opinions to see if we can move towards consensus on this? 

@jorisvandenbossche soln seems reasonable.

you _might_ want to enable `normalize=True` -> `pct`, `normalize='index'` -> `pct_rows`, `normalize='columns'` -> 'pct_cols'

That would be my preference

> you might want to enable normalize=True -> pct, normalize='index' -> pct_rows, normalize='columns' -> 'pct_cols'

+1

My opinion is based on the understanding that `crosstab` is similar to `pivot_table`. Thus:
- Because `pivot_table` accepts `str`, I think it is OK to add other `str` options to `aggfunc`.
- `aggfunc="pct", "pct_cols", "pct_rows"` outputs normalize counts if values is not specified, and normalize values if values is specified.
- I don't distinguish aggregate/normalize because `aggfunc` can be defined fornormalization. (`mean` is `(category sum)/(category counts)`, `pct` is `(category sum)/(total counts)`)

I don't have strong opposition against @jorisvandenbossche 's option. One concern is API gets complex more than required. It's less likely to normalize other values than count and sum.

> Because pivot_table accepts str, I think it is OK to add other str options to aggfunc

That's what I said above as well. `crosstab`'s `aggfunc` also accepts strings like `groupby().agg()` accepts strings. But there it are strings that match an existing function/method. This is not the case with 'pct_cols/rows'.

> One concern is API gets complex more than required.

Indeed, in general I am very reluctant in adding new keyword arguments. But in this case, I think it makes it more complex to explain what `aggfunc` does in case of combining it. It seems cleaner to separate the two notions

> It's less likely to normalize other values than count and sum.

That's indeed true.

Sounds like we're agreed `aggfunc` can take strings (I'll remove that exception!)

I think my main two thoughts are:
- There is no function that could do row-normalization. Thus, since it's functionally doing something different than any explicit `aggfunc` could do, it should be it's own option, even though I agree with @sinhrks  there are few situations when you'd want to normalize the output of a crosstab generated with a custom `aggfunc`. Indeed, other programs like Stata that do something similar (accept aggregation functions for table) also split out normalization into its own syntax. 
- As @jorisvandenbossche has pointed out, `normalize` is already a keyword in a core function (`value_counts`), so I think it's pretty API consistent to have. 

Regarding complexity, I think of crosstab as a tool that may be used to generate analysis outputs to potentially put in papers (that's my interest at least). Given that, I think making it as flexible and powerful is highly desirable, even at the cost of an extra key word.

so we are talking about doing these ops:

```
In [20]: r = pd.crosstab(df.a,df.b)

In [21]: r
Out[21]: 
b  3  4
a      
1  1  0
2  1  3

In [22]: r/r.sum()
Out[22]: 
b    3    4
a          
1  0.5  0.0
2  0.5  1.0

In [23]: r/r.sum().sum()
Out[23]: 
b    3    4
a          
1  0.2  0.0
2  0.2  0.6

In [24]: r.div(r.sum(1),axis='index')
Out[24]: 
b     3     4
a            
1  1.00  0.00
2  0.25  0.75
```

This kind of feels like a post-processing step rather than a result of a single operation.

You can indeed rather simple do this after `crosstab` (although the row-wise case is not that trivial anymore), but still I would be OK to add a convenience keyword for this.

yeah, just thinking if we have a kw (which is fine), then should explain what it is actually doing in the doc-string. As not completely obvious.

another option, is _maybe_ to actually have a `.normalize(...)` function on frames. 

`pd.crosstab(df.a, df.b).normalize('columns')`

Yes, it's post-processing. Indeed, that's how it's implemented. 

The one complication with a stand-alone normalize is that it can't be easily designed to deal with the `margin` columns -- if you do `crosstab(df.a, df.b, margins=True)` then you have to do a special normalization procedure to deal with the fact that the bottom row is a sum of all the rows above. A method that just divides all values by the total sum, you end up off by a factor of 1/2. I really thing there's value in having this as an integrated method. 

@nickeubank well one could argue the `margins` itself should be an external method as well! e.g. its kind of a 'footer' concept almost like a collection of DataFrame + footer Series + footer Series (rhs).

So that's really another abstraction and just being shoved into the current one.

@jreback `margins` would only work as an external method for frequency tables -- for any other function (like mean), it's a set of `groupby` calls for different populations.

Also, say we made separate methods just for summing up values in each row or column called `add_margins` and had a separate `normalize`. It would still be impossible to make a normalized frequency table with margins. 

Say you had a frequency table `df` and called `df.add_margins().normalize()`, you'd end up with all the values off by a factor of 1/2. And if you called `df.normalize().add_margins()`, then unless you happened to have the same number of observations in each cell of `df`, the answers you'd get is wrong since you lose the "weight" of each cell coming from the number of observations when you normalize it.  

@nickeubank you missed my point. I was talking about a DIFFERENT abstraction, noticed I used the form: DataFrame + footer Series + footer Series (rhs), which in essence are additional data carried along (kind of like an Index). This doesn't exist ATM, and the use case is prob somewhat limited. My point is that it would work as the further methods would have access to the orginal data (and the margins), and then could easily normalize properly.

But that's just pie in the sky as it would require quite some effort. 

Ah, I see. Yes, that would be nice!

In any case, regardless of this bit broader discussion, I think the original intent of this PR is OK? So @nickeubank I think you can certainlly update it.

Sounds good. I'll update shortly!

@jreback @jorisvandenbossche Updated. Comments on syntax? Will add a whatsnew when all else settled. 

@nickeubank can divine what things are being done with custom aggfuncs? (e.g. see what tests do for this). Then you can see if it even makes sense to normalize for them.

@jreback I'm not sure it's feasible to accommodate arbitrary functions passed to `aggfunc`. Among other things, because it's not always clear what normalization means outside of a frequency table. How do you normalize a mean? Or a standard deviation? Or the 90th percentile? 

Unless someone has a better ideal, I'm just going to restrict this to working when `aggfunc` is `None`.

@sinhrks ok, now does frequency count (and will normalize counts) if passed `values` but no `aggfunc`, as you suggested!

@nickeubank About the conflict between a custom aggfunc and normalize, I didn't follow the discussion of last week, but I don't see the problem. Shouldn't `normalize=True` not just ensure the values in the a row/column sum up to 1 (so are percentages of the sum), and I would say it's then up to the user to decide if that makes sense for his/her custom aggfunc?

@jreback revised and rebased.