1

How can I get all the different values of a specific column in a datatool database and the corresponding value in another column? In the following example, I want to get as output:

A city of Xland in the database is Xcity
A city of Yland in the database is Ycity
A city of Zland in the database is Zcity

I know that I can get the list of unique cities as in the example below (and similarly the list of unique countries) but how can I combine them?

\documentclass{article}

\usepackage{etoolbox}
\usepackage{datatool}

\begin{filecontents}{test.csv}
Name,Town,Country,Age
Adam,Xcity,Xland,20
Berta,Ytown,Yland,30
Cesar,Ztington,Zland,40
Dora,Ztington,Zland,20
Emil,Ytown,Yland,30
Franz,Ytown,Yland,20
\end{filecontents}

\DTLloaddb{data}{test.csv}

\begin{document}

\newcommand*{\uniquetowns}{}
\newcommand*{\uniquecountries}{}

\DTLforeach*{data}{\Town=Town,\uniquecountries=Country}{%
  \expandafter\DTLifinlist\expandafter{\Town}{\uniquetowns}%
  {}% do nothing, already in list
  {% add to list
    \ifdefempty{\uniquetowns}%
    {\let\uniquetowns\Town}% first element of list
    {% append to list
      \eappto\uniquetowns{,\Town}%
    }%
  }%
}

List of unique towns: \uniquetowns.


\end{document}

Related question: Obtain all different values of a specific column in a datatool database

1 Answer 1

1

I probably don't quite understand what you want to achieve, but how about this?

\documentclass{article}
\usepackage{datatool}

\begin{filecontents}{test.csv}
Name,Town,Country,Age
Adam,Xcity,Xland,20
Berta,Ytown,Yland,30
Cesar,Ztington,Zland,40
Dora,Ztington,Zland,20
Emil,Ytown,Yland,30
Franz,Ytown,Yland,20
\end{filecontents}

\DTLloaddb{data}{test.csv}

\begin{document}

\newcommand*{\uniquetowns}{}
% \newcommand*{\uniquecountries}{}

\DTLforeach*{data}{\Town=Town, \Country=Country}{%
  \expandafter\DTLifinlist\expandafter{\Town}{\uniquetowns}%
  {}% do nothing, already in list
  {% add to list
    \ifdefempty{\uniquetowns}%
    {\let\uniquetowns\Town}% first element of list
    {% append to list
      \eappto\uniquetowns{,\Town}%
    }%
    A city of \Country{} in the database is \Town . 
  }%
}

\end{document}

This would output for each different town:

enter image description here

However, in your example the names of the towns and countries are chosen in such a way, that each town can only be in a specific country and each country only is related to one specific town. Do you need to handle cases where two towns with the same name can be in different countries?

If this is the case, you can join the country and the town name for each entry to make unique contry-town entries. This way, you are guaranteed that you will get every town in every country. (Note that I had to change some of the macros to be able to cope with the combined strings.)

\documentclass{article}
\usepackage{datatool}

\begin{filecontents}{test.csv}
Name,Town,Country,Age
Adam,Rome,Italy,20
Berta,Rome,U.S.,30
Cesar,Berlin,Germany,40
Dora,Munich,Germany,20
Emil,New York,U.S.,30
Franz,Milan,Italy,20
Gustav,Milan,Italy,20
Heinrich,Rome,Italy,20
\end{filecontents}

\DTLloaddb{data}{test.csv}

\begin{document}

\newcommand*{\uniquetowns}{}
%\newcommand*{\uniquecountries}{}

\noindent%
\DTLforeach*{data}{\Town=Town, \Country=Country}{%
  \DTLifSubString{\uniquetowns}{\Town.\Country}%
  {}% do nothing, already in list
  {% add to list
    \ifdefempty{\uniquetowns}%
    {% first element of list
        \eappto\uniquetowns{\Town.\Country}%
    }%
    {% append to list
        \eappto\uniquetowns{,\Town.\Country}%
    }%
    A city of \Country{} in the database is \Town . \\
  }%
}

\end{document}

Output would be:

enter image description here

As you can see, Rome in both, Italy and the U.S., is being considered and also Munich and Berlin, both in Germany. Still, the list is unique, since Rome (Italy) and Milan (Italy) are both only listed once.


Update

Since the OP stated that they wanted a result where all town belonging to one country are combined in one sentence, I tried again. For this to work, however, multiple instances of \DTLforeach are needed. So, it will take some time, if there is a lot of data to proceed.

\documentclass{article}
\usepackage{datatool}

\begin{filecontents}{test.csv}
Name,Town,Country,Age
Adam,Rome,Italy,20
Berta,Rome,U.S.,30
Cesar,Berlin,Germany,40
Dora,Munich,Germany,20
Emil,New York,U.S.,30
Franz,Milan,Italy,20
Gustav,Milan,Italy,20
Heinrich,Rome,Italy,20
\end{filecontents}

\DTLloaddb{data}{test.csv}

\DTLnewdb{countries}
\newcommand*{\uniquecountries}{}
\newcommand*{\uniquetowns}{}
\newcommand*{\countrysentence}{}

\begin{document}

% first, generate database with unique entries
\DTLforeach*{data}{\Country=Country}{%
  \DTLifSubString{\uniquecountries}{\Country}%
  {% do nothing, already in list
  }%
  {% add to list
    \DTLnewrow{countries}%
    {\dtlexpandnewvalue\DTLnewdbentry{countries}{Country}{\Country}}%
    \eappto\uniquecountries{\Country : }%
  }%
}%

\noindent%
\DTLforeach*{countries}{\CurrentCountry=Country}{%
  \renewcommand{\uniquetowns}{}%
  \renewcommand{\countrysentence}{}%
  \DTLforeach*[\DTLiseq{\CurrentCountry}{\Country}]{data}%
  {\Country=Country, \Town=Town}{%
    \DTLifSubString{\uniquetowns}{\Town}%
    {% do nothing, already in list
    }%
    {% add to list
      \eappto\uniquetowns{\Town : }%
      \ifdefempty{\countrysentence}%
      {% first element of list
        \eappto\countrysentence{The cities in \CurrentCountry{} are \Town}%
      }%
      {% append to list
        \eappto\countrysentence{, \Town}%
      }%
    }%
  }%
  \eappto\countrysentence{.}%
  \countrysentence \\
}

\end{document}

This outputs:

enter image description here

Maybe this can be achieved easier, but I cound not find any simpler solution. There are two problems that led to this specific set up: First, there is no way to filter only unique data other than via another macro that stores a list, and second, as far as I can tell, you can not easily read data stored in a macro to \DTLforeach. Therefore I needed to create a list stored in a macro plus a database, which seems a bit cumbersome. But maybe I am wrong here.

5
  • 1
    Fantastic. That is exactly what I needed, and yes, coincidentally the first answer was what I needed, but the second will generalise to future issues I might encounter. Thanks!
    – augusto
    Nov 17, 2021 at 14:08
  • Can this be tweaked to obtain all cities per country? Ie, Outout: The cities in Italy are Rome, Milan. The cities in the US are Rome, New York. The cities in Germany are Berlin, Munich.
    – augusto
    Nov 17, 2021 at 18:37
  • I guess, you would need to do this the other way around: First get the unique countries and then loop through the data again. Nov 17, 2021 at 21:24
  • That is my guess, how to do it scapes me though. Will post it as a separate question. Thanks
    – augusto
    Nov 18, 2021 at 19:09
  • @augusto I updated my answer and included a solution which I think is a bit complicated. But it was the only way it worked for me. Maybe someone else knows a simpler solution. Nov 18, 2021 at 20:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .