\chapter{Joining data sources}
\label{chap:join}

\section{Introduction}

Gretl provides two commands for adding data from file to an existing
dataset in the program's workspace, namely \texttt{append} and
\texttt{join}. The \texttt{append} command, which has been available
for a long time, is relatively simple and is described in the
\GCR. Here we focus on the \texttt{join} command, which is much more
flexible and sophisticated. This chapter gives an overview of the
functionality of \texttt{join} along with a detailed account of its
syntax and options. We provide several toy examples and discuss one
real-world case at length.

First, a note on terminology: in the following we use the terms 
``left-hand'' and ``inner'' to refer to the dataset that is already in
memory, and the terms ``right-hand'' and ``outer'' to refer to the
dataset in the file from which additional data are to be drawn. 

Two main features of \texttt{join} are worth emphasizing at the
outset:
\begin{itemize}
\item ``Key'' variables can be used to match specific observations
  (rows) in the inner and outer datasets, and this match need not be
  1 to 1.
\item A row filter may be applied to screen out unwanted observations
  in the outer dataset.
\end{itemize}

As will be explained below, these features support rather complex
concatenation and manipulation of data from different sources.

A further aspect of \texttt{join} should be noted---one that makes
this command particularly useful when dealing with very large data
files.  That is, when gretl executes a join operation it does not, in
general, read into memory the entire content of the right-hand side
dataset.  Only those columns that are actually needed for the
operation are read in full. This makes \texttt{join} faster and less
demanding of computer memory than the methods available in most other
software. On the other hand, gretl's asymmetrical treatment of the
``inner'' and ``outer'' datasets in \texttt{join} may require some
getting used to, for users of other packages.

\section{Basic syntax}
\label{sec:join-syntax}

The minimal invocation of \texttt{join} is

\qquad \texttt{join} \textsl{filename} \textsl{varname}

where \textsl{filename} is the name of a data file and
\textsl{varname} is the name of a series to be imported. Only two
sorts of data file are supported at present: delimited text files
(where the delimiter may be comma, space, tab or semicolon) and
``native'' gretl data files (\texttt{gdt} or \texttt{gdtb}).  A series
named \textsl{varname} may already be present in the left-hand
dataset, but that is not required. The series to be imported may be
numerical or string-valued. For most of the discussion below we assume
that just a single series is imported by each \texttt{join} command,
but see section~\ref{sec:join-multi} for an account of multiple
imports.

The effect of the minimal version of \texttt{join} is this: gretl
looks for a data column labeled \textsl{varname} in the specified
file; if such a column is found and the number of observations on the
right matches the number of observations in the current sample range
on the left, then the values from the right are copied into the
relevant range of observations on the left. If \textsl{varname} does
not already exist on the left, any observations outside of the current
sample are set to \texttt{NA}; if it exists already then observations
outside of the current sample are left unchanged.

The case where you want to rename a series on import is handled by the
\option{data} option. This option has one required argument, the name
by which the series is known on the right. At this point we need to
explain something about right-hand variable names (column
headings). 

\subsection{Right-hand names}
\label{subsec:rhnames}

We accept on input arbitrary column heading strings, but if these
strings do not qualify as valid gretl identifiers they are
automatically converted, and in the context of \texttt{join} you must
use the converted names. A gretl identifier must start with a letter,
contain nothing but (ASCII) letters, digits and the underscore
character, and must not exceed 31 characters. The rules used in name
conversion are:

\begin{enumerate}
\item Skip any leading non-letters.
\item Until the 31-character is reached or the input is exhausted:
  transcribe ``legal'' characters; skip ``illegal'' characters apart
  from spaces; and replace one or more consecutive spaces with an
  underscore, unless the last character transcribed is an underscore
  in which case space is skipped.
\end{enumerate}

In the unlikely event that this policy yields an empty string, we
replace the original with \texttt{col}\textsl{n}, where \textsl{n} is
replaced by the 1-based index of the column in question among those
used in the join operation. If you are in doubt regarding the
converted name of a given column, the function \texttt{fixname()} can
be used as a check: it takes the original string as an argument and
returns the converted name. Examples:

\begin{code}
? eval fixname("valid_identifier")
valid_identifier
? eval fixname("12. Some name")
Some_name
\end{code}

Returning to the use of the \option{data} option, suppose we have a
column headed \verb|"12. Some name"| on the right and wish to import
it as \texttt{x}. After figuring how the right-hand name converts, we
can do
%
\begin{code}
join foo.csv x --data="Some_name"
\end{code}

\subsection{No right-hand names?}
\label{subsec:no-rhnames}

Some data files have no column headings; they jump straight into the
data (and you need to determine from accompanying documentation what
the columns represent). Since gretl expects column headings, you have
to take steps to get the importation right. It is generally a good
idea to insert a suitable header row into the data file. However, if
for some reason that's not practical, you should give the
\option{no-header} option, in which case gretl will name the columns
on the right as \texttt{col1}, \texttt{col2} and so on. If you do not
do either of these things you will likely lose the first row of data,
since gretl will attempt to make variable names out of it, as
described above.

\section{Filtering}
\label{sec:join-filter}

Rows from the \textit{outer} dataset can be filtered using the
\option{filter} option. The required parameter for this option is a
Boolean condition, that is, an expression which evaluates to non-zero
(true, include the row) or zero (false, skip the row) for each of the
outer rows. The filter expression may include any of the following
terms: up to three ``right-hand'' series (under their converted names as
explained above); scalar or string variables defined ``on the left'';
any of the operators and functions available in gretl (including
user-defined functions); and numeric or string constants.

Here are a few simple examples of potentially valid filter options
(assuming that the specified right-hand side columns are found):

\begin{code}
# 1. relationship between two right-hand variables
--filter="x15<=x17"

# 2. comparison of right-hand variable with constant
--filter="nkids>2"

# 3. comparison of string-valued right-hand variable with string constant
--filter="SEX==\"F\""

# 4. filter on valid values of a right-hand variable
--filter=!missing(income)

# 5. compound condition
--filter="x < 100 && (x > 0 || y > 0)"
\end{code}

Note that if you are comparing against a string constant (as in
example 3 above) it is necessary to put the string in ``escaped''
double-quotes (each double-quote preceded by a backslash) so the
interpreter knows that \texttt{F} is not supposed to be the name of a
variable.

It is safest to enclose the whole filter expression in double quotes,
however this is not strictly required unless the expression contains
spaces or the equals sign.

In general, an error is flagged if a missing value is encountered in a
series referenced in a filter expression, because the condition then
becomes indeterminate. In example 2 above, if the \texttt{nkids} value
is \texttt{NA} on any given row we are not in a position to evaluate the
condition \texttt{nkids>2}. However, you can use the \texttt{missing()}
function---or \texttt{ok()}, which is a shorthand for
\texttt{!missing()}---if you need a filter that keys off the missing or
non-missing status of a variable.

