Add rows for sum/mean/std at end of pgfplotstable

Question

How do you add a one or multiple rows at the bottom of a table in pgfplotstable that is a summation/average/variance or other function of the above data in that particular column?

How can this be done for just some of the columns in a table?

Answer 1

This answer is heavily inspired from Create a contingency table using pgfplotstable (I even stole the example table). For the second part (only treat some columns) it borrows from How to mark/label nth data point from file in pgfplots?.

The basic idea is: It is relatively easy to add columns to a table with entries computed from other columns, but it is not possible to add rows. Thus, we transpose, then add columns, then we transpose again. Since selecting a subset of the columns may not be what everyone needs, the below solution contains a macro \addstatistics that adds sum, mean and standard deviation for all columns as well as a macro \addsomestatistics that accepts as a third argument a list of columns (numbers between 0 and the number of data columns minus one in the tikz foreach list format). The first two arguments in both cases are the table and the name of the label column. See the code for examples how to use it.

\documentclass{report}
\usepackage{booktabs}
\usepackage{pgfplots}
\usepackage{pgfplotstable}
\pgfplotsset{compat=1.11}

\newcommand{\addstatistics}[2]{ %
% #1=table name
% #2=first column name
% Transpose the table, so we can work on rows instead of 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
    }
]{Sum}\intermediatetable
%
\pgfplotstablecreatecol[
    create col/expr={\thisrow{Sum}/(\pgfplotstablecols-2)}
]{Mean}\intermediatetable

% Standard deviation for each column
\pgfplotstablecreatecol[
    create col/assign/.code={%
        \def\colsumdevsquares{0}
        \pgfmathtruncatemacro\maxcolindex{\pgfplotstablecols-3}  % ignore the sum and the mean column!
        \pgfplotsforeachungrouped \col in {1,...,\maxcolindex}{
            \pgfmathsetmacro\colsumdevsquares{\colsumdevsquares+(\thisrowno{\col}-\thisrow{Mean})^2}
        }
        \pgfmathsetmacro\colstd{sqrt(\colsumdevsquares/(\maxcolindex-1))}%
        \pgfkeyslet{/pgfplots/table/create col/next content}\colstd
    }
]{Standard Deviation}\intermediatetable
%
% Transpose back to the original form
\pgfplotstabletranspose[colnames from=#2, input colnames to=#2]{\statstable}{\intermediatetable}
}
%

\newcommand{\addsomestatistics}[3]{ %
% #1=table name
% #2=first column name
% #3=list of columns to decorate
% Transpose the table, so we can work on rows instead of columns
\pgfplotstabletranspose[colnames from={#2},input colnames to={#2}]{\intermediatetable}{#1}
%
% Sums for each column
\pgfplotstablecreatecol[
    create col/assign/.code={%
      \def\entry{}
      \foreach \i in {#3} {
        \ifnum\pgfplotstablerow=\i
          \def\colsum{0}
          \pgfmathtruncatemacro\maxcolindex{\pgfplotstablecols-1}
          \pgfplotsforeachungrouped \col in {1,...,\maxcolindex}{
              \pgfmathsetmacro\colsum{\colsum+\thisrowno{\col}}
          }
          \xdef\entry{\colsum}
        \fi
      }
      \pgfkeyslet{/pgfplots/table/create col/next content}\entry
    }
]{Sum}\intermediatetable
%
\pgfplotstablecreatecol[
    create col/assign/.code={%
      \def\entry{}
      \foreach \i in {#3} {
        \ifnum\pgfplotstablerow=\i
          \pgfmathsetmacro\colmean{\thisrow{Sum}/(\pgfplotstablecols-2)}
          \xdef\entry{\colmean}
        \fi
      }
      \pgfkeyslet{/pgfplots/table/create col/next content}\entry
    }
]{Mean}\intermediatetable

% Standard deviation for each column
\pgfplotstablecreatecol[
    create col/assign/.code={%
      \def\entry{}
      \foreach \i in {#3} {
        \ifnum\pgfplotstablerow=\i
          \def\colsumdevsquares{0}
          \pgfmathtruncatemacro\maxcolindex{\pgfplotstablecols-3}  % ignore the sum and the mean column!
          \pgfplotsforeachungrouped \col in {1,...,\maxcolindex}{
            \pgfmathsetmacro\colsumdevsquares{\colsumdevsquares+(\thisrowno{\col}-\thisrow{Mean})^2}
          }
          \pgfmathsetmacro\colstd{sqrt(\colsumdevsquares/(\maxcolindex-1))}%
          \xdef\entry{\colstd}
        \fi
      }
      \pgfkeyslet{/pgfplots/table/create col/next content}\entry
    }
]{Standard Deviation}\intermediatetable
%
% Transpose back to the original form
\pgfplotstabletranspose[colnames from=#2, input colnames to=#2]{\statstable}{\intermediatetable}
}
%

\begin{document}

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

\addstatistics{\chisquaredata}{Gender}

\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},
]\statstable

\addsomestatistics{\chisquaredata}{Gender}{0,2}

\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},
]\statstable

\end{document}