5

I have two tables (A and B), both with a string valued column and an integer valued column. What i would like to actually have is a sorted table B sorted by the values of table A. The crucial point is that the rows are identified by the string value, not the index, and that B does not contain all rows that A has (otherwise i could just sort according to the string column first).

\documentclass{article}

\usepackage{pgfplots}
\usepackage{pgfplotstable}

    \def\histocomp#1 sort on #2{

    \pgfplotstableread{#2}\sortOnTable

    \begin{tikzpicture}
    \begin{axis}
    \pgfplotstablesort[sort key=index,sort key from=\sortOnTable]\loadedtable\sortOnTable
    \addplot+ [no markers] table[x expr=\coordindex, y index=1] \loadedtable;


    \pgfplotstableread{#1}\loadedtable 
    \pgfplotstablesort[sort key=index,sort key from=\sortOnTable]\loadedtable\loadedtable
    \addplot+ [only marks] table[x expr=\coordindex, y index=1] \loadedtable;
    \end{axis}
    \end{tikzpicture}

    }

\begin{document}
\histocomp{B} sort on {A}
\end{document}

A:

index values
foo 0.5
bar 0.2
baz 0.7

B:

index values
bar 0.8
baz 0.5

Is there such a thing as an outer join or something?

1
  • pgfplotstable doesn't have basic database functionalities. It takes quite some work for even this table join. But maybe datatool offers similar functionality.
    – percusse
    Aug 19, 2013 at 11:55

1 Answer 1

5

If I understand you correctly, you want to sort table B such that it's ordered according to the values column in A for the corresponding index column joined on A and B. In datatool you can provide a handler to \dtlsort that is used for the comparisons. In the example below I've defined a handler macro that looks up the corresponding value in A. This assumes the following CSV files:

data1.csv:

index,values
foo,0.5
bar,0.2
baz,0.7
wibble,0.1

data2.csv:

index,values
bar,0.8
wibble,0.6
baz,0.5

MWE:

\documentclass{article}

\usepackage{etoolbox}
\usepackage[verbose]{datatool}

\DTLloaddb{A}{data1.csv}
\DTLloaddb{B}{data2.csv}

% Define a comparison handler

\newcommand{\joincompare}[3]{%
 \ifstrequal{#2}{#3}%
 {%
   #1=0\relax
 }%
 {%
   % Fetch the row in A that corresponds to index "#2"
   \dtlgetrowforvalue{A}{\dtlcolumnindex{A}{index}}{#2}%
   % Fetch the "values" field in that row
   \dtlgetentryfromcurrentrow{\valueI}{\dtlcolumnindex{A}{values}}%
   % Fetch the row in A that corresponds to index "#3"
   \dtlgetrowforvalue{A}{\dtlcolumnindex{A}{index}}{#3}%
   % Fetch the "values" field in that row
   \dtlgetentryfromcurrentrow{\valueII}{\dtlcolumnindex{A}{values}}%
   % Compare the values
   % (This assumes values are plain numbers with a full stop as the
   % decimal point and no number group separators. If this isn't the
   % case you'll need to use \DTLifnumlt instead of \dtlifnumlt.)
   \dtlifnumlt{\valueI}{\valueII}{#1=-1\relax}{#1=1\relax}%
 }%
}

\begin{document}

Unsorted table~B data:
\begin{center}
\DTLdisplaydb{B}
\end{center}

Unsorted table~A data:
\begin{center}
\DTLdisplaydb{A}
\end{center}

\dtlsort{index}{B}{\joincompare}

Sorted table~B data:
\begin{center}    
\DTLdisplaydb{B}
\end{center}

Merged tables:
\begin{center}
\begin{tabular}{lll}%
\bfseries Index & \bfseries Values (B) & \bfseries Values (A)
\DTLforeach*{B}{\Index=index,\Values=values}{%
  \\%
  \Index & \Values & 
   \edef\dogetrow{\noexpand\dtlgetrowforvalue{A}{\dtlcolumnindex{A}{index}}{\Index}}%
   \dogetrow
   \dtlgetentryfromcurrentrow{\valuesA}{\dtlcolumnindex{A}{values}}%
   \valuesA
}%
\end{tabular}%
\end{center}
\end{document}

This produces:

Image of data

However I think you'd be better off storing your data in SQL tables and using an SQL statement to pull out the required data. You could either write a script that would do this and write out the appropriate results to a file TeX can read, or you can use datatooltk to pull the information (using an SQL SELECT statement) into a datatool database file. This is especially true if you have a large amount of data, as TeX isn't designed to be a structured query language.

Update:

The example below is a modification of the above that also displays the data, firstly in a scatter plot and then in a bar chart. For this, it's easier to append the relevant values from database A to database B:

\documentclass{article}

\usepackage{etoolbox}
\usepackage{datatool}
\usepackage{dataplot,databar}

\DTLloaddb{A}{data1.csv}
\DTLloaddb{B}{data2.csv}

% Define a comparison handler

\newcommand{\joincompare}[3]{%
 \ifstrequal{#2}{#3}%
 {%
   #1=0\relax
 }%
 {%
   % Fetch the row in A that corresponds to index "#2"
   \dtlgetrowforvalue{A}{\dtlcolumnindex{A}{index}}{#2}%
   % Fetch the "values" field in that row
   \dtlgetentryfromcurrentrow{\valueI}{\dtlcolumnindex{A}{values}}%
   % Fetch the row in A that corresponds to index "#3"
   \dtlgetrowforvalue{A}{\dtlcolumnindex{A}{index}}{#3}%
   % Fetch the "values" field in that row
   \dtlgetentryfromcurrentrow{\valueII}{\dtlcolumnindex{A}{values}}%
   % Compare the values
   % (This assumes values are plain numbers with a full stop as the
   % decimal point and no number group separators. If this isn't the
   % case you'll need to use \DTLifnumlt instead of \dtlifnumlt.)
   \dtlifnumlt{\valueI}{\valueII}{#1=-1\relax}{#1=1\relax}%
 }%
}

\begin{document}

Unsorted table~B data:
\begin{center}
\DTLdisplaydb{B}
\end{center}

Unsorted table~A data:
\begin{center}
\DTLdisplaydb{A}
\end{center}

\dtlsort{index}{B}{\joincompare}

Sorted table~B data:
\begin{center}    
\DTLdisplaydb{B}
\end{center}

Append ``values'' column from A to B (only for those rows that
are in B). Call the new column ``valuesA'':
\dtlforcolumn{\Index}{B}{index}{%
   \edef\dogetrow{\noexpand\dtlgetrowforvalue{A}{\dtlcolumnindex{A}{index}}{\Index}}%
   \dogetrow
   \dtlgetentryfromcurrentrow{\valuesA}{\dtlcolumnindex{A}{values}}%
   \edef\dogetrow{\noexpand\dtlgetrowforvalue{B}{\dtlcolumnindex{A}{index}}{\Index}}%
   \dogetrow
   \dtlappendentrytocurrentrow{valuesA}{\valuesA}%
   \dtlrecombine
}%

Updated table B:
\begin{center}
\DTLdisplaydb{B}
\end{center}

Plot of ``values'' column against ``valuesA'' column shown in
figure~\ref{fig:plot}. A bar chart is shown in
figure~\ref{fig:bar}.

\begin{figure}[htbp]
\centering
% round values on x tics to 1 d.p.
\setcounter{DTLplotroundXvar}{1}%
% similarly for the y axis
\setcounter{DTLplotroundYvar}{1}%
\DTLplot
{B}% database name
{%
   x=values,% column to use for x coords
   y=valuesA,% column to use for y coords
   xlabel={Values in column ``values''},% x axis label
   ylabel={Values in column ``valuesA''},% y axis label
   style=markers,% scatter plot (rather than line plot)
   marks={\pgfuseplotmark{x}},% plot markers
   ticdir=out,% direction of tics
   xticgap=0.1,% increment between tic marks on x axis
   yticgap=0.1,% increment between tic marks on y axis
   width=3in,% width of x-axis
   height=3in,% height of y-axis
}%
\caption{Plot of Values in Database ``B''}
\label{fig:plot}
\end{figure}

\begin{figure}[htbp]
\centering
% set the width of each bar:
\setlength{\DTLbarwidth}{5mm}
% set the colour of the bars:
\DTLsetbarcolor{1}{blue}%
\DTLsetbarcolor{2}{red}%
% Display the bar chart:
\DTLmultibarchart
{% bar chart settings
   variables={\ValuesB,\ValuesA},% plot variables
   length=2in,% length of Y axis
   axes=both,% show both axes
   uppermultibarlabels={B,A},% upper bar labels
   barlabel={\Index},% bar group labels
   verticalbars=true,% vertical orientation
}%
{B}% database name
{\ValuesB=values,\ValuesA=valuesA,\Index=index}% assignment list
\caption{Bar Chart of Values in Database ``B''}
\label{fig:bar}
\end{figure}

\end{document}

This produces the plot:

Figure 1

and the bar chart:

Figure 2

Caveat: TeX is a typesetting language not a structured query language or mathematical language, so it's slower to do this all in TeX rather than combining TeX with SQL and R/Matlab/Octave.

Note that with the latest version of datatool (v2.17) you can replace

\edef\dogetrow{\noexpand\dtlgetrowforvalue{A}{\dtlcolumnindex{A}{index}}{\Index}}%
\dogetrow

with

\edtlgetrowforvalue{A}{\dtlcolumnindex{A}{index}}{\Index}%

(And similarly for B)

2
  • nice! I think this is the best it can get, no clue how i can feed pgfplots with it, but i will still accept this as answer. In the mean time, i helped myself with some python scripts... If you manage to feed the sorted data into pgfplots however, that would make a really nice addition to this answer!
    – kutschkem
    Aug 30, 2013 at 13:01
  • 1
    @kutschkem datatool comes with a plotting package called dataplot if that's of any help. I've not used pgfplots so I don't know how to integrate datatool databases into it. Aug 30, 2013 at 13:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .