How do I sum CSV-values in to cells?
TeX - LaTeX Asked by Softest on August 6, 2020
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}
2 Answers
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]{>{centeringarraybackslash}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}
Correct answer by Alan Xiang on August 6, 2020
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]{>{centeringarraybackslash}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}-}
& Allvarlignewline&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åttlignewline&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örsumbarnewline&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}
Answered by Softest on August 6, 2020
Add your own answers!
Ask a Question
Get help from others!
Recent Answers
- Peter Machado on Why fry rice before boiling?
- Jon Church on Why fry rice before boiling?
- Lex on Does Google Analytics track 404 page responses as valid page views?
- Joshua Engel on Why fry rice before boiling?
- haakon.io on Why fry rice before boiling?
Recent Questions
- How can I transform graph image into a tikzpicture LaTeX code?
- How Do I Get The Ifruit App Off Of Gta 5 / Grand Theft Auto 5
- Iv’e designed a space elevator using a series of lasers. do you know anybody i could submit the designs too that could manufacture the concept and put it to use
- Need help finding a book. Female OP protagonist, magic
- Why is the WWF pending games (“Your turn”) area replaced w/ a column of “Bonus & Reward”gift boxes?