2

I have two files. A.csv:

date,colA
2016-01-01,1
2016-01-02,4
2016-01-03,2

And B.csv:

date,colB
2016-01-01,2
2016-01-03,4

I'd like to combine these to get equivalent of:

date,colA,colB
2016-01-01,1,2
2016-01-02,4,0
2016-01-03,2,4

So I want to append colB to original data and all missing dates from B.csv should have value 0 added. The resulting data will be plotted with pgfplots.

Is this possible with LaTeX, pgfplots or pgfplotstable? We can safely assuma that A.csv has all the needed dates.

Or is it possible to add the missing rows first to B.csv before merging?

My current solution is to add the missing rows manually and then use \pgfplotstablecreatecol[copy column from table=... macro.

EDIT: I'm using Overleaf so the solution should work there aswell.

Paapaa
  • 642

2 Answers2

2

Well,if you have R and you admit knitr as LaTeX solution, then is easy:

mwe

\documentclass{article}
\usepackage{verbatim,xcolor}
\begin{filecontents*}{A.csv}
date,colA
2016-01-01,1
2016-01-02,4
2016-01-03,2
\end{filecontents*}
\begin{filecontents*}{B.csv}
date,colB
2016-01-01,2
2016-01-03,4
\end{filecontents*}
\begin{document}
<<echo=F,results='asis'>>=
A <- read.csv(file="A.csv")
B <- read.csv(file="B.csv")
C <- merge(A,B,by="date", all=T)
C[is.na(C)] <- 0
write.csv(C, file="C.csv",quote=F,row.names=F) 
@
Contents of \fcolorbox{gray}{gray!20}{\texttt{C.csv}}:~~~~~
\parbox{6cm}{\verbatiminput{C.csv}}
\end{document}
Fran
  • 80,769
  • Thanks! But I'm using Overleaf and I guess they don't have R/knitr support. I'd prefer pgfplots(table) solution. – Paapaa Jun 19 '16 at 16:09
  • @Paapaa In ShareLaTeX this code works out of the box but A.csv and B.csv should be made previously (i.e, without filecontents) – Fran Jun 19 '16 at 18:59
  • Ok, good to know! Will definitely try ShareLaTeX if there is no solution that works without R/knitr. – Paapaa Jun 19 '16 at 21:36
2

How about this solution, not requiring R? Uses only LaTeX and the datatool package

\documentclass{article}
\usepackage{filecontents}
\usepackage{datatool}

\begin{filecontents*}{A.csv}
date,colA
2016-01-01,1
2016-01-02,4
2016-01-03,2
\end{filecontents*}

\begin{filecontents*}{B.csv}
date,colB
2016-01-01,2
2016-01-03,4
\end{filecontents*}

% \DTLloaddb{A}{A.csv}
\DTLloaddb{B}{B.csv}
\DTLloaddb{C}{A.csv}

\DTLforeach{C}{\Date=date}{
    \DTLgetvalueforkey{\tmp}{colB}{B}{date}{\Date}
    \DTLappendtorow{colB}{\DTLifnull{\tmp}{0}{\tmp}}
}

\begin{document}

\DTLsetseparator{,}
\DTLsetdelimiter{"}
\DTLsavedb{C}{C.csv} % doesn't write if before \begin{document}

\DTLdisplaydb{C}

\end{document}
Michal Sudwoj
  • 326