Splitting a row into several rows in pgfplotstable

Question

I have data set as follows:

id vala    valb
1  24      75
2  56      87

In this data set, there are two value types: vala and valb. The number of types may change, it can be three, four, etc.

I need to convert this first data set to:

id val type
1  24  a 
1  75  b
2  56  a
2  87  b

and then typeset it. Or without conversion, typeset the first data set in the format of the second data set.

type column is to differentiate ids, however it is not very necessary. If possible, it should be generated from the last character of the column names.

Here is MWE:

\documentclass[parskip]{scrartcl}
\usepackage[margin=15mm]{geometry}
\usepackage{pgfplotstable}
\pgfplotsset{width=10cm,compat=1.6}
\usepackage{filecontents}


\begin{filecontents}{testdataExisting.dat}
id vala    valb
1  24      75
2  56      87
\end{filecontents}

\begin{filecontents}{testdataGenerated.dat}
id val type
1  24  a 
1  75  b
2  56  a
2  87  b
\end{filecontents}

 \begin{document}
Table 1

 \pgfplotstabletypeset[col sep=space,columns={id,vala,valb},]{testdataExisting.dat}

Table 2

 \pgfplotstabletypeset[col sep=space,columns={id,val,type},
    columns/type/.style={string type,column type=r},
 ]{testdataGenerated.dat}
 \end{document}

Output:

Answer 1

Define a new table with the element values correctly collected from the original table as follows:

\documentclass[parskip]{scrartcl}
\usepackage[margin=15mm]{geometry}
\usepackage{pgfplotstable}
\pgfplotsset{width=10cm,compat=1.6}
\usepackage{filecontents}


\begin{filecontents}{testdataExisting.dat}
id vala    valb
1  24      75
2  56      87
\end{filecontents}

\pgfplotstableread{testdataExisting.dat}\origtable

\begin{document}
Table 1

\pgfplotstabletypeset[col sep=space,columns={id,vala,valb}]\origtable

Table 2

\pgfplotstablegetrowsof{\origtable}
\pgfmathtruncatemacro\rr{2*\pgfmathresult}
\pgfplotstablenew[%
  create on use/newid/.style={%
  create col/assign/.code={%
  \pgfmathtruncatemacro\orr{\pgfplotstablerow/2}
  \pgfplotstablegetelem{\orr}{id}\of\origtable
  \pgfkeyslet{/pgfplots/table/create col/next content}\pgfplotsretval}},%
  create on use/newval/.style={%
  create col/assign/.code={%
  \pgfmathtruncatemacro\orr{\pgfplotstablerow/2}
  \pgfmathtruncatemacro\occ{\pgfplotstablerow-2*\orr}
  \edef\ocol{\ifnum\occ=0 vala\else valb\fi}
  \pgfplotstablegetelem{\orr}{\ocol}\of\origtable
  \pgfkeyslet{/pgfplots/table/create col/next content}\pgfplotsretval}},%
  create on use/type/.style={%
  create col/assign/.code={%
  \pgfmathtruncatemacro\orr{\pgfplotstablerow/2}
  \pgfmathtruncatemacro\occ{\pgfplotstablerow-2*\orr}
  \edef\otyp{\ifnum\occ=0 a\else b\fi}
  \pgfkeyslet{/pgfplots/table/create col/next content}\otyp}},%
  columns={newid,newval,type}]{\rr}\newtable

  \pgfplotstabletypeset[columns/type/.style={string type,column type=r}]\newtable
\end{document}

For the new table, there is a create on use for each column. \pgfmathtruncatemacro is used as a handy way to get an integer value out and assign it to a macro.