Write a column selectively to the appropriate row using pgfplotstable?

Question

Description: I have a .csv file named namespgf.csv

Reg.No.,Name
5501,Kathirvelu A
5502,Gugan K
5503,Kalaitchelvi S
5504,Suresh S
5505,Mahesh K

and another .csv file named markspgf.csv

number,marks
5501,67
5502,25
5503,62
5505,95

Pl. note that the register number 5504 is not there in markspgf.csv.

Now I try to copy the marks column to the namespgf.csv file and typeset a table like this (This is what I want):

Reg.No.     Name                 marks
5501        Kathirvelu A         67
5502        Gugan K              25
5503        Kalaitchelvi S       62
5505        Mahesh K             95

Here, the candidate with register number 5504 has no marks in `markspgf.csv. I want to omit the entry corresponding to that number from my resulting table as above. But this is what I get in my attempt:

Here is the code (MWE) that produces the table:

\documentclass{article}
\usepackage{pgfplotstable}
\usepackage{filecontents}
%
\begin{filecontents}{namespgf.csv}
Reg.No.,Name
5501,Kathirvelu A
5502,Gugan K
5503,Kalaitchelvi S
5504,Suresh S
5505,Mahesh K
\end{filecontents}
%
\begin{filecontents}{markspgf.csv}
number,marks
5501,67
5502,25
5503,62
5505,95
\end{filecontents}
\begin{document}
%=============================================================================
\pgfplotstableread[col sep=comma]{namespgf.csv}\namespgf
\pgfplotstableread[col sep=comma,verb string type]{markspgf.csv}\markspgf
%
\pgfkeys{/pgfplots/table/verb string type}
% ----------------------------------------------------------------------%
\pgfplotstablecreatecol[copy column from table={\markspgf}{[index] 1},skip first n=1] {marks} {\namespgf}
%
{\centering
 \pgfplotstabletypeset[columns={Reg.No.,Name,marks}
 ]{\namespgf}
}
%
\end{document}

The question:

How to omit the entry corresponding to Reg. No. 5504 and put the correct marks in front of Reg. No. 5505? (How to get the table as I explained above)?

Please note that I want a solution using pgfplotstable. The main reason behind this is the size of the data I am dealing with. With this amount of data, datatool takes light years to compile and some times, pdflatex runs out of memory. But pgfplotstable is fast and and memory efficient.

Answer 1

Edit See the edit history for the incomplete answer.

---

What I did is to run over the Reg.No. entries and try to see if there is a match in the number column of \markspgf table. If there is, then we use the marks value of that row in creating the new column, otherwise we put a N/A. (if not needed this can be skipped). Then, we record the counter value since \pgfplotstablerow variable is not available now so we keep track of the rows that failed to be matched with and increment the counter.

When the numbers are exhausted, we use this new column in the new table and we again check at each row whether this row is to be used or not. If the row number is matched with the first number on the list, we trigger the if condition with \pgfplotsuserowfalse otherwise nothing happens and the row is typeset.

\documentclass{article}
\usepackage{pgfplotstable,xstring}
\usepackage{filecontents,etoolbox}
%
\begin{filecontents}{namespgf.csv}
Reg.No.,Name,Place
5501,Kathirvelu A,re
5502,Gugan K,rt
5503,Kalaitchelvi S,uy
5504,Suresh S,itr
5505,Mahesh K,utyehd
5506,Balini N,utyehd
5507,Kumar H,utyehd
5508,Khalate A,utyehd
5509,me,hgte
5510,you,there
5511,them,here
5512,who,where 
\end{filecontents}
%
\begin{filecontents}{markspgf.csv}
number,marks
5501,67
5502,25
5503,62
5505,95
5507,100
5512,45
\end{filecontents}
\def\marksuccess{}
\def\mypopulatedlist{}
\newcounter{myrowcount}
\setcounter{myrowcount}{0}


\begin{document}
%=============================================================================
\pgfplotstableread[col sep=comma]{namespgf.csv}\namespgf
\pgfplotstableread[col sep=comma,verb string type]{markspgf.csv}\markspgf
%
\pgfkeys{/pgfplots/table/verb string type}
% ----------------------------------------------------------------------%
\pgfplotstablecreatecol[
create col/assign/.code={%
\let\marksuccess\relax
    \pgfplotstableforeachcolumnelement{number}\of\markspgf\as\cellb{%
        \IfStrEq{\thisrow{Reg.No.}}{\cellb}{%True
            \pgfplotstablegetelem{\pgfplotstablerow}{marks}\of\markspgf
            \edef\myretval{\pgfplotsretval}
            \def\marksuccess{1}
             %\breakforeach %This would have saved some time if it was available
        }
        {}%False
    }
    \if\marksuccess\relax
    \def\myretval{N/A}%
    \xappto\mypopulatedlist{\arabic{myrowcount},}
    \else
    \fi
    \stepcounter{myrowcount}
    \pgfkeyslet{/pgfplots/table/create col/next content}\myretval
}]{Marks}\namespgf


\pgfplotstabletypeset[
column type=l,
columns={Reg.No.,Name,Marks},
row predicate/.code={%
\StrBefore{\mypopulatedlist}{,}[\mynextrow] %Look at the first number before the comma on the list
\IfStrEq{#1}{\mynextrow}{%If the current row is equal to that number
\StrLen{\mynextrow}[\numberlength]%Get the length of the number
\StrGobbleLeft{\mypopulatedlist}{\number\numexpr\numberlength+1\relax}[\mytemplist]%Delete this number and the next comma from the list
\edef\mypopulatedlist{\mytemplist}%Overwrite the old list with the new
\pgfplotstableuserowfalse% Tell pgfplots to omit this row
}{}
}
]\namespgf

\end{document}

Answer 2

Here is a solution that uses the datatool package:

Notes:

• In the MWE below, I used the filecontents* so that the filecontents header is not added to the data file.

Code:

\documentclass{article}
\usepackage{datatool}
\usepackage{xstring}
\usepackage{filecontents}

\begin{filecontents*}{namespgf.csv}
Reg.No.,Name
5501,Kathirvelu A
5502,Gugan K
5503,Kalaitchelvi S
5504,Suresh S
5505,Mahesh K
\end{filecontents*}
%
\begin{filecontents*}{markspgf.csv}
number,marks
5501,67
5502,25
5503,62
5505,95
\end{filecontents*}

%------- Useful for debuging ---------------
\newcommand{\PrintNamesDTLTable}[1]{% #1 = names database
    \begin{tabular}{c c}%
        Reg No & Name \\\hline%
    \DTLforeach{#1}{%
        \RegNo=Reg.No.,%
        \Name=Name%
        }{%
        \RegNo & \Name\\%
    }%
    \end{tabular}%
}%

\newcommand{\PrintMarksDTLTable}[1]{% #1 = marks database
    \begin{tabular}{c c}%
        Number & Marks \\\hline%
    \DTLforeach{#1}{%
        \Number=number,%
        \Marks=marks%
        }{%
        \Number & \Marks\\%
    }%
    \end{tabular}%
}%
%-------------------------------------------

\newcommand*{\PrintIfRegNumberIsInMarks}[3]{%
    % #1 = reg number
    % #2 = name
    % #3 = marks db
    %
    \DTLforeach{#3}{\Number=number, \Marks=marks}{%
        \IfEq{#1}{\Number}{% Found member
            #1 & #2 & \Marks \\%
            \dtlbreak% Done, break out of loop
        }{% Haven't found it yet, so keep looking....
        }%
    }%
}%

\newcommand{\PrintNamesWithMarksDTLTable}[2]{%
    % #1 = names database
    % #2 = marks database
    \begin{tabular}{c c c}%
        Reg No & Name & Marks\\\hline%
        \DTLforeach{#1}{%
            \RegNo=Reg.No.,%
            \Name=Name%
            }{%
                \PrintIfRegNumberIsInMarks{\RegNo}{\Name}{#2}%
            }%
    \end{tabular}%
}%


\begin{document}
\DTLloaddb{NamesDB}{namespgf.csv}
%\PrintNamesDTLTable{NamesDB}% Useful for debugging

\DTLloaddb{MarksDB}{markspgf.csv}
%\PrintMarksDTLTable{MarksDB}% Useful for debugging

\PrintNamesWithMarksDTLTable{NamesDB}{MarksDB}
\end{document}

Answer 3

I realize that you're looking for a pgfplotstable solution only, but as food for thought I'll add another option. This uses the namespgf.csv to generate a set of key-value pairs. When the marks are written, the name corresponding to the registration number is inserted. I made a couple of mock spreadsheets with ~3000 entries (using numbers rather than names) and it took ~15s to compile. I'm not sure how that would compare to a pgfplotstable solution in terms of efficiency. Note that to simplify things a bit, I removed the headers from the data and added them to the table manually.

\documentclass{article}

\usepackage{xparse}
\usepackage{booktabs}
\usepackage{longtable}
\usepackage{filecontents}

\begin{filecontents*}{namespgf.csv}
5501,Kathirvelu A
5502,Gugan K
5503,Kalaitchelvi S
5504,Suresh S
5505,Mahesh K
\end{filecontents*}
%
\begin{filecontents*}{markspgf.csv}
5501,67
5502,25
5503,62
5505,95
\end{filecontents*}

\ExplSyntaxOn
\tl_new:N \g_tab_rows_tl
\ior_new:N \g_names_ior
\ior_new:N \g_marks_ior
\prop_new:N \g_names_prop

\ior_open:Nn \g_names_ior {namespgf.csv}
\ior_open:Nn \g_marks_ior {markspgf.csv}

\cs_new:Npn \set_name_keys:w #1,#2\q_stop
    {
        \prop_put:Nnn \g_names_prop {#1} {#2}% reg number is the key and name is the value
    }

\cs_new:Npn \tab_write_keys:w #1,#2\q_stop % data from grades sheet
    {
        \prop_gpop:NnN \g_names_prop {#1} \l_tmpa_tl % pop the name for the reg number
        \tl_gput_right:Nn \g_tab_rows_tl {#1&} % number 1st
        \tl_gput_right:NV \g_tab_rows_tl \l_tmpa_tl % name 2nd
        \tl_gput_right:Nn \g_tab_rows_tl {&#2\\} % grade 3rd
    }

\ior_str_map_inline:Nn \g_names_ior
    {
        \set_name_keys:w #1\q_stop
    }

\ior_str_map_inline:Nn \g_marks_ior
    {
        \tab_write_keys:w #1\q_stop
    }

\ior_close:N \g_names_ior
\ior_close:N \g_marks_ior

\NewDocumentCommand { \WriteRows } {}
    {
        \tl_use:N \g_tab_rows_tl
    }

\ExplSyntaxOff

\begin{document}

\begin{longtable}{clc}
Reg.No.&Name&Marks\\
\toprule
\WriteRows
\bottomrule
\end{longtable}

\end{document}