# Write a column selectively to the appropriate row using pgfplotstable?

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}
%=============================================================================
%
\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.

-
Is the namespgf.csv file considered to be complete? i.e., is it guaranteed that every entry in markspgf.csv exists in namespgf.csv. Also, is there a typo in the markspgf.csv file created by filecontents as it does not match the description given earlier? –  Peter Grill Jul 27 '12 at 6:34
What you want is a database table join. pgfplotstable has no built-in support for table joins. That said, you could certainly write more-or-less sophisticated join implementations (either using linear search as in Peter Grills answer or indexed search as in Scott's answer). Note that table joins have very good implementations in SQL DBs. A ready-to-use example might be SQLLite. Are you sure you want to do that in TeX? –  Christian Feuersänger Jul 28 '12 at 9:22
Yes, that is precisely what a table join is... and I fear that I might not have time to write a solution soon (some hackish solution which fits just to your minimal example might work, though - just as scott did). –  Christian Feuersänger Jul 28 '12 at 14:26
I would recommend importing the 2 CSV files into a database. there you join the two tables creating a new table and export that as CSV file. TeX is for typesetting, not for database stuff. –  user25094 Jan 27 '13 at 22:51

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}
%=============================================================================
%
\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}


-
This is great and I was looking for something like this @percusse. One small query: Instead of writing N/A, how to remove that number and name totally (i.e., remove the row containing 5504 Suresh S N/A so that sorting after this is easy? May be slight change in conditional? But how? –  Harish Kumar Jul 28 '12 at 23:19
@HarishKumar That part I don't know yet. Did you try with bigger data files already? –  percusse Jul 28 '12 at 23:26
@ChristianFeuersänger Is there any way to omit lines in the table based on some filtering criteria? –  percusse Jul 29 '12 at 23:09
@percusse Yes, filtering can be done using row predicate. However, row predicate was designed to operate on row indices rather than content. If you can answer the question "should row no index be typeset", then you are fine. Everything beyond that would be a feature request. –  Christian Feuersänger Jul 30 '12 at 7:20
This is the exact hack I needed. Great solution and help. Thank you. I wish, may this feature be added in the new version (a request to Dr. @Christian Feuersänger). –  Harish Kumar Aug 4 '12 at 0:55

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}
%\PrintNamesDTLTable{NamesDB}% Useful for debugging

%\PrintMarksDTLTable{MarksDB}% Useful for debugging

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

-
Thank you, but I already have it working using datatool. But your approach is different than mine and my real application is some what complex (involving many numbers). I want a solution using pgfplotstable for some reasons (academic interest is one of them). Any suggestions? Please. –  Harish Kumar Jul 27 '12 at 7:41

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}
`
-
I should appreciate your effort. But I have a set up already built for a purpose (Putting a merit list for our PG admission) that uses pgfplotstable heavily. I am afraid that now I may not get enough time to change all that as the deadline is nearing. Hence a pgfplotstable solution is badly needed for me. –  Harish Kumar Jul 28 '12 at 12:55