Obtain unique values of multiple specific columns from a datatool database

Question

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

Answer 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:

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:

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:

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.