\section{Matching with keys}
\label{sec:join-keys}

Things get interesting when we come to key-matching. The purpose of
this facility is perhaps best introduced by example.  Suppose that (as
with many survey and census-based datasets) we have a dataset that is
composed of two or more related files, each having a different unit of
observation; for example we have a ``persons'' data file and a
``households'' data file. Table~\ref{tab:join-csv} shows a simple,
artificial case. The file \texttt{people.csv} contains a unique
identifier for the individuals, \texttt{pid}. The households file,
\texttt{hholds.csv}, contains the unique household identifier
\texttt{hid}, which is also present in the persons file.

As a first example of \texttt{join} with keys, let's add the
household-level variable \texttt{xh} to the persons dataset:
%
\begin{code}
open people.csv --quiet
join hholds.csv xh --ikey=hid
print --byobs
\end{code}

The basic key option is named \texttt{ikey}; this indicates ``inner
key'', that is, the key variable found in the left-hand or inner
dataset. By default it is assumed that the right-hand dataset contains
a column of the same name, though as we'll see below that assumption
can be overridden. The \texttt{join} command above says, find a series
named \texttt{xh} in the right-hand dataset and add it to the
left-hand one, using the values of \texttt{hid} to match rows.
Looking at the data in Table~\ref{tab:join-csv} we can see how this
should work. Persons 1 and 2 are both members of household 1, so they
should both get values of 1 for \texttt{xh}; persons 3 and 4 are
members of household 2, so that \texttt{xh} = 4; and so on. Note that
the order in which the key values occur on the right-hand side does
not matter.  The gretl output from the \texttt{print} command is shown
in the lower panel of Table~\ref{tab:join-csv}.

\begin{table}[thbp]
\begin{center}
\setlength{\tabcolsep}{4em}
\begin{tabular}{ll}
\texttt{people.csv} & \texttt{hholds.csv} \\[6pt]
\texttt{pid,hid,gender,age,xp} & \texttt{hid,country,xh} \\
\texttt{1,1,M,50,1} & \texttt{1,US,1} \\
\texttt{2,1,F,40,2} & \texttt{6,IT,12} \\
\texttt{3,2,M,30,3} & \texttt{3,UK,6} \\
\texttt{4,2,F,25,2} & \texttt{4,IT,8} \\
\texttt{5,3,M,40,3} & \texttt{2,US,4} \\
\texttt{6,4,F,35,4} & \texttt{5,IT,10} \\
\texttt{7,4,M,70,3} \\
\texttt{8,4,F,60,3} \\
\texttt{9,5,F,20,4} \\
\texttt{10,6,M,40,4}
\end{tabular}

\vspace{1em}

\begin{tabular}{rrr}
  \texttt{pid}  & \texttt{hid}   &  \texttt{xh} \\[6pt]
    \texttt{1}  &  \texttt{1}    &   \texttt{1} \\
    \texttt{2}  &  \texttt{1}    &   \texttt{1} \\
    \texttt{3}  &  \texttt{2}    &   \texttt{4} \\
    \texttt{4}  &  \texttt{2}    &   \texttt{4} \\
    \texttt{5}  &  \texttt{3}    &   \texttt{6} \\
    \texttt{6}  &  \texttt{4}    &   \texttt{8} \\
    \texttt{7}  &  \texttt{4}    &   \texttt{8} \\
    \texttt{8}  &  \texttt{4}    &   \texttt{8} \\
    \texttt{9}  &  \texttt{5}    &  \texttt{10} \\
   \texttt{10}  &  \texttt{6}    &  \texttt{12}
\end{tabular}
\caption{Two linked CSV data files, and the effect of a \texttt{join}}
\label{tab:join-csv}
\end{center}
\end{table}

Note that key variables are treated conceptually as integers. If a
specified key contains fractional values these are truncated.

Two extensions of the basic key mechanism are available.
\begin{itemize}
\item If the outer dataset contains a relevant key variable but it
  goes under a different name from the inner key, you can use the
  \verb|--okey| option to specify the outer key. (As with other
  right-hand names, this does not have to be a valid gretl
  identifier.) So, for example, if \texttt{hholds.csv} contained the
  \texttt{hid} information, but under the name \texttt{HHOLD}, the
  \texttt{join} command above could be modified as
  \begin{code}
  join hholds.csv xh --ikey=hid --okey=HHOLD
  \end{code}

\item If a single key is not sufficient to generate the matches you
  want, you can specify a double key in the form of two series names
  separated by a comma; in this case the importation of data is
  restricted to those rows on which both keys match. The syntax here
  is, for example
  \begin{code}
  join foo.csv x --ikey=key1,key2
  \end{code}
  Again, the \verb|--okey| option may be used if the corresponding
  right-hand columns are named differently. The same number of keys 
  must be given on the left and the right, but when a double key is
  used and only one of the key names differs on the right, the name
  that is in common may be omitted (although the comma separator must
  be retained). For example, the second of the following lines is
  acceptable shorthand for the first:
  \begin{code}
  join foo.csv x --ikey=key1,Lkey2 --okey=key1,Rkey2
  join foo.csv x --ikey=key1,Lkey2 --okey=,Rkey2
  \end{code}
\end{itemize}

\subsection{The number of key-matches}

The example shown in Table~\ref{tab:join-csv} is an instance of a 1 to
1 match: applying the matching criterion produces exactly one value of
the variable \texttt{xh} corresponding to each row of the inner
dataset. Three other possibilities arise:
\begin{itemize}
\item Some rows on the left have multiple matches on the right (``1 to
  $n$ matching'').  
\item Some rows on the right have multiple matches on the left (``$n$
  to 1 matching'').
\item Some rows in the inner dataset have no match on the right.
  
\end{itemize}
The 1-to-$n$ case is addressed in detail in the next section; here we
discuss the others.

The $n$ to 1 case is straightforward. If a particular key value
(or combination of key values) occurs at each of $n > 1$
observations on the left but at a single observation on the right,
then the right-hand value is entered at each of the matching slots on
the left.

Treatment of the case where there's \textit{no} match on the right
depends on whether the join operation is adding a new series to the
inner dataset or modifying an existing one. When adding a new series,
unmatched rows automatically get \texttt{NA} for the imported
data. However, if join is pulling in values for a series already present
on the left only matched rows will be updated. In other words we do
\textit{not} overwite an existing value on the left with \texttt{NA}
when there's no match on the right.

These default policies may not produce the desired results in every case
but gretl provides the means to modify the effect if need be.  We will
illustrate with two scenarios.

