datatool: How to load a table from file, rotate or not, then impose predefined headers

Question

This is a follow-up question to my previous one.

In this one, I would like to tackle two cases:

First, load the table as it is while just imposing my own column and row headers.

Second, load the table and rotate it, then impose my own column and row headers.

For both cases mentioned above, two different types of databases are to be handled as shown below

\documentclass{article}
\usepackage{datatool,filecontents,booktabs}

% 1st dataset
\begin{filecontents*}{data1.csv}
            ,   col1  , col2  , col3
    row1    ,   11    , 12    , 13
    row2    ,   21    , 22    , 23
\end{filecontents*}

% 2nd dataset
\begin{filecontents*}{data2.csv}
    11  , 12 , 13
    21  , 22 , 23
\end{filecontents*}

\begin{document}

\section{First case: without rotation}

The desired output for both datasets should be

\begin{table}[h]
    \centering
    \caption{without rotation}
    \begin{tabular}{@{}lccc@{}}
        \toprule
        & First Column  & Second Column & Third Column\\
        \midrule
        First Row   &   11  &   12  & 13 \\ 
        \midrule 
        Second Row  &   21  &   22  & 23 \\
        \bottomrule
    \end{tabular}
\end{table}

\section{Second case: with rotation}

The desired output for both datasets should be

\begin{table}[h]
    \centering
    \caption{with rotation}
    \begin{tabular}{@{}lcc@{}}
        \toprule
                    & First Column  & Second Column\\
        \midrule
        First Row   &   21  &   11 \\ 
        \midrule 
        Second Row  &   22  &   12 \\
        \midrule 
        Third Row   &   23  &   13 \\
        \bottomrule
    \end{tabular}
\end{table}
\end{document}

Answer 1

It's best to ensure that both databases are assigned the same keys (through the keys option in \DTLloaddb). This means that the keys can be used to reference the same column in both databases, even though they have different column indexes:

\DTLloaddb[keys={rowheader,col1,col2,col3}]{database1}{database1.csv}
\DTLloaddb[noheader,keys={col1,col2,col3}]{database2}{database2.csv}

The column headers can be set for both databases using these keys:

