# Pgfplots: Assemble dates from different columns

I am trying to plot a datafile of a time series in pgfplots. The date is not in the YYYY-MM-DD hh:mm format required by the PGF calendar library (see p. 210 in the pgfplots manual), but in different columns called year, month, day etc.

Is there a way of joining different columns into a new string in the required format on the fly when calling \addplot table (maybe with something like x expr=...)? I'm trying to avoid using \pgfplotstableread with a create on use command because I have found that to take surprisingly long to run.

Here's an example of the datafile format I'm talking about:

year, month, day, hour, minute, value
2006, 5, 21, 1, 0, 10.5
2006, 5, 22, 2, 0, 23.4
2006, 5, 22, 4, 5, 30.1
2006, 5, 27, 3, 0, 35.2

-
You can always use create on use with a pgfplotstableset command after you use pgfplotstableread. –  philosodad Jan 25 '11 at 2:34
can be done with pst-plot. When using xelatex you can create directly a pdf –  Herbert Jan 25 '11 at 8:09

I am aware that this question is quite old now... but in case there is still interest in an answer: here is one which works "on-the-fly":


\documentclass{minimal}
\usepackage{pgfplots}
\usepgfplotslibrary{dateplot}
\begin{document}

\pgfplotsset{
custom date format/.code={
% set the normal data format:
\pgfkeysalso{date coordinates in=x}%
% query the 'x coord trafo' key's value:
\pgfkeysgetvalue{/pgfplots/x coord trafo/.@cmd}\temp
% store it (globally):
\global\let\theolddatacoordtrafo=\temp
% redefine it:
\pgfkeysdef{/pgfplots/x coord trafo}{%
% evaluate the expression:
\edef\temp{#1}%
\message{Coordinate \coordindex: Using \temp.^^J}%
% now, insert the *value* of '\temp' as argument to
% the original 'x coord trafo':
\expandafter\theolddatacoordtrafo\temp\pgfeov
}%
},
}
\begin{tikzpicture}
\begin{axis}[
custom date format={\thisrow{year}-\thisrow{month}-\thisrow{day} \thisrow{hour}:\thisrow{minute}},
% pretty-printing:
width=\linewidth,
xticklabel style={rotate=90,anchor=near xticklabel},
xticklabel=\day. \hour:\minute,
]

year, month, day, hour, minute, value
2006, 5, 21, 1, 0, 10.5
2006, 5, 22, 2, 0, 23.4
2006, 5, 22, 4, 5, 30.1
2006, 5, 27, 3, 0, 35.2
};
\end{axis}
\end{tikzpicture}
\end{document}


My idea here was to define a custom 'x coord trafo', which concatenates your columns in a "proper" way and then hands the concatenated and expanded ('\edef' = expanded definition) result to the 'x coord trafo' which is used by the dateplot lib.

Consequently, the dataplot lib is unaware of the preprocessing step and the preprocessing step is performed on-the-fly.

Actually, the feature to assemble symbolic coordinates from tables is missing in pgfplots. I have taken a note on my todo list such that future versions will (eventually) support something like the 'x symbolic expr={\thisrow{year}-\thisrow{month}...}'.

-
Oh wow, this is very impressive! Works like a charm! Thanks for answering the question even though it's quite old, the solution is definitely still useful to me! (And thanks for pgfplots, by the way. Such a useful tool!) –  Jake Mar 11 '11 at 23:32

I would recommend a preprocess step to the input file. With Linux, the following command on a terminal

awk --field-separator="," '{printf "%d-%d-%d %d:%d %e\n",$1,$2,$3,$4,$5,$6}' input_file > output_file


results in an output_file with the format

2006-5-21 1:0 1.050000e+01
2006-5-22 2:0 2.340000e+01
2006-5-22 4:5 3.010000e+01
2006-5-27 3:0 3.520000e+01


etc., which should be usable with the pgfplots package in reasonable time.

-
Thanks for your answer, that's a very elegant way of doing it. Note that the separator between the date/time and the number should be something other than a whitespace (a comma, for example), because the date and time need to be in the same field for pgfplots to use it. The pattern could thus be something like "%d-%d-%d %d:%d,%e\n". –  Jake Mar 3 '11 at 9:02

I don't see any way to do this with pgfplots without using the create on use command, as this is exactly what the command is for. Your speed issue may be related to timing: it may be much faster to import the data one time using \pgfplotstableread, and then use pgfplotstableset to create custom columns, or to use the read completely command to put the entire file into memory before any post or pre processing.

-
Thanks for your answer. It seems that my datafile is just too large to be handled gracefully by pgfplotstables (3000 rows with 15 columns): Reading the table first using \pgfplotstableread is still unusably slow. But you're right, create on use seems to be the only way to do stuff like that. –  Jake Jan 25 '11 at 21:29

This might be complicated to do in Latex, if I were you I would preprocess the data. Tools like Excel( use the concatenate function ) or R ( use the paste function ) are helpfull and you have it done in two minutes.

Unless its absolutely necessary to that directly in Latex ofc...

-