pgfplots(table) spreadsheet like formulas

Question

I'm currently experimenting with the pgfplots and pgfplotstable packages. I noticed the possibility to create new columns (copying or transforming another column) when I was wondering if it was possible to use spreadsheet like formulas (meaning the value of each cell of the new column should be the value of the cell to its left (same row) + the value of the cell above (same column), and yes there has to be some initializing).

Does anyone know of such a feature? And if yes how to do this? (regardless if it is only pgfplotstable or even pgfplots)

You can look at the datatool package, see the manual from page 76 onwards. Note that this is different from pgfplotstable. — Marijn, Mar 20 '20 at 12:46
Hm ok I need to feed the output (the table with the additional column) to pgfplots, is that possible that way? (didn't saw this yet) — atticus, Mar 20 '20 at 13:12
The packages can be combined, see some examples at https://tex.stackexchange.com/questions/tagged/pgfplots+datatool. — Marijn, Mar 20 '20 at 13:16
Is there a better way to do this than just use \DTLforeach*{values}{\x=x,\y=y}{ \addplot coordinates {(\x,\y)}; }? — atticus, Mar 20 '20 at 13:52
You have in total 4 questions. Three of them have an answer. None is accepted. In addition, apparently no or little upvotes from your side. Do you understand how this community works? :) — Dr. Manuel Kuehner, Mar 20 '20 at 17:20
Yes I do ;) It's just that some of my questions do not have any answers at all (yet) and in many other cases the issue isn't resolved completely up to now. Here actually I just wait if @Marijn writes again about the datatool packacke and how to use it in combination with pgfplots — atticus, Mar 20 '20 at 17:31
well I think I try this in another post, actually this doesn't fit into this one right. This post is about calculations in pgfplotstable and not about datatool ;) — atticus, Mar 20 '20 at 23:43
see https://tex.stackexchange.com/questions/533465/use-datatool-to-process-input-and-pass-it-further-to-pgfplots-to-plot-the-data — atticus, Mar 21 '20 at 15:50

frougon · Accepted Answer · 2020-03-20T17:18:15.153

This can be done with some limitations using pgfplotstable's \pgfplotstablecreatecol macro (also with create on use to lazily create the column data, but its contents would then be lost after returning from \pgfplotstabletypeset). According to the pgfplotstable documentation:

Currently, you can only access three values of one column at a time: the current row, the previous row and the next row. Access to arbitrary indices is not (yet) supported.

In the example below, I implemented the formula described in the question, initializing it with (arbitrary) value 100 in “row -1.” Since the initial table data is:

the computed values are:

1 + 100 = 101
6 + 101 = 107
11 + 107 = 118

In order to access previously-computed values in the column being dynamically created, I store them globally (here: only the most recently computed value, using \xdef\myPreviousValue{...}) because pgfplotstable's \prevrow macro doesn't give access to values from the column being created, as it seems. If access to any previously-computed value in the column being created is desired, one could use a pgfmath array¹ or an expl3 tl or seq variable, for instance.

\begin{filecontents*}{data.csv}
 x   y
 0   1
 5   6
10  11
\end{filecontents*}

\documentclass{article}
\usepackage{booktabs}
\usepackage{pgfplotstable}
\pgfplotsset{compat=1.16}

\pgfplotstableread[row sep=newline, col sep=space]{data.csv}\myTable

\newcommand*{\myPreviousValue}{100} % initialization (row -1, sort of)

% Dynamically create column z
\pgfplotstablecreatecol[
  create col/assign/.code={%
    \pgfmathsetmacro{\myValue}{int(\thisrow{y} + \myPreviousValue)}%
    \pgfplotstableset{create col/next content/.expand once={\myValue}}%
    \xdef\myPreviousValue{\myValue}%
  }]
{z}\myTable

\begin{document}

\pgfplotstabletypeset[
  columns/x/.style={column name={$x$}},
  columns/y/.style={column name={$y$}},
  columns/z/.style={column name={$z$}},
  every head row/.style={before row=\toprule, after row=\midrule},
  every last row/.style={after row=\bottomrule}
  ]{\myTable}

\end{document}

Note: the int() in the pgfmath expression I used might appear as unnecessary because by default, \pgfplotstabletypeset formats values using \pgfmathprintnumber and, again by default, \pgfmathprintnumber detects if the input has a fractional part equal to zero in order to special-case the printing of integers. For instance, \pgfmathprintnumber{118.0} prints 118 by default. However, without the int(), the values stored in \myValue—and thus also in the created in-memory column and in \myPreviousValue—would have a trailing .0; using the int() function prevents this.

Footnote

Search the TikZ & PGF manual for “array access operators” in the Mathematical and Object-Oriented Engines part.

Oh, that looks pretty nice just three little questions: in the line \pgfmathsetmacro{\myValue}{int(\thisrow{y} + \myPreviousValue)}% what does the int() do? Is this some kind of "cast" of the result?
Is there another way to insert tables from pgfplotstables to pgfplots without macros (I suppose not or is there one?)

And the last one, just curious is there a specific reason why people add a % at the end of the line? — atticus, Mar 20 '20 at 16:42

pgfplots(table) spreadsheet like formulas

1 Answers1

Linked