How to load a table from file, then transpose it and replace its headers if any?

Question

If I have two types of databases; the first one has raw data with row and column headers, while the second one has only raw data.

I would like to get the desired output shown below through:

1- (in the first case) replacing the row and column headers with my own ones, or (in the second case) defining my own headers

2- transposing the data

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

% 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*}

\begin{document}

    I would like the desired output for both databases to be

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

Desired Output

Do you want a transpose or a rotation? You say transpose but show a rotation. It does matter. — William 'Ike' Eisenhauer, Jan 26 '18 at 18:07
@William'Ike'Eisenhauer Thanks for pointing this out. I mean transpose, and I fix it now. — Diaa, Jan 26 '18 at 18:09
Possible duplicate of tabular input by columns (i.e. transpose a table) — bmv, Jan 26 '18 at 18:15
@bmv I am afraid it is not. I need here to change the headers in the data file or defining my own. It is not just a transpose. — Diaa, Jan 26 '18 at 18:17
pgfplotstable can do a transpose. See https://tex.stackexchange.com/questions/191136/using-set-list-with-pgfplotstable?s=6|32.5915 — John Kormylo, Jan 27 '18 at 18:41

score 2 · Accepted Answer · answered Jan 29 '18 at 20:23

The datatool package stores header information separately from the actual data. In this sense it's more like a structured query language (SQL) database or an array of arrays rather than like a spreadsheet application. When importing data from a CSV file using \DTLloaddb or \DTLloadrawdb the first row is parsed for the header information (unless the noheader option is used) and the remaining rows are data.

The header information provides a mapping between the column index (starting from 1) and a label that may be used as a reference. Whenever a command is used that accepts a label as a column identifier it's internally converted to the corresponding column index. The header information also includes a title for the column (used by \DTLdisplaydb and \DTLdisplaylongdb) and a type identifier (unknown, string, integer, decimal or currency).

For example, with database1.csv:

        ,col1   , col2  , col3
row1    , 11    , 12    , 13
row2    , 21    , 22    , 23