First consider adding a new series recording ``number of hours worked''
when the inner dataset contains individuals and the outer file contains
data on jobs. If an individual does not appear in the jobs file (no
match on the right) the default policy means that her hours worked will
be recorded as \texttt{NA}. If in context you wish to set hours worked
to zero rather than \texttt{NA} for such individuals, gretl's
\texttt{misszero()} function can be used to turn \texttt{NA} into 0 in
the imported series.

Second, consider updating an existing series via join when the outer
file is presumed to contain all available updated values, such that ``no
match'' should be taken as an implicit \texttt{NA}. In that case we want
the (presumably out-of-date) values on any unmatched rows to be
overwritten with \texttt{NA}. Let the series in question be called
\texttt{x} (both on the left and the right) and let the common key be
called \texttt{pid}. The solution is then
\begin{code}
join update.csv tmpvar --data=x --ikey=pid
x = tmpvar
\end{code}
where \texttt{tmpvar} does not already exist in the inner dataset. As a
new series, \texttt{tmpvar} will get \texttt{NA} for all unmatched rows;
we then transcribe its values into \texttt{x}.  In a more complicated
case one might use the \texttt{smpl} command to limit the sample range
before assigning \texttt{tmpvar} to \texttt{x}, or use the conditional
assignment operator \texttt{?:}.

One further point: given some missing values in an imported series you
may want to know whether (a) the \texttt{NA}s were explicitly
represented in the outer data file or (b) they arose due to ``no
match''. You can find this out by using a method described in the
following section, namely the \texttt{count} variant of the
aggregation option: this will give you a series with 0 values for all
and only unmatched rows.

\section{Aggregation}
\label{sec:join-aggr}

In the case of 1 to $n$ matching of rows ($n > 1$) the user must
specify an ``aggregation method''; that is, a method for mapping from
$n$ rows down to one. This is handled by the \verb|--aggr| option
which requires a single argument from the following list:

\begin{center}
\begin{tabular}{ll}
\textit{Code} & \textit{Value returned} \\[6pt]
\texttt{count} & count of matches \\
\texttt{avg} & mean of matching values \\
\texttt{sum} & sum of matching values \\
\texttt{min} & minimum of matching values \\
\texttt{max} & maximum of matching values \\
\texttt{seq:}$i$ & the $i^{\rm th}$ matching 
  value (e.g.\ \texttt{seq:2}) \\
\texttt{min(}\textsl{aux}\texttt{)} & 
  minimum of matching values of auxiliary variable \\
\texttt{max(}\textsl{aux}\texttt{)} & 
  maximum of matching values of auxiliary variable\\
\end{tabular}
\end{center}

Note that the \texttt{count} aggregation method is special, in that
there is no need for a ``data series'' on the right; the imported
series is simply a function of the specified key(s). All the other
methods require that ``actual data'' are found on the right.  Also
note that when \texttt{count} is used, the value returned when no
match is found is (as one might expect) zero rather than \texttt{NA}.

The basic use of the \texttt{seq} method is shown above: following the
colon you give a positive integer representing the (1-based) position
of the observation in the sequence of matched rows. Alternatively, a
negative integer can be used to count down from the last match
(\texttt{seq:-1} selects the last match, \texttt{seq:-2} the
second-last match, and so on). If the specified sequence number is out
of bounds for a given observation this method returns \texttt{NA}.

Referring again to the data in Table~\ref{tab:join-csv}, suppose we
want to import data from the persons file into a dataset established
at household level.  Here's an example where we use the individual
\texttt{age} data from \texttt{people.csv} to add the average and
minimum age of household members.
\begin{code}
open hholds.csv --quiet
join people.csv avgage --ikey=hid --data=age --aggr=avg
join people.csv minage --ikey=hid --data=age --aggr=min
\end{code}

Here's a further example where we add to the household data the sum of
the personal data \texttt{xp}, with the twist that we apply filters to
get the sum specifically for household members under the age of 40,
and for women.
\begin{code}
open hholds.csv --quiet
join people.csv young_xp --ikey=hid --filter="age<40" --data=xp --aggr=sum
join people.csv female_xp --ikey=hid --filter="gender==\"F\"" --data=xp --aggr=sum
\end{code}

The possibility of using an auxiliary variable with the \texttt{min}
and \texttt{max} modes of aggregation gives extra flexibility. For
example, suppose we want for each household the income of its oldest
member:
\begin{code}
open hholds.csv --quiet
join people.csv oldest_xp --ikey=hid --data=xp --aggr=max(age)
\end{code}


\section{String-valued key variables}
\label{sec:join-strings}

The examples above use numerical variables (household and individual
ID numbers) in the matching process. It is also possible to use
string-valued variables, in which case a match means that the string
values of the key variables compare equal (with case sensitivity). 
When using double keys, you can mix numerical and string keys, but
naturally you cannot mix a string variable on the left (via
\texttt{ikey}) with a numerical one on the right (via \texttt{okey}),
or vice versa.

Here's a simple example. Suppose that alongside \texttt{hholds.csv} we
have a file \texttt{countries.csv} with the following content:
\begin{code}
 country,GDP
 UK,100
 US,500
 IT,150
 FR,180
\end{code}

The variable \texttt{country}, which is also found in
\texttt{hholds.csv}, is string-valued. We can pull the GDP of the
country in which the household resides into our households dataset
with
\begin{code}
open hholds.csv -q
join countries.csv GDP --ikey=country
\end{code}
which gives
\begin{code}
           hid      country          GDP

1            1            1          500
2            6            2          150
3            3            3          100
4            4            2          150
5            2            1          500
6            5            2          150
\end{code}

\section{Importing multiple series}
\label{sec:join-multi}

The examples given so far have been limited in one respect. While
several columns in the outer data file may be referenced (as keys, or
in filtering or aggregation) only one column has actually provided
data---and correspondingly only one series in the inner dataset has
been created or modified---per invocation of \texttt{join}. However,
\texttt{join} can handle the importation of several series at
once. This section gives an account of the required syntax along with
certain restrictions that apply to the multiple-import case.

There are two ways to specify more than one series for importation:
\begin{enumerate}
\item The \textsl{varname} field in the command can take the form of
  a space-separated list of names rather than a single name.
\item Alternatively, you can give the name of an array of strings
  in place of \textsl{varname}: the elements of this array should be
  the names of the series to import.
\end{enumerate}

Here are the limitations:
\begin{enumerate}
\item The \verb|--data| option, which permits the renaming of a series
  on import, is not available. When importing multiple series you
  are obliged to accept their ``outer'' names, fixed up as described
  in section~\ref{subsec:rhnames}.
