Create a contingency table using pgfplotstable

Question

I'm trying to create a general pgfplotstable style for displaying contingency tables (the below example is taken from Wikipedia) of any sort but I am having trouble computing the totals for each row and column and adding them to the table on the fly.

I am aware that totaling up the row will involve creating a column and using \pgfmathaccuma, while the totals row should be done via \pgfplotstablevertcat but the particulars seem to be beyond me even after repeated reading of the documentation.

The solution should work for different tables with different number of columns/rows and different labels (though the totaled column and row can both be called Totals, they're just named differently here for reasons of clarity).

NB: I've included a hand-formatted table of the desired output below and my efforts so far (which use the actual table format I want to work with).

\documentclass[11pt,a4paper,oneside]{report}
\usepackage{booktabs}
\usepackage{pgfplots}
\usepackage{pgfplotstable}

\begin{document}

\section*{Desired output}
\pgfplotstabletypeset[
  every head row/.style={%
    before row={\toprule 
        & \multicolumn{2}{c}{Handedness}\\            \cmidrule{2-3}},
    after row=\midrule},
  every last row/.style={after row=\bottomrule},
  columns/Gender/.style={string type},
]{
    Gender       Right-handed Left-handed {Total by Gender}
    Male                   43           9               52
    Female                 44           4               48
    {Total by Handedness}  87          13              100
}

\section*{Current efforts}

\pgfplotstableread{
    Gender Right-handed Left-handed
    Male             43           9
    Female           44           4
}\chisquaredata

\pgfplotstabletypeset[
% Does not total up correctly
  create on use/{Total by Gender}/.style={
    create col/expr={\pgfmathaccuma + \thisrow{\pgfplotstablecolname}}},
  every head row/.style={%
    before row={\toprule 
        & \multicolumn{2}{c}{Handedness}\\
        \cmidrule{2-3}},
    after row=\midrule},
  every last row/.style={after row=\bottomrule},
  columns/Gender/.style={string type},
  columns={Gender, Right-handed, Left-handed, {Total by Gender}},
]\chisquaredata

\end{document}

Answer 1

With pgfplotstable, it's much easier to add columns to tables than to add rows, so what I would suggest is to calculate the row sums first, then transpose the table, and then calculate the column sums (which are now rows, so we can just repeat the first action).

Here's a macro \createcontingencytable{<table name>}{<first column name>}{<name for row sums>}{<name for column sums>} that takes a table and the necessary names and spits out a \contingencytable macro that contains the table with the row and column sums.

For your example, you would say

\createcontingencytable{\chisquaredata}{Gender}{Total by Gender}{Total by Handedness}

\pgfplotstabletypeset[
  every head row/.style={%
    before row={\toprule 
        & \multicolumn{2}{c}{Handedness}\\            \cmidrule{2-3}},
    after row=\midrule},
  every last row/.style={after row=\bottomrule},
  columns/Gender/.style={string type},
  columns={Gender, Right-handed, Left-handed, {Total by Gender}},
]\contingencytable

to get

---

It also works with tables with more than two columns and rows:

\pgfplotstableread{
    Gender Right-handed Left-handed Ambidextrous
    Male             43           9           10
    Female           44           4            5
    Other            20           3            3
}\chisquaredata

\createcontingencytable{\chisquaredata}{Gender}{Total by Gender}{Total by Handedness}

---

Here's the complete code

\documentclass[11pt,a4paper,oneside]{report}
\usepackage{booktabs}
\usepackage{pgfplots}
\usepackage{pgfplotstable}

\newcommand{\createcontingencytable}[4]{ %
% #1=table name
% #2=first column name
% #3=new row sum name
% #4=new column sum name
\pgfplotstablecreatecol[
    create col/assign/.code={% In each row ... 
        \def\rowsum{0}
        \pgfmathtruncatemacro\maxcolindex{\pgfplotstablecols-1}
        % ... loop over all columns, summing up the elements
        \pgfplotsforeachungrouped \col in {1,...,\maxcolindex}{
            \pgfmathsetmacro\rowsum{\rowsum+\thisrowno{\col}}
        }
        \pgfkeyslet{/pgfplots/table/create col/next content}\rowsum
    }
]{#3}{#1}%
%
% Transpose the table, so we can repeat the summation step for the columns
\pgfplotstabletranspose[colnames from={#2},input colnames to={#2}]{\intermediatetable}{#1}
%
% Sums for each column
\pgfplotstablecreatecol[
    create col/assign/.code={%
        \def\colsum{0}
        \pgfmathtruncatemacro\maxcolindex{\pgfplotstablecols-1}
        \pgfplotsforeachungrouped \col in {1,...,\maxcolindex}{
            \pgfmathsetmacro\colsum{\colsum+\thisrowno{\col}}
        }
        \pgfkeyslet{/pgfplots/table/create col/next content}\colsum
    }
]{#4}\intermediatetable
%
% Transpose back to the original form
\pgfplotstabletranspose[colnames from=#2, input colnames to=#2]{\contingencytable}{\intermediatetable}
}
%

\begin{document}

\pgfplotstableread{
    Gender Right-handed Left-handed Ambidextrous
    Male             43           9           10
    Female           44           4            5
    Other            20           3            3
}\chisquaredata

\createcontingencytable{\chisquaredata}{Gender}{Total by Gender}{Total by Handedness}

\pgfplotstabletypeset[
  every head row/.style={%
    before row={\toprule 
        & \multicolumn{3}{c}{Handedness}\\            \cmidrule{2-4}},
    after row=\midrule},
  every last row/.style={after row=\bottomrule},
  columns/Gender/.style={string type},
  columns={Gender, Right-handed, Left-handed, Ambidextrous, {Total by Gender}},
]\contingencytable


\end{document}