loaded with \DTLloaddb{database1}{database1.csv} then column 1 has an empty label, column 2 has the label col1, column 3 has the label col2 and column 4 has the label col3. The row indexes correspond to the row of actual data (not the CSV file line numbers). So the entry for row 1, column 1 has the value row1, and the entry for row 1, column 2 has the value 11 (including spaces, since \DTLloaddb doesn't trim, you need datatooltk for improved CSV parsing).

So instead of using the convenient high-level user commands, such as \DTLdisplaydb or \DTLforeach, it's possible to look-up data using the row and column index.

For example:

\documentclass{article}

\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{database1}{database1.csv}
\DTLloaddb[noheader]{database2}{database2.csv}

\newcount\rowidx
\newcount\colidx

\newcommand{\rowthencolumn}[1]{%
  \rowidx=0\relax
  \loop % row loop
  \advance\rowidx by 1\relax
   {% column loop (needs scoping)
     \colidx=0\relax
     \loop
     \advance\colidx by 1\relax
      \ifnum\colidx>1 ,\space\fi
      \DTLgetvalue{\thisvalue}{#1}{\rowidx}{\colidx}\thisvalue
     \ifnum\colidx<\DTLcolumncount{#1}
     \repeat
   }%
   \par
  \ifnum\rowidx<\DTLrowcount{#1}
  \repeat
}

\begin{document}
\section{database1}
Iterate row then column:

\rowthencolumn{database1}

\section{database2}
Iterate row then column:

\rowthencolumn{database2}

\end{document}

which produces:

Reversing the loop nesting will iterate over column first then row:

\documentclass{article}

\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{database1}{database1.csv}
\DTLloaddb[noheader]{database2}{database2.csv}

\newcount\rowidx
\newcount\colidx

\newcommand{\columnthenrow}[1]{%
  \colidx=0\relax
  \loop % column loop
   \advance\colidx by 1\relax
   {% row loop (needs scoping)
     \rowidx=0\relax
     \loop
     \advance\rowidx by 1\relax
      \ifnum\rowidx>1 ,\space\fi
      \DTLgetvalue{\thisvalue}{#1}{\rowidx}{\colidx}\thisvalue
     \ifnum\rowidx<\DTLrowcount{#1}
     \repeat
   }%
   \par
  \ifnum\colidx<\DTLcolumncount{#1}
  \repeat
}

\begin{document}
\section{database1}
Iterate column then row:

\columnthenrow{database1}

\section{database2}
Iterate column then row:

\columnthenrow{database2}

\end{document}

This produces:

To skip the first column, just start the loop from the next index:

\documentclass{article}

\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{database1}{database1.csv}
\DTLloaddb[noheader]{database2}{database2.csv}

\newcount\rowidx
\newcount\colidx

\newcommand{\columnthenrow}[2][0]{%
  \colidx=#1\relax
  \loop % column loop
  \advance\colidx by 1\relax
   {% row loop (needs scoping)
     \rowidx=0\relax
     \loop
      \advance\rowidx by 1\relax
      \ifnum\rowidx>1 ,\space\fi
      \DTLgetvalue{\thisvalue}{#2}{\rowidx}{\colidx}\thisvalue
     \ifnum\rowidx<\DTLrowcount{#2}
     \repeat
   }%
   \par
  \ifnum\colidx<\DTLcolumncount{#2}
  \repeat
}

\begin{document}
\section{database1}
Iterate column then row:

\columnthenrow[1]{database1}

\section{database2}
Iterate column then row:

\columnthenrow{database2}

\end{document}

The loop increments the index at the start of each iteration, so the starting point needs to be one less than the actual value. The above produces:

The column loop can be replaced by \dtlforeachkey, which supplies not only the column index for the current iteration but also the other header information, which includes the header title:

\documentclass{article}

\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[autokeys,headers={Column 1,Column 2,Column 3,Column 4}]
 {database1}{database1.csv}
\DTLloaddb[noheader,headers={Column 1,Column 2,Column 3}]
 {database2}{database2.csv}

\newcount\rowidx

\newcommand{\columnthenrow}[2][0]{%
  \dtlforeachkey(\thiskey,\thiscol,\thistype,\thisheader)\in#2\do
  {%
    \ifnum\thiscol>#1\relax
      % header title
      \thisheader
      % row loop 
      \rowidx=0\relax
      \loop
        \advance\rowidx by 1\relax
        ,\space
        \DTLgetvalue{\thisvalue}{#2}{\rowidx}{\thiscol}\thisvalue
      \ifnum\rowidx<\DTLrowcount{#2}
      \repeat
      \par
    \fi
  }%
}

\begin{document}
\section{database1}
Iterate column then row:

\columnthenrow[1]{database1}

\section{database2}
Iterate column then row:

\columnthenrow{database2}

\end{document}

This produces:

Your custom row headers need to be added before the first loop:

\newcommand{\columnthenrow}[2][0]{%
  Row 1, Row 2\par
  \dtlforeachkey(\thiskey,\thiscol,\thistype,\thisheader)\in#2\do
  {%
    \ifnum\thiscol>#1\relax
      % header title
      \thisheader
      % row loop 
      \rowidx=0\relax
      \loop
        \advance\rowidx by 1\relax
        ,\space
        \DTLgetvalue{\thisvalue}{#2}{\rowidx}{\thiscol}\thisvalue
      \ifnum\rowidx<\DTLrowcount{#2}
      \repeat
      \par
    \fi
  }%
}

Loops and tabular don't mix well, so to convert this to nicely tabulated content it's best to first build the tabular code and then use it:

\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[autokeys,headers={Column 1,Column 2,Column 3,Column 4}]
 {database1}{database1.csv}
\DTLloaddb[noheader,headers={Column 1,Column 2,Column 3}]
 {database2}{database2.csv}

\newcount\rowidx

\newcommand{\columnthenrow}[2][0]{%
  \def\tabularcontents{\begin{tabular}{lcc}\toprule&Row 1&Row2}%
  \dtlforeachkey(\thiskey,\thiscol,\thistype,\thisheader)\in#2\do
  {%
    \ifnum\thiscol>#1\relax
      % header title
      \eappto\tabularcontents{%
        \noexpand\\\noexpand\midrule\expandonce\thisheader}%
      % row loop 
      \rowidx=0\relax
      \loop
        \advance\rowidx by 1\relax
        \DTLgetvalue{\thisvalue}{#2}{\rowidx}{\thiscol}%
        \eappto\tabularcontents{\noexpand&\expandonce\thisvalue}%
      \ifnum\rowidx<\DTLrowcount{#2}
      \repeat
    \fi
  }%
  \appto\tabularcontents{\\\bottomrule\end{tabular}}%
  \tabularcontents
}

\begin{document}
\section{database1}

\columnthenrow[1]{database1}

\section{database2}

\columnthenrow{database2}

\end{document}

It's truly an informative detailed answer that needs to be read carefully. I highly appreciate the time and effort you put into delivering it. — Diaa, Jan 29 '18 at 20:38
I am sorry, but after revisiting your code, I have been trying for hours to tweak it for two other cases without success. I would like to edit your code for: (Edit 1) copying the data as it is with just adding or renaming row and column headers, and (Edit 2) just rotating the data while adding or renaming row and column headers. — Diaa, Feb 11 '18 at 23:13
@DiaaAbidou I recommend you post a follow-up question with a link back to this, demonstrating your two other cases. (I'm going away for a few days but someone else may be able to answer before I get back.) — Nicola Talbot, Feb 12 '18 at 00:20
Kindly, be informed that I have asked a follow-up question to this one as you recommended. Thanks — Diaa, Feb 15 '18 at 18:31

How to load a table from file, then transpose it and replace its headers if any?

Desired Output

1 Answers1

Linked