6

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

enter image description here

2

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:

1 database1 Iterate row then column: row1 , 11 , 12 , 13 row2 , 21 , 22 , 23 2  database2 Iterate row then column: 11 , 12 , 13 21 , 22 , 23

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:

1 database1 Iterate column then row: row1 , row2 11 , 21 12 , 22 13, 23 2  database2 Iterate column then row: 11 , 21 12 , 22 13, 23

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:

1 database1 Iterate column then row: 11 , 21 12 , 22 13, 23 2  database2 Iterate column then row: 11 , 21 12 , 22 13, 23

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:

1 database1 Iterate column then row: Column 2, 11 , 21 Column 3, 12 , 22 Column 4, 13, 23 2  database2 Iterate column then row: Column 1, 11 , 21 Column 2, 12 , 22 Column 3, 13, 23

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}

image of tabulated contents

  • 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
  • 1
    @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

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.