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

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

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

\newcount\rowidx
\newcount\colidx

\newcommand{\rowthencolumn}[1]{%
\rowidx=0\relax
\loop % row loop
{% column loop (needs scoping)
\colidx=0\relax
\loop
\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*}

\newcount\rowidx
\newcount\colidx

\newcommand{\columnthenrow}[1]{%
\colidx=0\relax
\loop % column loop
{% row loop (needs scoping)
\rowidx=0\relax
\loop
\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*}

\newcount\rowidx
\newcount\colidx

\newcommand{\columnthenrow}[2][0]{%
\colidx=#1\relax
\loop % column loop
{% row loop (needs scoping)
\rowidx=0\relax
\loop
\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*}

{database1}{database1.csv}
{database2}{database2.csv}

\newcount\rowidx

\newcommand{\columnthenrow}[2][0]{%
{%
\ifnum\thiscol>#1\relax
% row loop
\rowidx=0\relax
\loop
,\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
{%
\ifnum\thiscol>#1\relax
% row loop
\rowidx=0\relax
\loop
,\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*}

{database1}{database1.csv}
{database2}{database2.csv}

\newcount\rowidx

\newcommand{\columnthenrow}[2][0]{%
\def\tabularcontents{\begin{tabular}{lcc}\toprule&Row 1&Row2}%
{%
\ifnum\thiscol>#1\relax
\eappto\tabularcontents{%
% row loop
\rowidx=0\relax
\loop
\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 0: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