2

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}

enter image description here

4

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:

image of non-rotated tables

Rotated tables:

image of rotated tables

Transposed tables:

image of 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:

‘11 ’.

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.

  • For the second time, your help is highly appreciated. However, I am afraid I couldn't find how to rotate (not transpose) the data in your answer as I showed in my second desired table. – Diaa Feb 19 '18 at 19:45
  • @DiaaAbidou Sorry I should've checked the output more closely with yours. By rotation, do you mean that the second row becomes the first column and the first row becomes the second column? Do you always have two rows of data? – Nicola Talbot Feb 19 '18 at 20:30
  • Thanks for consideration. By rotation, I mean that the first row becomes the last column, and the last row becomes the first column, and so on for the rest of rows. Usually, I have several m rows and n columns. – Diaa Feb 19 '18 at 21:02
  • @DiaaAbidou Okay. Hopefully I've got it right now :-) – Nicola Talbot Feb 19 '18 at 21:30
  • 1
    @DiaaAbidou \begin{tabular}{@{}l\columnargs @{}} should work. (Make sure there's a space after \columnargs if @ is a letter.) – Nicola Talbot Feb 21 '18 at 11:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.