\newcommand{\setdatabaseheaders}[1]{%
  \DTLsetheader{#1}{col1}{First Column}%
  \DTLsetheader{#1}{col2}{Second Column}%
  \DTLsetheader{#1}{col3}{Third Column}%
}

\setdatabaseheaders{database1}
\setdatabaseheaders{database2}

However, if the column headers are being shared across all the tables, then it's simpler to just store them in an easy to access manner with the help of etoolbox's \csdef command:

\csdef{columnheader1}{First Column}
\csdef{columnheader2}{Second Column}
\csdef{columnheader3}{Third Column}

There's no concept of row headers with datatool but it's easy enough to provide some based on the row index in a similar way:

\csdef{rowheader1}{First Row}
\csdef{rowheader2}{Second Row}

The column identified by rowheader needs to be ignored when displaying the data. The existence of this column can be checked with \DTLifhaskey. The custom commands for displaying the data can insert the row headers by referencing the command given by \csname rowheaderidx\endcsname (or \csuse{rowheaderidx}) at the start of each row. Similarly for the column headers.

Complete MWE:

\documentclass{article}

\usepackage{booktabs}
\usepackage{datatool}

% Case 1
\begin{filecontents*}{database1.csv}
            ,col1   , col2  , col3
    row1    , 11    , 12    , 13
    row2    , 21    , 22    , 23
\end{filecontents*}

% Case 2
\begin{filecontents*}{database2.csv}
    11  , 12 , 13
    21  , 22 , 23
\end{filecontents*}

\DTLloaddb[keys={rowheader,col1,col2,col3}]{database1}{database1.csv}
\DTLloaddb[noheader,keys={col1,col2,col3}]{database2}{database2.csv}

\csdef{rowheader1}{First Row}
\csdef{rowheader2}{Second Row}
\csdef{rowheader3}{Third Row}

\csdef{columnheader1}{First Column}
\csdef{columnheader2}{Second Column}
\csdef{columnheader3}{Third Column}


\newcount\columnidx
\newcount\rowidx
\newcount\columncount

\newcommand{\displaydatabase}[1]{%
  \DTLifhaskey{#1}{rowheader}%
  {%
    \columncount=\numexpr\DTLcolumncount{#1}-1\relax
    \def\columnoffset{1}%
  }%
  {%
    \columncount=\DTLcolumncount{#1}%
    \def\columnoffset{0}%
  }%
  % construct \begin{tabular} arguments
  \def\columnargs{}%
  \columnidx=0
  \loop
    \advance\columnidx by 1\relax
    \appto\columnargs{c}%
  \ifnum\columnidx<\columncount
  \repeat
  \edef\tabularcontents{\noexpand\begin{tabular}{l\columnargs}}%
  \appto\tabularcontents{\toprule}%
  % add table header
  \dtlforeachkey(\thiskey,\thiscol,\thistype,\thisheader)\in#1\do
  {%
    \ifdefstring{\thiskey}{rowheader}%
    {}%
    {%
      \eappto\tabularcontents{\noexpand& 
        \expandonce{\csname columnheader\number\numexpr\thiscol-\columnoffset\endcsname}}%
    }%
  }%
  % iterate over all rows
  \rowidx=0
  \loop
    \advance\rowidx by 1\relax
  % header
    \eappto\tabularcontents{\noexpand\\\noexpand\midrule 
      \expandonce{\csname rowheader\the\rowidx\endcsname}}%
  % columns
    \dtlforeachkey(\thiskey,\thiscol,\thistype,\thisheader)\in#1\do
    {%
      \ifdefstring{\thiskey}{rowheader}%
      {}%
      {%
        \DTLgetvalue{\thisvalue}{#1}{\rowidx}{\thiscol}%
        \eappto\tabularcontents{\noexpand& \expandonce\thisvalue}%
      }%
    }%
  \ifnum\rowidx<\DTLrowcount{#1}
  \repeat
  \appto\tabularcontents{\\\bottomrule\end{tabular}}%
  \tabularcontents
}

\newcommand{\displayrotateddatabase}[1]{%
  \DTLifhaskey{#1}{rowheader}%
  {%
    \def\columnoffset{1}%
  }%
  {%
    \def\columnoffset{0}%
  }%
  \columncount=\DTLrowcount{#1}%
  % construct \begin{tabular} arguments
  \def\columnargs{}%
  \columnidx=0
  \loop
    \advance\columnidx by 1\relax
    \appto\columnargs{c}%
  \ifnum\columnidx<\columncount
  \repeat
  \edef\tabularcontents{\noexpand\begin{tabular}{l\columnargs}}%
  \appto\tabularcontents{\toprule}%
  % add table header
  \columnidx=0
  \loop
    \advance\columnidx by 1\relax
    \eappto\tabularcontents{\noexpand& 
       \expandonce{\csname columnheader\the\columnidx\endcsname}}%
  \ifnum\columnidx<\columncount
  \repeat
  % iterate through all columns omitting rowheader
  \dtlforeachkey(\thiskey,\thiscol,\thistype,\thisheader)\in#1\do
  {%
    \ifdefstring{\thiskey}{rowheader}%
    {}%
    {%
      % header title
      \eappto\tabularcontents{%
        \noexpand\\\noexpand\midrule
           \expandonce{\csname rowheader\number\numexpr\thiscol-\columnoffset\endcsname}}%
      % row loop in reverse order
      \rowidx=\DTLrowcount{#1}\relax
      \loop
        \DTLgetvalue{\thisvalue}{#1}{\rowidx}{\thiscol}%
        \eappto\tabularcontents{\noexpand&\expandonce\thisvalue}%
        \advance\rowidx by -1\relax
      \ifnum\rowidx>0
      \repeat
    }%
  }%
  \appto\tabularcontents{\\\bottomrule\end{tabular}}%
  \tabularcontents
}

\newcommand{\displaytransposedatabase}[1]{%
  \DTLifhaskey{#1}{rowheader}%
  {%
    \def\columnoffset{1}%
  }%
  {%
    \def\columnoffset{0}%
  }%
  \columncount=\DTLrowcount{#1}%
  % construct \begin{tabular} arguments
  \def\columnargs{}%
  \columnidx=0
  \loop
    \advance\columnidx by 1\relax
    \appto\columnargs{c}%
  \ifnum\columnidx<\columncount
  \repeat
  \edef\tabularcontents{\noexpand\begin{tabular}{l\columnargs}}%
  \appto\tabularcontents{\toprule}%
  % add table header
  \columnidx=0
  \loop
    \advance\columnidx by 1\relax
    \eappto\tabularcontents{\noexpand& 
       \expandonce{\csname columnheader\the\columnidx\endcsname}}%
  \ifnum\columnidx<\columncount
  \repeat
  % iterate through all columns omitting rowheader
  \dtlforeachkey(\thiskey,\thiscol,\thistype,\thisheader)\in#1\do
  {%
    \ifdefstring{\thiskey}{rowheader}%
    {}%
    {%
      % header title
      \eappto\tabularcontents{%
        \noexpand\\\noexpand\midrule
           \expandonce{\csname rowheader\number\numexpr\thiscol-\columnoffset\endcsname}}%
      % row loop 
      \rowidx=0\relax
      \loop
        \advance\rowidx by 1\relax
        \DTLgetvalue{\thisvalue}{#1}{\rowidx}{\thiscol}%
        \eappto\tabularcontents{\noexpand&\expandonce\thisvalue}%
      \ifnum\rowidx<\DTLrowcount{#1}
      \repeat
    }%
  }%
  \appto\tabularcontents{\\\bottomrule\end{tabular}}%
  \tabularcontents
}

\begin{document}
\section{No rotation}
\subsection{database1}

\displaydatabase{database1}

\subsection{database2}

\displaydatabase{database2}

\subsection{Desired Output}

\begin{table}[h]
    \centering
    \caption{without rotation}
    \begin{tabular}{@{}lccc@{}}
        \toprule
        & First Column  & Second Column & Third Column\\
        \midrule
        First Row   &   11  &   12  & 13 \\ 
        \midrule 
        Second Row  &   21  &   22  & 23 \\
        \bottomrule
    \end{tabular}
\end{table}

\newpage
\section{Rotation}
\subsection{database1}

\displayrotateddatabase{database1}

\subsection{database2}
\displayrotateddatabase{database2}

\subsection{Desired Output}
\begin{table}[h]
    \centering
    \caption{with rotation}
    \begin{tabular}{@{}lcc@{}}
        \toprule
                    & First Column  & Second Column\\
        \midrule
        First Row   &   21  &   11 \\ 
        \midrule 
        Second Row  &   22  &   12 \\
        \midrule 
        Third Row   &   23  &   13 \\
        \bottomrule
    \end{tabular}
\end{table}

\newpage
\section{Transpose}
\subsection{database1}

\displaytransposedatabase{database1}

\subsection{database2}

\displaytransposedatabase{database2}

\end{document}

Non-rotated tables:

Rotated tables:

Transposed tables:

Notes:

• \DTLloaddb doesn't trim trailing spaces, so if I hadn't used keys={rowheader,col1,col2,col3}, the keys for the second, third and fourth columns would include a trailing space. The following MWE doesn't work:

  \documentclass{article}
  
  \usepackage{datatool}
  
  \begin{filecontents*}{database1.csv}
              ,col1   , col2  , col3
      row1    , 11    , 12    , 13
      row2    , 21    , 22    , 23
  \end{filecontents*}
  
  \DTLloaddb{database1}{database1.csv}
  
  \DTLsetheader{database1}{col1}{First Column}
  
  \begin{document}
  
  \end{document}
  

  This produces the error

  ! Package datatool Error: Database `database1' doesn't contain key `col1'.
  

  The column label includes a trailing space, so the assignment needs to be:

  \DTLsetheader{database1}{col1 }{First Column}
  

• Leading spaces are ignored, so

  \DTLsetheader{database1}{ col2 }{Second Column}
  

  produces an error, even though there's a leading space in the CSV file. It needs to be:

  \DTLsetheader{database1}{col2 }{Second Column}
  

Similarly for each entry. For example:

\documentclass{article}

\usepackage{datatool}

\begin{filecontents*}{database1.csv}
            ,col1   , col2  , col3
    row1    , 11    , 12    , 13
    row2    , 21    , 22    , 23
\end{filecontents*}

\DTLloaddb{database1}{database1.csv}

\begin{document}

\DTLgetvalue{\thisvalue}{database1}{1}{2}`\thisvalue'.

\end{document}

This produces:

The leading space is ignored but the trailing space is retained.

In this example, it doesn't make much difference as the keys are assigned using keys which doesn't contain any spaces. The entries are in a tabular environment so the spurious space isn't noticeable, but it's something to be careful about.