How do I sum CSV-values in to cells?

Question

I am both new here on stackexchange and fairly new to TeX.

I have tried searching for similar questions but found none.

I am working on a document where I need to pppulate a table from a csv file. The data should not be populated as is, but the number of values corresponding to each cell in the table (explained below). I have attached a picture showing the end result I want and a MWE showing the table (I don't know how to color the cells and get the labels "Konsekvens" and "Sannolikhet" in the right place without mixing up the formatting of the cells) and some example data that should result in the table in the picture.

For example for each 4,4 in the data set the upper right cell should increase, resulting in a 7 in that cell since there are 7 4,4.

The data is just example data so the table might differ slightly from the image.

A minor problem is that I can not get m{2cm} to work in stead of p{2cm} as shown here.

I have tried datatool but my only thought there was to use variables but using 16 variables feels like a complicated solution and I hope it can be done in a better way, and that's why I'm asking for help here.

\documentclass{article}

\usepackage{filecontents}
\usepackage{multirow}
\usepackage{graphicx}

\begin{filecontents*}{mycsvdata.csv}
Sannolikhet,Konsekvens
4,4
4,4
4,4
4,4
4,4
4,4
4,4
4,3
4,2
4,2
2,4
2,4
2,4
2,4
2,2
1,4
1,4
1,4
1,4
1,4
1,3
1,3
1,2
1,1
\end{filecontents*}

\begin{document}
\begin{tabular}{c|p{2cm}|p{2cm}|p{2cm}|p{2cm}|p{2cm}|}
\cline{2-6}
   \multirow{5}{*}{\rotatebox{90}{Konsekvens}}
                     & Mycket allvarlig&~&~&~&~ \\[0.5cm]\cline{2-6}
        & Allvarlig&~&~&~&~ \\[0.5cm]\cline{2-6}
        & Måttlig&~&~&~&~ \\[0.5cm]\cline{2-6}
        & Försumbar&~&~&~&~ \\[0.5cm]\cline{2-6}
   \multicolumn{1}{l}{}&~&Osannolik&Möjlig&Sannolikt&Mycket sannolikt \\\cline{3-6}
     \multicolumn{6}{r}{Sannolikhet}
\end{tabular}

\end{document}

Answer 1

If you don't mind a little bit of exotic Lua programming...

\documentclass{article}
\usepackage[T1]{fontenc}
\usepackage{colortbl}
\usepackage{multirow}
\usepackage{graphicx}
\usepackage{array}
\usepackage{luacode}
\usepackage{hhline}
\usepackage{xcolor}


\begin{document}

\newcolumntype{M}[1]{>{\centering\arraybackslash}m{#1}}

\begin{luacode*}

local table_str = [[
\begin{tabular}{c|M{2cm}|M{2cm}|M{2cm}|M{2cm}|M{2cm}|}
\hhline{~|*{5}-}
   \multirow{5}{*}{\rotatebox{90}{Konsekvens}}
                     & Mycket allvarlig&@&@&@&@ \\[0.5cm]\hhline{~|*{5}-}
        & Allvarlig&@&@&@&@ \\[0.5cm]\hhline{~|*{5}-}
        & Måttlig&@&@&@&@ \\[0.5cm]\hhline{~|*{5}-}
        & Försumbar&@&@&@&@ \\[0.5cm]\hhline{~|*{5}-}
   \multicolumn{1}{l}{}&~&Osannolik&Möjlig&Sannolikt&Mycket sannolikt \\\hhline{~|~|*{4}-}
     \multicolumn{6}{r}{Sannolikhet}
\end{tabular}
]]

local result_table = {}
for i=1,4 do
    result_table[i] = {}
    for j=1,4 do
        result_table[i][j] = 0
    end
end

local line_num = 0
for line in io.lines('mycsvdata.csv') do
    if line_num ~= 0 then
        local stripped_line = string.gsub(line, '%s', '')
        local comma_pos = string.find(line, ',')
        if comma_pos ~= nil then
            local left = tonumber(stripped_line:sub(1, comma_pos - 1))
            local right = tonumber(stripped_line:sub(comma_pos + 1))
            result_table[left][right] = result_table[left][right] + 1
        end
    end
    line_num = line_num + 1
end

for i=0,15 do
    local y = 4 - math.floor(i / 4)
    local x = i % 4 + 1
    local cell_num = result_table[x][y]
    local cell_str = tostring(cell_num)
    if cell_num == 0 then
        cell_str = ''
    end
    if y+x <= 4 then
        cell_str = [[{\cellcolor{green!50}}]] .. cell_str
    elseif x+y <= 5 then
        cell_str = [[\cellcolor{yellow!50}]] .. cell_str
    elseif x+y <= 6 then
        cell_str = [[\cellcolor{orange!50}]] .. cell_str
    else
        cell_str = [[\cellcolor{red!50}]] .. cell_str
    end
    
    local new_str, sub_count = string.gsub(table_str, '@', cell_str, 1)
    table_str = new_str
end

local out_str, _ = string.gsub(table_str, '\n', '')
tex.print(out_str)

\end{luacode*}


\end{document}

Answer 2

I´m not totally satisfied with the solution, but I have a working code now. I thought I might as well share it so someone else can make use of it.

I still want "Sannolikhet" to be centered over the four last columns but when I do that I get vertical lines where they are not supposed to be. I also want "Konsekvens" to be vertically centered over the four top rows.

The result:

The code:

\documentclass{article}

\usepackage{filecontents}
\usepackage{multirow}
\usepackage{graphicx}
\usepackage[table]{xcolor}
\usepackage{hhline}
\usepackage{datatool}
\usepackage{array}

\begin{filecontents*}{mycsvdata.csv}
Sannolikhet;Konsekvens
4;4
4;4
4;4
4;4
4;4
4;4
4;4
4;3
4;2
4;2
2;4
2;4
2;4
2;4
2;2
1;4
1;4
1;4
1;4
1;4
1;3
1;3
1;2
1;1
\end{filecontents*}

\newcolumntype{M}[1]{>{\centering\arraybackslash}m{#1}}

\definecolor{orangex}{RGB}{255,205,55}
\definecolor{greenx}{RGB}{146,208,80}

\newcounter{AA}
\newcounter{AB}
\newcounter{AC}
\newcounter{AD}
\newcounter{BA}
\newcounter{BB}
\newcounter{BC}
\newcounter{BD}
\newcounter{CA}
\newcounter{CB}
\newcounter{CC}
\newcounter{CD}
\newcounter{DA}
\newcounter{DB}
\newcounter{DC}
\newcounter{DD}

\DTLsetseparator{;}

\begin{document}

\DTLloaddb{riskvarde}{mycsvdata.csv}
\DTLforeach*{riskvarde}{\Sannolikhet=Sannolikhet,\Konsekvens=Konsekvens}{
\ifthenelse{\DTLiseq{\Sannolikhet \Konsekvens}{11}}{\stepcounter{AA}}{}
\ifthenelse{\DTLiseq{\Sannolikhet \Konsekvens}{12}}{\stepcounter{AB}}{}
\ifthenelse{\DTLiseq{\Sannolikhet \Konsekvens}{13}}{\stepcounter{AC}}{}
\ifthenelse{\DTLiseq{\Sannolikhet \Konsekvens}{14}}{\stepcounter{AD}}{}
\ifthenelse{\DTLiseq{\Sannolikhet \Konsekvens}{21}}{\stepcounter{BA}}{}
\ifthenelse{\DTLiseq{\Sannolikhet \Konsekvens}{22}}{\stepcounter{BB}}{}
\ifthenelse{\DTLiseq{\Sannolikhet \Konsekvens}{23}}{\stepcounter{BC}}{}
\ifthenelse{\DTLiseq{\Sannolikhet \Konsekvens}{24}}{\stepcounter{BD}}{}
\ifthenelse{\DTLiseq{\Sannolikhet \Konsekvens}{31}}{\stepcounter{CA}}{}
\ifthenelse{\DTLiseq{\Sannolikhet \Konsekvens}{32}}{\stepcounter{CB}}{}
\ifthenelse{\DTLiseq{\Sannolikhet \Konsekvens}{33}}{\stepcounter{CC}}{}
\ifthenelse{\DTLiseq{\Sannolikhet \Konsekvens}{34}}{\stepcounter{CD}}{}
\ifthenelse{\DTLiseq{\Sannolikhet \Konsekvens}{41}}{\stepcounter{DA}}{}
\ifthenelse{\DTLiseq{\Sannolikhet \Konsekvens}{42}}{\stepcounter{DB}}{}
\ifthenelse{\DTLiseq{\Sannolikhet \Konsekvens}{43}}{\stepcounter{DC}}{}
\ifthenelse{\DTLiseq{\Sannolikhet \Konsekvens}{44}}{\stepcounter{DD}}{}
}


\begin{tabular}{c|M{2cm}|M{2cm}|M{2cm}|M{2cm}|M{2cm}|}




\hhline{~|*{5}-}
   \multirow{5}{*}{\rotatebox{90}{Konsekvens}}
                     & Mycket allvarlig&\cellcolor{yellow!75}\ifthenelse{\theAD>0}{\theAD}{}&\cellcolor{orangex}\ifthenelse{\theBD>0}{\theBD}{}&\cellcolor{red!50}\ifthenelse{\theCD>0}{\theCD}{}&\cellcolor{red!50}\ifthenelse{\theDD>0}{\theDD}{} \\\hhline{~|*{5}-}
        & Allvarlig\newline&\cellcolor{greenx}\ifthenelse{\theAC>0}{\theAC}{}&\cellcolor{yellow!75}\ifthenelse{\theBC>0}{\theBC}{}&\cellcolor{orangex}\ifthenelse{\theCC>0}{\theCC}{}&\cellcolor{red!50}\ifthenelse{\theDC>0}{\theDC}{} \\\hhline{~|*{5}-}
        & Måttlig\newline&\cellcolor{greenx}\ifthenelse{\theAB>0}{\theAB}{}&\cellcolor{greenx}\ifthenelse{\theBB>0}{\theBB}{}&\cellcolor{yellow!75}\ifthenelse{\theCB>0}{\theCB}{}&\cellcolor{orangex}\ifthenelse{\theDB>0}{\theDB}{} \\\hhline{~|*{5}-}
        & Försumbar\newline&\cellcolor{greenx}\ifthenelse{\theAA>0}{\theAA}{}&\cellcolor{greenx}\ifthenelse{\theBA>0}{\theBA}{}&\cellcolor{greenx}\ifthenelse{\theCA>0}{\theCA}{}&\cellcolor{yellow!75}\ifthenelse{\theDA>0}{\theDA}{} \\\hhline{~|*{5}-}
        \multicolumn{1}{l}{}&~&Osannolik&Möjlig&Sannolikt&Mycket sannolikt \\ \hhline{~|~|*{4}-}
        \multicolumn{6}{r}{Sannolikhet}
\end{tabular}

\end{document}