[Tex/LaTex] pgfplots need help with complicated stacked bar chart using selected rows from data table

bar chartpgfplotspgfplotstabletables

I'm trying to recreate this bar chart from the data table found in the MWE below.

example chart

It involves doing a couple things that I can't figure out:

  1. stack positive and negative bars on the same axis, such that the negative numbers don't merely subtract from the positive ones (this is the default behavior of xbar stacked)
  2. selecting only certain rows from the table. For the blue bars I need only rows 1-3 (counting the column headers as row 0). For the red bars I need two instances of row 4 and one of row 5.
  3. creating the lines that show the values from the SUM columns and creating data labels.
  4. creating a legend using the column headers of columns 2 and 3 (even though column 3 contains no real data).
  5. reverse the ordering of the items on the y axis. Notice that the categories in the MWE count down from top to bottom; I'd like the opposite.

You'll see where I've got so far by looking at the MWE. As for the rest, I'm rather baffled. I need to automate this process and this is the format the data comes in (so reformatting the underlying data table is a suboptimal solution).

MWE:

\documentclass{article}

\usepackage{pgfplots}
\usepackage{filecontents}

\begin{document}

\begin{filecontents}{data.dat}
category,value,negvalue
cat1,3,0
cat2,5,0
cat3,1,0
cat1and2n,-6,0
cat3n,-3,0
cat1sum,-3,0
cat2sum,-1,0
cat3sum,-2,0
\end{filecontents}

\pgfplotstableset{col sep=comma}
\pgfplotstableread{data.dat}\datatable

\begin{tikzpicture}
\begin{axis}[
xbar,
yticklabels from table={\datatable}{category},
ytick=data]

\addplot table[
y expr=\coordindex,
x=value]
{data.dat};
\end{axis}
\end{tikzpicture}

\end{document}

I appreciate any help!

Best Answer

For 2.: you can filter the rows like in Calculate and draw difference of two plots automatically

\pgfkeys{
    /tr/rowfilter/.style 2 args={
        /pgfplots/x filter/.append code={
            \edef\arga{\thisrow{#1}}
            \edef\argb{#2}
            \ifx\arga\argb
            \else
                \def\pgfmathresult{}
            \fi
        }
    }
}

but this filter is only applicable for fixed contens

\addplot table[/tr/rowfilter={category}{cat1sum}] {data.dat};

but this gives you only the 6th row. Unfortunately I don't know TeX enough to check for a pattern like 'cart[0-9]sum'

Related Question