Someone recently asked me about creating cross-tabulations and contingency tables using pandas. I wrote a bit about this in October after implementing the pivot_table function for DataFrame. So I thought I would give a few more examples and show R code vs. the equivalent pandas code which will be helpful for those already used to the R way of doing things.
When calling help(xtabs) (or help(table)) in R, you get a number of examples for using table, xtabs, and ftable:
> head(esoph) agegp alcgp tobgp ncases ncontrols 1 25-34 0-39g/day 0-9g/day 0 40 2 25-34 0-39g/day 10-19 0 10 3 25-34 0-39g/day 20-29 0 6 4 25-34 0-39g/day 30+ 0 5 5 25-34 40-79 0-9g/day 0 27 6 25-34 40-79 10-19 0 7 > ftable(xtabs(cbind(ncases, ncontrols) ~ agegp + tobgp, data = esoph)) ncases ncontrols agegp tobgp 25-34 0-9g/day 0 70 10-19 1 19 20-29 0 11 30+ 0 16 35-44 0-9g/day 2 109 10-19 4 46 20-29 3 27 30+ 0 17 45-54 0-9g/day 14 104 10-19 13 57 20-29 8 33 30+ 11 19 55-64 0-9g/day 25 117 10-19 23 65 20-29 12 38 30+ 16 22 65-74 0-9g/day 31 99 10-19 12 38 20-29 10 20 30+ 2 4 75+ 0-9g/day 6 26 10-19 5 11 20-29 0 3 30+ 2 4 > ftable(xtabs(cbind(ncases, ncontrols) ~ agegp, data = esoph)) ncases ncontrols agegp 25-34 1 116 35-44 9 199 45-54 46 213 55-64 76 242 65-74 55 161 75+ 13 44
Here are the same examples using pandas:
In [19]: table = esoph.pivot_table(['ncases', 'ncontrols'], rows=['agegp', 'tobgp'], aggfunc=np.sum) In [20]: table Out[20]: ncases ncontrols agegp tobgp 25-34 0-9g/day 0. 70. 10-19 1. 19. 20-29 0. 11. 30+ 0. 16. 35-44 0-9g/day 2. 109 10-19 4. 46. 20-29 3. 27. 30+ 0. 17. 45-54 0-9g/day 14 104 10-19 13 57. 20-29 8. 33. 30+ 11 19. 55-64 0-9g/day 25 117 10-19 23 65. 20-29 12 38. 30+ 16 22. 65-74 0-9g/day 31 99. 10-19 12 38. 20-29 10 20. 30+ 2. 4.0 75+ 0-9g/day 6. 26. 10-19 5. 11. 20-29 0. 3.0 30+ 2. 4.0 In [22]: table2 = esoph.pivot_table(['ncases', 'ncontrols'], rows='agegp', aggfunc=np.sum) In [23]: table2 Out[23]: ncases ncontrols agegp 25-34 1. 116 35-44 9. 199 45-54 46 213 55-64 76 242 65-74 55 161 75+ 13 44.
One of the really great things about pandas is that the object produced is still a DataFrame (the R object is of a special class)! So we could do anything with it that we do with normal DataFrame objects:
In [26]: table['ncases'].unstack('agegp') Out[26]: agegp 25-34 35-44 45-54 55-64 65-74 75+ tobgp 0-9g/day 0 2 14 25 31 6 10-19 1 4 13 23 12 5 20-29 0 3 8. 12 10 0 30+ 0 0 11 16 2. 2
Here is another fun example:
In [31]: import pandas.rpy.common as com In [32]: wp = com.load_data('warpbreaks') In [33]: wp['replicate'] = np.tile(range(1, 10), 6) In [34]: wp.pivot_table('breaks', rows=['wool', 'tension'], cols='replicate', aggfunc=np.sum) Out[34]: replicate 1 2 3 4 5 6 7 8 9 wool tension A H 36 21 24 18 10 43 28 15 26 L 26 30 54 25 70 52 51 26 67 M 18 21 29 17 12 18 35 30 36 B H 20 21 24 17 13 15 15 16 28 L 27 14 29 19 29 31 41 20 44 M 42 26 19 16 39 28 21 39 29
Here is the equivalent R code:
> warpbreaks$replicate <- rep(1:9, len = 54) > ftable(xtabs(breaks ~ wool + tension + replicate, data = warpbreaks)) replicate 1 2 3 4 5 6 7 8 9 wool tension A L 26 30 54 25 70 52 51 26 67 M 18 21 29 17 12 18 35 30 36 H 36 21 24 18 10 43 28 15 26 B L 27 14 29 19 29 31 41 20 44 M 42 26 19 16 39 28 21 39 29 H 20 21 24 17 13 15 15 16 28
As soon as we add a formula framework to statsmodels I could see incorporating that into this kind of function in Python.