\item While the other \texttt{join} options are available, they
  necessarily apply uniformly to all the series imported via a given
  command. This means that if you want to import several series but
  using different keys, filters or aggregation methods you must use a
  sequence of commands.
\end{enumerate}

Here are a couple of examples of multiple imports.
\begin{code}
# open base datafile containing keys
open PUMSdata.gdt

# join using a list of import names
join ss13pnc.csv SCHL WAGP WKHP --ikey=SERIALNO,SPORDER

# using a strings array: may be worthwhile if the array
# will be used for more than one purpose
strings S = defarray("SCHL", "WAGP", "WKHP")
join ss13pnc.csv S --ikey=SERIALNO,SPORDER
\end{code}

\section{A real-world case}
\label{sec:join-SHIW}

For a real use-case for \texttt{join} with cross-sectional data, we
turn to the Bank of Italy's \textit{Survey on Household Income and
  Wealth} (SHIW).\footnote{Details of the survey can be found at
  \url{http://www.bancaditalia.it/statistiche/indcamp/bilfait/dismicro}.
  The ASCII (CSV) data files for the 2010 survey are available at
  \url{http://www.bancaditalia.it/statistiche/indcamp/bilfait/dismicro/annuale/ascii/ind10_ascii.zip}.}
In ASCII form the 2010 survey results comprise 47\,MB of data in 29
files. In this exercise we will draw on five of the SHIW files to
construct a replica of the dataset used in Thomas Mroz's famous paper
\citep{mroz87} on women's labor force participation, which contains
data on married women between the age of 30 and 60 along with certain
characteristics of their households and husbands.

Our general strategy is as follows: we create a ``core'' dataset by
opening the file \texttt{carcom10.csv}, which contains basic data on
the individuals. After dropping unwanted individuals (all but married
women), we use the resulting dataset as a base for pulling in further
data via the \texttt{join} command.

The complete script to do the job is given in the Appendix to this
chapter; here we walk through the script with comments interspersed.
We assume that all the relevant files from the Bank of Italy survey
are contained in a subdirectory called \texttt{SHIW}.

Starting with \texttt{carcom10.csv}, we use the \verb|--cols| option
to the \texttt{open} command to import specific series, namely
\texttt{NQUEST} (household ID number), \texttt{NORD} (sequence number
for individuals within each household), \texttt{SEX} (male = 1, female
= 2), \texttt{PARENT} (status in household: 1 = head of household, 2 =
spouse of head, etc.), \texttt{STACIV} (marital status: married = 1),
\texttt{STUDIO} (educational level, coded from 1 to 8),
\texttt{ETA} (age in years) and \texttt{ACOM4C} (size of town).
%
\begin{code}
open SHIW/carcom10.csv --cols=1,2,3,4,9,10,29,41
\end{code}
%
We then restrict the sample to married women from 30 to 60 years of
age, and additionally restrict the sample of women to those who are
either heads of households or spouses of the head.
%
\begin{code}
smpl SEX==2 && ETA>=30 && ETA<=60 && STACIV==1 --restrict
smpl PARENT<3  --restrict
\end{code}
%
For compatibility with the Mroz dataset as presented in the gretl
data file \texttt{mroz87.gdt}, we rename the age and education
variables as \texttt{WA} and \texttt{WE} respectively, we compute the
\texttt{CIT} dummy and finally we
store the reduced base dataset in gretl format.
%
\begin{code}
rename ETA WA
rename STUDIO WE
series CIT = (ACOM4C > 2)

store mroz_rep.gdt
\end{code}

The next step will be to get data on working hours from the jobs file
\texttt{allb1.csv}. There's a complication here. We need the total
hours worked over the course of the year (for both the women and their
husbands). This is not available as such, but the variables
\texttt{ORETOT} and \texttt{MESILAV} give, respectively, average hours
worked per week and the number of months worked in 2010, each on a
per-job basis. If each person held at most one job over the year we
could compute his or her annual hours as
\begin{code}
HRS = ORETOT * 52 * MESILAV/12
\end{code}
However, some people had more than one job, and in this case what we
want is the sum of annual hours across their jobs.  We could use
\texttt{join} with the \texttt{seq} aggregation method to construct
this sum, but it is probably more straightforward to read the
\texttt{allb1} data, compute the \texttt{HRS} values per job as shown
above, and save the results to a temporary CSV file.
%
\begin{code}
open SHIW/allb1.csv --cols=1,2,8,11 --quiet
series HRS = misszero(ORETOT) * 52 * misszero(MESILAV)/12
store HRS.csv NQUEST NORD HRS
\end{code}

Now we can reopen the base dataset and join the hours variable from
\texttt{HRS.csv}.  Note that we need a double key here: the women are
uniquely identified by the combination of \texttt{NQUEST} and
\texttt{NORD}. We don't need an \texttt{okey} specification since
these keys go under the same names in the right-hand file. We define
labor force participation, \texttt{LFP}, based on hours.
%
\begin{code}
open mroz_rep.gdt
join HRS.csv WHRS --ikey=NQUEST,NORD --data=HRS --aggr=sum
WHRS = misszero(WHRS)
LFP = WHRS > 0
\end{code}
%
For reference, here's how we could have used \texttt{seq} to avoid
writing a temporary file:
%
\begin{code}
join SHIW/allb1.csv njobs --ikey=NQUEST,NORD --data=ORETOT --aggr=count
series WHRS = 0
loop i=1..max(njobs)
  join SHIW/allb1.csv htmp --ikey=NQUEST,NORD --data=ORETOT --aggr="seq:$i"
  join SHIW/allb1.csv mtmp --ikey=NQUEST,NORD --data=MESILAV --aggr="seq:$i"
  WHRS += misszero(htmp) * 52 * misszero(mtmp)/12 
endloop
\end{code}

To generate the work experience variable, \texttt{AX}, we use the file
\texttt{lavoro.csv}: this contains a variable named \texttt{ETALAV}
which records the age at which the person first started work.
%
\begin{code}
join SHIW/lavoro.csv ETALAV --ikey=NQUEST,NORD
series AX = misszero(WA - ETALAV)
\end{code}
%
We compute the woman's hourly wage, \texttt{WW}, as the ratio of total
employment income to annual working hours.  This requires drawing the
series \texttt{YL} (payroll income) and \texttt{YM} (net
self-employment income) from the persons file \texttt{rper10.csv}.
%
\begin{code}
join SHIW/rper10.csv YL YM --ikey=NQUEST,NORD --aggr=sum
series WW = LFP ? (YL + YM)/WHRS : 0
\end{code}
%
The family's net disposable income is available as \texttt{Y} in the file
\texttt{rfam10.csv}; we import this as \texttt{FAMINC}.
%
\begin{code}
join SHIW/rfam10.csv FAMINC --ikey=NQUEST --data=Y
\end{code}
%
Data on number of children are now obtained by applying the
\texttt{count} method. For the Mroz replication we want the number of
children under the age of 6, and also the number aged 6 to 18.
%
\begin{code}
join SHIW/carcom10.csv KIDS --ikey=NQUEST --aggr=count --filter="ETA<=18"
join SHIW/carcom10.csv KL6 --ikey=NQUEST --aggr=count --filter=ETA<6
series K618 = KIDS - KL6
\end{code}
%
We want to add data on the women's husbands, but how do we find them?
To do this we create an additional inner key which we'll call
\verb|H_ID| (husband ID), by sub-sampling in turn on the observations
falling into each of two classes: (a) those where the woman is
recorded as head of household and (b) those where the husband has that
status. In each case we want the individual ID (\texttt{NORD}) of the
household member whose status is complementary to that of the woman in
question. So for case (a) we subsample using \texttt{PARENT==1} (head
of household) and filter the join using \texttt{PARENT==2} (spouse of
head); in case (b) we do the converse. We thus construct \verb|H_ID|
piece-wise.
%
\begin{code}
# for women who are household heads
smpl PARENT==1 --restrict --replace
join SHIW/carcom10.csv H_ID --ikey=NQUEST --data=NORD --filter="PARENT==2"
# for women who are not household heads
smpl PARENT==2 --restrict --replace
join SHIW/carcom10.csv H_ID --ikey=NQUEST --data=NORD --filter="PARENT==1"
smpl full
\end{code}
%
Now we can use our new inner key to retrieve the husbands' data,
matching \verb|H_ID| on the left with \texttt{NORD} on the right
within each household.
%
\begin{code}
join SHIW/carcom10.csv HA --ikey=NQUEST,H_ID --okey=NQUEST,NORD --data=ETA
join SHIW/carcom10.csv HE --ikey=NQUEST,H_ID --okey=NQUEST,NORD --data=STUDIO
join HRS.csv HHRS --ikey=NQUEST,H_ID --okey=NQUEST,NORD --data=HRS --aggr=sum
HHRS = misszero(HHRS)
\end{code}
%
The remainder of the script is straightforward and does not require
discussion here: we recode the education variables for compatibility;
delete some intermediate series that are not needed any more; add
informative labels; and save the final product. See the Appendix for
details.

To compare the results from this dataset with those from the earlier
US data used by Mroz, one can copy the input file \texttt{heckit.inp}
(supplied with the gretl package) and substitute \verb|mroz_rep.gdt|
for \texttt{mroz87.gdt}. It turns out that the results are
qualitatively very similar.

\section{The representation of dates}
\label{sec:join-isodates}

Up to this point all the data we have considered have been
cross-sectional. In the following sections we discuss data that have a
time dimension, and before proceeding it may be useful to say
something about the representation of dates. Gretl takes the ISO 8601
standard as its reference point but provides mean of converting dates
provided in other formats; it also offers a set of calendrical
functions for manipulating dates (\texttt{isodate}, \texttt{isoconv},
\texttt{epochday} and others).

ISO 8601 recognizes two formats for daily dates, ``extended'' and
``basic''. In both formats dates are given as 4-digit year, 2-digit
month and 2-digit day, in that order. In extended format a dash is
inserted between the fields---as in \texttt{2013-10-21} or more
generally \texttt{YYYY-MM-DD}---while in basic format the fields are
run together (\texttt{YYYYMMDD}). Extended format is more easily
parsed by human readers while basic format is more suitable for
computer processing, since one can apply ordinary arithmetic to
compare dates as equal, earlier or later.  The standard also
recognizes \texttt{YYYY-MM} as representing year and month, e.g.\
\texttt{2010-11} for November 2010,\footnote{The form \texttt{YYYYMM}
  is \textit{not} recognized for year and month.} as well as
a plain four-digit number for year alone.

One problem for economists is that the ``quarter'' is not a period
covered by ISO 8601. This could be presented by \texttt{YYYY-Q} (with
only one digit following the dash) but in gretl output we in fact use
a colon, as in \texttt{2013:2} for the second quarter of 2013. (For
printed output of months gretl also uses a colon, as in
\texttt{2013:06}. A difficulty with following ISO here is that in a
statistical context a string such as \texttt{1980-10} may look more
like a subtraction than a date.)  Anyway, at present we are more
interested in the parsing of dates on input rather than in what gretl
prints. And in that context note that ``excess precision'' is
acceptable: a month may be represented by its first day (e.g.\
\texttt{2005-05-01} for May, 2005), and a quarter may be represented
by its first month and day (\texttt{2005-07-01} for the third quarter
of 2005).

Some additional points regarding dates will be taken up as they become
relevant in practical cases of joining data.

\section{Time-series data}
\label{sec:join-timeser}

Suppose our left-hand dataset is recognized by gretl as time series
with a supported frequency (annual, quarterly, monthly, weekly, daily
or hourly). This will be the case if the original data were read from
a file that contained suitable time or date information, or if a
time-series interpretation has been imposed using either the
\texttt{setobs} command or its GUI equivalent.  Then---apart, perhaps,
from some very special cases---joining additional data is bound to
involve matching observations by time-period. In this case, contrary
to the cross-sectional case, the inner dataset has a natural ordering
of which gretl is aware; hence, no ``inner key'' is required.

If, in addition, the file from data which are to be joined is in
native gretl format and contains time-series information, keys are not
needed at all. Three cases can arise: the frequency of the outer
dataset may be the same, lower or higher than that of the inner
dataset. In the first two cases \texttt{join} should work without any
special apparatus; lower-frequency values will be repeated for each
high-frequency period. In the third case, however, an aggregation
method must be specified: gretl needs to know how to map
higher-frequency data into the existing dataset (by averaging,
summing, or whatever).

If the outer data file is \textit{not} in native gretl format we need
a means of identifying the period of each observation on the right, an
outer key which we'll call a ``time key''.  The \texttt{join} command
provides a simple (but limited) default for extracting period
information from the outer data file, plus an option that can be used
if the default is not applicable, as follows.
\begin{itemize}
\item The default assumptions are: (1) the time key appears in the
  first column; (2) the heading of this column is either left blank or
  is one of \texttt{obs}, \texttt{date}, \texttt{year},
  \texttt{period}, \texttt{observation}, or \verb|observation_date|
  (on a case-insensitive comparison); and (3) the time format conforms
  to ISO 8601 where applicable (``extended'' daily date format
  \texttt{YYYY-MM-DD}, monthly format \texttt{YYYY-MM}, or annual
  format \texttt{YYYY}).
\item If dates do not appear in the first column of the outer file, or
  if the column heading or format is not as just described, the
  \option{tkey} option can be used to indicate which column should be
  used and/or what format should be assumed.
\end{itemize}

\subsection{Setting the time-key column and/or format}

The \option{tkey} option requires a parameter holding the name of the
column in which the time key is located and/or a string specifying the
format in which dates/times are written in the time-key column. This
parameter should be enclosed in double-quotes. If both elements are
present they should be separated by a comma; if only a format is given
it should be preceded by a comma. Some examples:

\begin{code}
--tkey="Period,%m/%d/%Y"
--tkey="Period"
--tkey="obsperiod"
--tkey=",%Ym%m"
\end{code}

The first of these applies if \texttt{Period} is not the first column
on the right, and dates are given in the US format of month, day,
year, separated by slashes. The second implies that although
\texttt{Period} is not the first column, the date format is ISO 8601.
The third again implies that the date format is OK; here the name is
required even if \texttt{obsperiod} is the first column since this
heading is not one recognized by gretl's heuristic. The last example
implies that dates are in the first column (with one of the recognized
headings), but are given in the non-standard format year,
``\texttt{m}'', month.

The date format string should be composed using the codes employed by
the POSIX function \texttt{strptime}; Table \ref{tab:join-datefmt}
contains a list of the most relevant codes.\footnote{The
  \texttt{\%q} code for quarter is not present in \texttt{strptime};
  it is added for use with \texttt{join} since quarterly data are
  common in macroeconomics.}

\begin{table}[htbp]
  \centering
  \begin{tabular}{rp{0.7\textwidth}}
    \textbf{Code} & \textbf{Meaning} \\
    \hline
    \verb|%%| & The \% character. \\
    \verb|%b| & The month name according to the current locale,
    either abbreviated or in full.\\
    \verb|%C| & The century number (0--99).\\
    \verb|%d| & The day of month (1--31). \\
    \verb|%D| & Equivalent to \verb|%m/%d/%y|.  (This is the American
    style date, very  confusing  to  non-Americans, especially
    since \verb|%d/%m/%y| is widely used in Europe.  The 
    ISO 8601 standard format is \verb|%Y-%m-%d|.) \\
    \verb|%H| & The hour (0--23).\\
    \verb|%j| & The day number in the year (1--366).\\
    \verb|%m| & The month number (1--12).\\
    \verb|%n| & Arbitrary whitespace.\\
    \verb|%q| & The quarter (1--4).\\
    \verb|%w| & The weekday number (0--6) with Sunday = 0.\\
    \verb|%y| & The year within century (0--99).  When a century is
    not otherwise specified, values in  the  range  69--99  refer
    to  years  in  the  twentieth  century (1969--1999);  values
    in the range 00--68 refer to years in the twenty-first century (2000--2068).\\
    \verb|%Y| &  The year, including century (for example, 1991).\\
    \hline
  \end{tabular}
  \caption{Date format codes}
  \label{tab:join-datefmt}
\end{table}

\subsection{Example: daily stock prices}

We show below the first few lines of a file named \texttt{IBM.csv}
containing stock-price data for IBM corporation.

\begin{code}
Date,Open,High,Low,Close,Volume,Adj Close
2013-08-02,195.50,195.50,193.22,195.16,3861000,195.16
2013-08-01,196.65,197.17,195.41,195.81,2856900,195.81
2013-07-31,194.49,196.91,194.49,195.04,3810000,195.04
\end{code}

Note that the data are in reverse time-series order---that won't
matter to \texttt{join}, the data can appear in any order. Also note
that the first column is headed \texttt{Date} and holds daily dates as
ISO 8601 extended. That means we can pull the data into gretl very
easily. In the following fragment we create a suitably dimensioned
empty daily dataset then rely on the default behavior of \texttt{join}
with time-series data to import the closing stock price.

\begin{code}
nulldata 500
setobs 5 2012-01-01
join IBM.csv Close
\end{code}

To make explicit what we're doing, we could accomplish exactly the
same using the \option{tkey} option:

\begin{code}
join IBM.csv Close --tkey="Date,%Y-%m-%d"
\end{code}

\subsection{Example: OECD quarterly data}

Table~\ref{tab:oecd-gdp} shows an excerpt from a CSV file provided
by the OECD statistical site (\url{stat.oecd.org}) in response to a
request for GDP at constant prices for several
countries.\footnote{Retrieved 2013-08-05. The OECD files in fact
  contain two leading columns with very long labels; these are irrelevant
  to the present example and can be omitted without altering the
  sample script.}

\begin{table}[htbp]
\begin{code}
Frequency,Period,Country,Value,Flags
"Quarterly","Q1-1960","France",463876.148126845,E
"Quarterly","Q1-1960","Germany",768802.119278467,E
"Quarterly","Q1-1960","Italy",414629.791450547,E
"Quarterly","Q1-1960","United Kingdom",578437.090291889,E
"Quarterly","Q2-1960","France",465618.977328614,E
"Quarterly","Q2-1960","Germany",782484.138122549,E
"Quarterly","Q2-1960","Italy",420714.910290157,E
"Quarterly","Q2-1960","United Kingdom",572853.474696578,E
"Quarterly","Q3-1960","France",469104.41925852,E
"Quarterly","Q3-1960","Germany",809532.161494483,E
"Quarterly","Q3-1960","Italy",426893.675840156,E
"Quarterly","Q3-1960","United Kingdom",581252.066618986,E
"Quarterly","Q4-1960","France",474664.327992619,E
"Quarterly","Q4-1960","Germany",817806.132384948,E
"Quarterly","Q4-1960","Italy",427221.338414114,E
...
\end{code}
\caption{Example of CSV file as provided by the OECD statistical
  website}
\label{tab:oecd-gdp}  
\end{table}

This is an instance of data in what we call \emph{atomic format}, that
is, a format in which each line of the outer file contains a single
data-point and extracting data mainly requires filtering the appropriate
lines. The outer time key is under the \texttt{Period} heading, and
has the format \texttt{Q\emph{<quarter>-<year>}}. Assuming that the
file in Table~\ref{tab:oecd-gdp} has the name \texttt{oecd.csv}, the
following script reconstructs the time series of Gross Domestic
Product for several countries:

\begin{footnotesize}
\begin{verbatim}
nulldata 220
setobs 4 1960:1

join oecd.csv FRA --tkey="Period,Q%q-%Y" --data=Value --filter="Country==\"France\""
join oecd.csv GER --tkey="Period,Q%q-%Y" --data=Value --filter="Country==\"Germany\""
join oecd.csv ITA --tkey="Period,Q%q-%Y" --data=Value --filter="Country==\"Italy\""
join oecd.csv  UK --tkey="Period,Q%q-%Y" --data=Value --filter="Country==\"United Kingdom\""
\end{verbatim}
\end{footnotesize}

Note the use of the format codes \verb|%q| for the quarter and
\verb|%Y| for the 4-digit year. A touch of elegance could
have been added by storing the invariant options to \cmd{join} using
the \cmd{setopt} command, as in

\begin{footnotesize}
\begin{verbatim}
setopt join persist --tkey="Period,Q%q-%Y" --data=Value
join oecd.csv FRA --filter="Country==\"France\""
join oecd.csv GER --filter="Country==\"Germany\""
join oecd.csv ITA --filter="Country==\"Italy\""
join oecd.csv  UK --filter="Country==\"United Kingdom\""
setopt join clear
\end{verbatim}
\end{footnotesize}

If one were importing a large number of such series it might be worth
rewriting the sequence of joins as a loop, as in

\begin{footnotesize}
\begin{verbatim}
strings countries = defarray("France", "Germany", "Italy", "United Kingdom")
strings vnames = defarray("FRA", "GER", "ITA", "UK")
setopt join persist --tkey="Period,Q%q-%Y" --data=Value

loop foreach i countries
  vname = vnames[i]
  join oecd.csv @vname --filter="Country==\"$i\""
endloop
setopt join clear
\end{verbatim}
\end{footnotesize}

\section{Special handling of time columns}
\label{sec:join-tconvert}

When dealing with straight time series data the \texttt{tkey}
mechanism described above should suffice in almost all cases. In some
contexts, however, time enters the picture in a more complex way;
examples include panel data (see section~\ref{sec:join-panel}) and
so-called realtime data (see chapter~\ref{chap:realtime}). To handle
such cases \texttt{join} provides the \option{tconvert} option. This
can be used to select certain columns in the right-hand data file for
special treatment: strings representing dates in these columns will be
converted to numerical values: 8-digit numbers on the pattern
\texttt{YYYYMMDD} (ISO basic daily format).  Once dates are in this
form it is easy to use them in key-matching or filtering.

By default it is assumed that the strings in the selected columns are
in ISO extended format, \texttt{YYYY-MM-DD}. If that is not
the case you can supply a time-format string using the
\option{tconv-fmt} option. The format string should be written using
the codes shown in Table~\ref{tab:join-datefmt}.

Here are some examples:

\begin{code}
# select one column for treatment
--tconvert=start_date

# select two columns for treatment
--tconvert="start_date,end_date"

# specify US-style daily date format
--tconv-fmt="%m/%d/%Y"

# specify quarterly date-strings (as in 2004q1)
--tconv-fmt="%Yq%q"
\end{code}

Some points to note:

\begin{itemize}
\item If a specified column is not selected for a substantive role in
  the join operation (as data to be imported, as a key, or as an
  auxiliary variable for use in aggregation) the column in question is
  not read and so no conversion is carried out.
\item If a specified column contains numerical rather than string
  values, no conversion is carried out.
\item If a string value in a selected column fails parsing using the
  relevant time format (user-specified or default), the converted
  value is \texttt{NA}.
\item On successful conversion, the output is always in daily-date
  form as stated above. If you specify a monthly or quarterly time
  format, the converted date is the first day of the month or quarter.
\end{itemize}


\section{Panel data}
\label{sec:join-panel}

In section~\ref{sec:join-timeser} we gave an example of reading
quarterly GDP data for several countries from an OECD file. In that
context we imported each country's data as a distinct time-series
variable. Now suppose we want the GDP data in panel format instead
(stacked time series). How can we do this with \texttt{join}? 

As a reminder, here's what the OECD data look like:
\begin{code}
Frequency,Period,Country,Value,Flags
"Quarterly","Q1-1960","France",463876.148126845,E
"Quarterly","Q1-1960","Germany",768802.119278467,E
"Quarterly","Q1-1960","Italy",414629.791450547,E
"Quarterly","Q1-1960","United Kingdom",578437.090291889,E
"Quarterly","Q2-1960","France",465618.977328614,E
\end{code}
and so on. If we have four countries and quarterly observations
running from 1960:1 to 2013:2 ($T$ = 214 quarters) we might set up our
panel workspace like this:
\begin{code}
scalar N = 4
scalar T = 214
scalar NT = N*T
nulldata NT --preserve
setobs T 1.1 --stacked-time-series
\end{code}

The relevant outer keys are obvious: \texttt{Country} for the country
and \texttt{Period} for the time period. Our task is now to construct
matching keys in the inner dataset. This can be done via two
panel-specific options to the \texttt{setobs} command. Let's work on
the time dimension first:
\begin{code}
setobs 4 1960:1 --panel-time
series quarter = $obsdate
\end{code}
This variant of \texttt{setobs} allows us to tell gretl that time in
our panel is quarterly, starting in the first quarter of 1960. Having
set that, the accessor \verb|$obsdate| will give us a series of
8-digit dates representing the first day of each quarter---19600101,
19600401, 19600701, and so on, repeating for each country. As we
explained in section~\ref{sec:join-tconvert}, we can use the
\option{tconvert} option on the outer series \texttt{Period} to get
exactly matching values (in this case using a format of \verb|Q%q-%Y|
for parsing the \texttt{Period} values).

Now for the country names:
\begin{code}
string cstrs = sprintf("France Germany Italy \"United Kingdom\"")
setobs country cstrs --panel-groups
\end{code}
Here we write into the string \texttt{cstrs} the names of the
countries, using escaped double-quotes to handle the space in ``United
Kingdom'', then pass this string to \texttt{setobs} with the
\option{panel-groups} option, preceded by the identifier
\texttt{country}. This asks gretl to construct a string-valued series
named \texttt{country}, in which each name will repeat $T$ times.

We're now ready to join. Assuming the OECD file is named
\texttt{oecd.csv} we do
\begin{code}
join oecd.csv GDP --data=Value \
 --ikey=country,quarter --okey=Country,Period \
 --tconvert=Period --tconv-fmt="Q%q-%Y"
\end{code}

\subsection{Other input formats}

The OECD file discussed above is in the most convenient format for
\texttt{join}, with one data-point per line. But sometimes we may want
to make a panel from a data file structured like this:
\begin{code}
# Real GDP
Period,France,Germany,Italy,"United Kingdom"
"Q1-1960",463863,768757,414630,578437
"Q2-1960",465605,782438,420715,572853
"Q3-1960",469091,809484,426894,581252
"Q4-1960",474651,817758,427221,584779
"Q1-1961",482285,826031,442528,594684
...
\end{code}

Call this file \verb|side_by_side.csv|.  Assuming the same initial
set-up as above, we can panelize the data by setting the sample to
each country's time series in turn and importing the relevant
column. The only point to watch here is that the string ``United
Kingdom'', being a column heading, will become \verb|United_Kingdom|
on importing (see section~\ref{sec:join-syntax}) so we'll need a
slightly different set of country strings.
%
\begin{code}
strings cstrs = defarray("France", "Germany", "Italy", "United_Kingdom")
setobs country cstrs --panel-groups
loop foreach i cstrs
  smpl country=="$i" --restrict --replace
  join side_by_side.csv GDP --data=$i \
  --ikey=quarter --okey=Period \
  --tconvert=Period --tconv-fmt="Q%q-%Y"
endloop
smpl full
\end{code}

If our working dataset and the outer data file are dimensioned such
that there are just as many time-series observations on the right as
there are time slots on the left---and the observations on the right
are contiguous, in chronological order, and start on the same date as
the working dataset---we could dispense with the key apparatus and
just use the first line of the \texttt{join} command shown
above. However, in general it is safer to use keys to ensure that the
data end up in correct registration.

\section{Memo: \texttt{join} options}
\label{sec:join-options}

Basic syntax: \texttt{join} \textsl{filename} \textsl{varname(s)} [
\textsl{options} ]

\begin{center}
\begin{tabular}{lp{.7\textwidth}}
  \textit{flag} & \textit{effect} \\ [6pt]
  \verb|--data| & Give the name of the data column on the right, in
                  case it differs from \textsl{varname}
                  (\ref{sec:join-syntax}); single import only  \\
  \verb|--filter| & Specify a condition for filtering data rows
                    (\ref{sec:join-filter}) \\
  \verb|--ikey| & Specify up to two keys for matching data rows
                  (\ref{sec:join-keys}) \\
  \verb|--okey| & Specify outer key name(s) in case they
                  differ the inner ones (\ref{sec:join-keys})\\
  \verb|--aggr| & Select an aggregation method for 1 to $n$ joins
                 (\ref{sec:join-aggr}) \\ 
  \verb|--tkey| & Specify right-hand time key (\ref{sec:join-timeser}) \\
  \verb|--tconvert| & Select outer date columns for conversion to
                      numeric form (\ref{sec:join-tconvert}) \\
  \verb|--tconv-fmt| & Specify a format for use with \texttt{tconvert} 
                       (\ref{sec:join-tconvert}) \\
  \verb|--no-header| & Treat the first row on the right as data
                       (\ref{sec:join-syntax}) \\
  \verb|--verbose| & Report on progress in reading the outer data
\end{tabular}
\end{center}


\pagebreak[4]

\section*{Appendix: the full Mroz data script}

\begin{code}
# start with everybody; get gender, age and a few other variables 
# directly while we're at it
open SHIW/carcom10.csv --cols=1,2,3,4,9,10,29,41

# subsample on married women between the ages of 30 and 60
smpl SEX==2 && ETA>=30 && ETA<=60 && STACIV==1 --restrict
# for simplicity, restrict to heads of households and their spouses
smpl PARENT<3  --restrict

# rename the age and education variables for compatibility; compute
# the "city" dummy and finally save the reduced base dataset
rename ETA WA
rename STUDIO WE
series CIT = (ACOM4C>2)
store mroz_rep.gdt

# make a temp file holding annual hours worked per job
open SHIW/allb1.csv --cols=1,2,8,11 --quiet
series HRS = misszero(ORETOT) * 52 * misszero(MESILAV)/12
store HRS.csv NQUEST NORD HRS

# reopen the base dataset and begin drawing assorted data in
open mroz_rep.gdt

# women's annual hours (summed across jobs) 
join HRS.csv WHRS --ikey=NQUEST,NORD --data=HRS --aggr=sum
WHRS = misszero(WHRS)

# labor force participation
LFP = WHRS > 0

# work experience: ETALAV = age when started first job
join SHIW/lavoro.csv ETALAV --ikey=NQUEST,NORD
series AX = misszero(WA - ETALAV)

# women's hourly wages
join SHIW/rper10.csv YL YM --ikey=NQUEST,NORD --aggr=sum
series WW = LFP ? (YL + YM)/WHRS : 0

# family income (Y = net disposable income)
join SHIW/rfam10.csv FAMINC --ikey=NQUEST --data=Y

# get data on children using the "count" method
join SHIW/carcom10.csv KIDS --ikey=NQUEST --aggr=count --filter="ETA<=18"
join SHIW/carcom10.csv KL6 --ikey=NQUEST --aggr=count --filter=ETA<6
series K618 = KIDS - KL6

# data on husbands: we first construct an auxiliary inner key for 
# husbands, using the little trick of subsampling the inner dataset
#
# for women who are household heads
smpl PARENT==1 --restrict --replace
join SHIW/carcom10.csv H_ID --ikey=NQUEST --data=NORD --filter="PARENT==2"
# for women who are not household heads
smpl PARENT==2 --restrict --replace
join SHIW/carcom10.csv H_ID --ikey=NQUEST --data=NORD --filter="PARENT==1"
smpl full

# add husbands' data via the newly-added secondary inner key
join SHIW/carcom10.csv HA --ikey=NQUEST,H_ID --okey=NQUEST,NORD --data=ETA
join SHIW/carcom10.csv HE --ikey=NQUEST,H_ID --okey=NQUEST,NORD --data=STUDIO
join HRS.csv HHRS --ikey=NQUEST,H_ID --okey=NQUEST,NORD --data=HRS --aggr=sum
HHRS = misszero(HHRS)

# final cleanup begins

# recode educational attainment as years of education
matrix eduyrs = {0, 5, 8, 11, 13, 16, 18, 21}
series WE = replace(WE, seq(1,8), eduyrs)
series HE = replace(HE, seq(1,8), eduyrs)

# cut some cruft
delete SEX STACIV KIDS YL YM PARENT H_ID ETALAV

# add some labels for the series
setinfo LFP -d "1 if woman worked in 2010"
setinfo WHRS -d "Wife's hours of work in 2010"
setinfo KL6 -d "Number of children less than 6 years old in household"
setinfo K618 -d "Number of children between ages 6 and 18 in household"
setinfo WA -d "Wife's age"
setinfo WE -d "Wife's educational attainment, in years"
setinfo WW -d "Wife's average hourly earnings, in 2010 euros"
setinfo HHRS -d "Husband's hours worked in 2010"
setinfo HA -d "Husband's age"
setinfo HE -d "Husband's educational attainment, in years"
setinfo FAMINC -d "Family income, in 2010 euros"
setinfo AX -d "Actual years of wife's previous labor market experience"
setinfo CIT -d "1 if live in large city"

# save the final product
store mroz_rep.gdt
\end{code}

    
%%% Local Variables: 
%%% mode: latex
%%% TeX-master: "gretl-guide"
%%% End: 
