Data management

Documentation of the code in experiment_floodplain/data_management. The data management process consists of three tasks:

• Task 1: In the folder task_download_data. Downloads the data necessary to replicate the project.

• Task 2: In the folder task_create_target_population. Creates the target population, i.e., creates a dataset of the addresses that were eligible for being contacted (what is called “full sample” throughout the paper). The result is the dataset target_population.csv in bld/data.

• Task 3: In the folder task_sample_survey_recipients. Creates a dataset of the addresses that were in fact contacted. The result is the dataset survey_recipients.csv in bld/data.

  Note

  The dataset survey_recipients.csv needs rvo_pc6.csv to be reproduced. The latter will be shared upon publication of this paper. By default, this project creates a slightly different version of survey_recipients.csv to illustrate how the code works. I provide the (anonymized) dataset of the people who were in fact contacted under bld/replication_data/SURVEY.

• Task 4: In the folder task_clean_survey_data. Cleans the original Qualtrics survey data. The result is the dataset survey_data.csv in bld/data.

  Note

  Because of data protection concerns, the original dataset –which is anonymized and contains only a subset of the original Qualtrics data– will be shared upon publication of this paper. By default, this replication package uses fake data created based on the original dataset with the Python library Synthetic Data Vault (SDV). See also the Introduction of this replication package.

Script for Task 1: downloading the data

Script task_download_data.py

This script downloads publicly available data necessary for the replication of this paper (available via 4TU.ResearchData) and saves them to bld/replication_data. The datasets are the following:

• BAG data: The BAG folder contains data about all the (full) addresses in the Netherlands, derived from the Addresses and Buildings Key Registry (Basisregistraties Adressen en Gebouwen, or BAG) and acquired in .csv format via Geotoko, in July 2022.

  The .csv file provides a variety of building-related information, such as the function of each (dwelling within a) building, its perimeter and size, and its year of construction. The dataset codebook (in Dutch) is included in the folder.

• ENW data: The ENW folder contains shapefiles representing those areas of the Southern regions of The Netherlands (Limburg and North Brabant) that were flooded in July 2021. These maps have been shared by the ENW (Expertise Netwerk Waterveiligheid), the association of Dutch flood protection specialists, via the 4TU.ResearchData repository.

  The maps are based on best available information collected via aerial photography and during fieldwork at the flooded sites, and include realized inundation extent (in the sub-folders floodsGeul, floodsMaas, and floodsRoer), areas evacuated via emergency ordinances (in the sub-folder evacuations), and locations where incidents to water management infrastructure occurred (in the sub-folder incidents).

• Risicokaart data: The RISICOKAART folder contains shapefiles of the flood maps developed for the European Floods Directive (ROR2) delivered at the end of 2019, obtained in October 2021 by contacting lbo@risicokaart.nl. The layer can be viewed by the general population via an interactive app available at the Risicokaart website.

  Flood maps have been developed for four different scenarios: Large probability (10% risk in any given year), medium probability (1% risk in any given year), small probability (0.1% risk in any given year, or 1 in 1,000 years flood), and scenario of extraordinary events (0.01% risk in any given year, or or 1 in 10,000 years flood). Data on predicted flood extents under each scenario, represented by polygons classified by maximum water depth, are in the folder floodmaps2019.

  The folder otherinfo contains additional data on the location of primary water defences (which protects The Netherlands against floods from major rivers and the sea) and of regional water defences (which protects The Netherlands against floods from smaller rivers).

• Survey data: The SURVEY folder contains the dataset of survey recipients without identifying information (e.g., no addresses), named survey_recipients.csv, and a synthentic dataset named synthetic_survey_data.csv generated using Synthetic Data Vault (SDV) and based on the original Qualtrics data.

  Note

  This project automatically uses the real survey respondents’ data if the corresponding file is placed under src/experiment_floodplain/data. See also the Introduction of this documentation.

  The SURVEY folder contains two more datasets: original_variables.csv, which details which original variables will be cleaned (where by original I mean the “raw” data collected by Qualtrics), and final_variables.csv, which details which variables will appear in the final dataset.

Script generate_synthetic_data.py

In the folder generate_synthetic_data.

This script generates synthetic survey data based on the original one, using the python package Synthetic Data Vault (this package is not part of the environment and needs to be installed separately, in case you want to run this script). The resulting dataset, synthetic_survey_data.csv, is downloaded to bld/replication_data/SURVEY.

Note

This task is not part of the project workflow.

Scripts for Task 2: creating the target population

Script task_clean_BAG_data.py

This script cleans the raw data in bld/replication_data/BAG, creates a DataFrame and saves it as .csv file in bld/data/BAG.

The final dataset contains full addresses and some building-related characteristics for all (in-between, detached, semi-detached, corner or two-under-one-roof) houses in Limburg that, as of 2022:

• Belong to buildings “in use”;

• Are used for residential purposes;

• Have a one-to-one relationship with their building (e.g. no houses that share the same building with shops or other spaces);

• Have unique addresses based on street, housenumber, and postcode.

These restrictions are supposed to facilitate reaching our sample via survey invitation letters and to avoid ambiguities on the effective flood exposure of the survey respondents (for example, I drop apartments because it is not possible to tell on which floor they are located).

Script task_clean_ENW_data.py

This script cleans the raw data in bld/replication_data/ENW, creates a GeoDataFrame and saves it as geopackage (.GPKG) in bld/data/ENW.

The final dataset contains four columns:

• FEATURE, indicating the event type (incident, evacuation, or flood);

• geometry, containing the shapely Polygons or shapely Point associated with the FEATURE;

• Status, which is always 1 except for some evacuations (as some areas were not formally evacuated);

• COMMENT containing additional information (e.g. incident acronym as found in ENW “ Hoogwater 2021 Feiten en Duiding” report).

Script task_assign_flood_exposure.py

This script assigns each address in the clean BAG dataset in bld/data/BAG to (1) flooded areas in July 2021 according to ENW data, (2) 6-digits postcodes from which claims for house-related and house content-related damages were filed to the RVO after the floods in July 2021, and (3) flood probability and maximum water depth according to the Risicokaart flood maps. The final dataset is saved as a .csv file named full_sample.csv in bld/data.

The RVO data are necessary to create the indicator variable FLOODED, which in turn is necessary for the sampling of survey recipients. The RVO data are not available to the public, but will be available to researchers as a .csv file named rvo_pc6.csv once the paper is published. See also the Introduction of this documentation.

Note

This project automatically uses the RVO data if the .csv file is placed under src/experiment_floodplain/data. Otherwise, the values of the variable FLOODED are generated at random.

Scripts for Task 3: sampling the survey recipients

Script task_add_survey_weights_to_full_sample.py

This script adds the survey weights to the full sample of addresses eligible to be contacted. I oversample in areas affected by the July 2021 floods. The resulting dataset is full_sample_with_weights.csv in bld/data.

Note

The sampling weights will be the true ones only if rvo_pc6.csv is placed under src/experiment_floodplain/data. If not, the variable FLOODED (on which the sampling strategy depends) will be assigned at random, thus affecting the sampling weights. This does not really matter to reproduce the results in the paper, because I provide the dataset of true survey recipients (without identifying information) in bld/replication_data/SURVEY.

Script task_create_pilot_sample.py

This script creates the Qualtrics contact list for the pilot sample. This script produces three .csv files, all saved in bld/data/survey_recipients:

• pilot_sample.csv, which contains the sampled addresses.

• pilot_balance.csv, which compares a number of average covariate values between the population and (both unweighted and weighted) sample.

• pilot_qualtrics.csv, which has the same dimensions of pilotSample.csv but only contains variables needed for the qualtrics survey, including unique codes to be used as authenticators.

Script task_create_main_sample.py

This script creates the Qualtrics contact list for the main sample. This script produces three .csv files, all saved in bld/data/survey_recipients:

• main_sample.csv, which contains the sampled addresses.

• main_balance.csv, which compares a number of average covariate values between the population and (both unweighted and weighted) sample.

• main_qualtrics.csv, which has the same dimensions of main_sample.csv but only contains variables needed for the qualtrics survey, again including unique codes to be used as authenticators.

Script task_create_survey_recipients_dataset.py

This script adjusts the survey recipients dataset to reflect the addresses that were in fact contacted after data collection. In particular:

• Add indicator variable for incorrect addresses (where mail was rejected).

• Add indicator variable for whether the address was reached during data collection, and at what stage.

The resulting dataset is saved as survey_recipients.csv in bld/data.

Note

This dataset is identical to survey_recipients.csv in bld/replication_data/SURVEY only if the dataset rvo_pc6.csv is placed under src/experiment_floodplain/data.

Scripts for Task 4: cleaning the survey data

Sript task_clean_survey_data.py

This script cleans the original Qualtrics data.

Auxiliary modules

Script merge_data.py

In the folder task_create_target_population.

This script contains functions to merge data from various sources.

add_floodmaps_indicators(df, waterdepth_column, floodrisk_column)

Derive indicators of flood risk and maximum water depth from existing columns named waterdepth_column and floodrisk_column in Pandas.DataFrame df.

assign_address_to_admin_region(addressesGDF, admin_path, admin_level)

Assign each address in addGDF to specified administrative region.

I do a spatial merge (i.e. I assign each address to the administrative region that contains the shapely.Point associated with such address, as recovered from the lat and lon columns) instead of merging on administrative level’s code.

The reason is that I am forced to use data from different years, and any administrative level of a certain address (but especially the low-level ones) can change over time. For example, postcodes can be reassigned to a different buurt or wijk.

The function returns a dataframe which contains a column equal to the index of the merged administrative level dataframe.

Parameters:

• addressesGDF (geopandas.DataFrame) – Dataframe of addresses. Needs to have an active geometry column to perform the spatial merge.

• admin_path (str or Pathlib object) – Path to geopandas.DataFrame of administrative region. Needs to have an active geometry column to perform the spatial merge.

• admin_level (str) – Level of administrative region. Can be “pc6”, “pc5”, “buurt”, “wijk”, or “gemeente”.

• path (str or Pathlib object) – Path to save the final dataframe to.

Returns:

geopandas.DataFrame

assign_admin_geometries_to_addresses(df, admin_path, admin_level)

Merge each row of df to the appropriate administrative region boundary in the admin_path geopandas.GeoDataFrame.

Parameters:

• df (pandas.DataFrame) – Dataframe of BAG addresses, with index_{admin_level} columns (needed to merge on) and either LAT and LON columns or geometry column. It is supposed to be the dataframe saved as population.csv in bld.data.

• admin_path (str or Pathlib object) – Path to administrative dataset to load. It is supposed to be one of the datasets in bld.data.CSB.

• admin_level (str) – Administrative level of dataset, for example “pc5”, “pc6”, “buurt”, “wijk”.

Returns:

geopandas.GeoDataFrame

assign_flood_risk(key, RKdatadict, bagGDF, depthDict, max_distance=500)

Assign each row in bagGDF to flooded or non-flooded status according to given scenario of Risicokaart flood maps.

Parameters:

• key (str) – Refers to Risicokaart flood scenario.

• RKdatadict (dict) – Dictionary whose keys refer to the Risicokaart scenarios, and whose values refer to the path to the Risicokaart flood maps.

• bagGDF (geopandas.Dataframe) – BAG dataset. Needs to have geometry column.

• depthDict (dict) – Dictionary of water depth labels.

Returns:

geopandas.Dataframes

drop_duplicates_with_lower_waterdepth(gdf, duplicated_row, key)

For geodataframe gdf, corresponding to a key scenario, drop all the rows with the index of duplicated_row besides the one with the highest value for NEAR_WATERDEPTH_{key}_INDICATOR.

get_conditional_waterdepth(df)

Compute minimum water depth, conditional on being flooded, for df. Requires df to have column FLOOD_STATUS, WATERDEPTH_10_INDICATOR, WATERDEPTH_100_INDICATOR, WATERDEPTH_1000_INDICATOR, WATERDEPTH_10000_INDICATOR, and WATERDEPTH_MAX.

get_flood_status(gdf)

Get flood risk status for each column in gdf (takes value “floodable”, “nearly floodable”, “never flooded”).

get_flood_variables(gdf)

Get flood risk variables for gdf. In particular, this function generates the following variables (where “scenario” takes values 10, 100, 1000, and 10000):

• FLOOD_{scenario} for scenario in (10, 100, 1000, 10000): Whether the address is within a Risicokaart flooded geometry under given scenario.

• WATERDEPTH_{scenario} for scenario in (10, 100, 1000, 10000): Maximum water depth of the flooded geometry the address is within under given scenario.

• WATERDEPTH_{scenario}_INDICATOR for scenario in (10, 100, 1000, 10000): Maximum water depth of the flooded geometry the address is within under given scenario.

get_july_floods_indicators(gdf)

Get flood exposure variables for gdf. In particular, this function generates the following variables:

• FLOODED_ENW_UNPREDICTED_{suffix}: Areas that were flooded in July 2021 according

  to ENW data, but that never flood according to Risicokaart flood maps.

• FLOODED_RVO_UNPREDICTED_{suffix}: Areas from which flood damage claims were filed

  after the July 2021 floods, but that never flood according to Risicokaart flood maps. Only computed if the RVO data are present.

The suffix “_STRICT” indicates that we consider unpredictable all the floods that happened outside of the geometries in the Risicokaart maps. The suffix “_LAX” indicates that we consider unpredictable all the floods that happened farther away than 500m from the geometries in the Risicokaart maps.

get_most_likely_scenario(df, extended=False)

Get most likely scenario for which each addres in df floods. Setting extended to True considers also houses within 500m from flooded areas.

get_waterdepth_indicator(gdf, waterdepths, var_name)

Convert water depth variable named var_name_waterdepth in gdf to numeric, for scenario in waterdepths.

Parameters:

• gdf (geopandas.GeoDataFrame) – Dataframe.

• waterdepths (list) – List of some or all values in 10, 100, 1000, 10000.

• var_name (str) – Fixed part of variable name in original dataset.

Returns:

geopandas.GeoDataFrame

load_BAG_chunk(BAGpath, BAGdict, chunk)

Read 1 million rows of BAG data .csv file, after skipping the number of rows indicated by the chunk argument. Read the first row as columns’ names.

Parameters:

• BAGpath (str or Pathlib object) – Path to BAG data.

• BAGdict (dict) – Dictionary of labels for columns of BAG dataframe.

• chunk (int) – Number of rows to skip.

Returns:

Pandas.DataFrame

merge_flood_datasets_to_bag(scenarios, RKdatadict, bagGDF)

Merge 4 datasets assigning BAG addresse to each of the 4 Risicokaart scenarios to full BAG data.

Parameters:

• scenarios (list of pandas.Dataframes) – List of dataframes, one for each Risicokaart scenario.

• RKdatadict (dict) – Dictionary whose keys refer to the Risicokaart scenarios.

• bagGDF (geopandas.Dataframe) – BAG dataset.

Returns:

geopandas.Dataframe

remove_redundant_flood_rows(gdf, key, finalGDF)

For geodataframe gdf, corresponding to a key scenario, keep the duplicated row with the highest maximum depth.

Script sample.py

In the folder task_sample_survey_recipients.

Auxiliary functions to generate sample of survey respondents.

format_data_for_qualtrics(qualtricsDF)

Format pandas.DataFrame that needs to be uploaded as Qualtrics contact list.

get_covariates_dataset(covariatesDF, valuesDF, weights=False)

Compute (weighted) average values of variables in covariatesDF from valuesDF. Missing values are automatically excluded.

Parameters:

• covariatesDF (pandas.DataFrame) – Dataframe of covariates. Need to have columns named “CATEGORY”, “DESCRIPTION”, “VARLEVEL”, “VARNAME”, and “WEIGHTS”.

• valuesDF (pandas.DataFrame) – Dataframes of covariates’ values. Need to have a column named “VARNAME” containing the variables in covariatesDF.

• weights (Bool) – Weights to compute weighted mean. If True, will be pulled from valuesDF.WEIGHTS. Default is False

Returns:

pandas.DataFrame

id_generator(seed, length=8, restriction=False)

Generate random string of given length, given seed.

Script clean_data.py

In the folder task_clean_survey_data.

This script contains functions used in task_clean_survey_data.py to clean the original Qualtrics data in bld/data/SURVEY.

add_floodmaps_indicators(df, waterdepth_column, floodrisk_column)

Derive indicators of flood risk and maximum water depth from existing columns named waterdepth_column and floodrisk_column in Pandas.DataFrame df.

add_outcomes_dummies(df)

Add columns to indicate (1) whether respondent has at least one outcome, and (2) whether a given outcome is present, to Pandas.DataFrame df. The latter needs to have columns “worry_RE”, “risk_RE”, “damages_RE”, “comptot_RE”, “compshare_RE”, “wtp_info”, and “wtp_insurance”.

add_revise_beliefs_variables(df)

Add variables providing information on expected direction of beliefs updating conditional on baseline information frictions for risk and damages.

add_time_spent_on_treatment_text(df)

Add the time spent on the treatment text (“{text}_time_page_submit”) to the appropriate total_seconds_-prefixed column for each survey respondent.

I derived the time spent on each text from the toggle box, therefore by design the columns prefixed by total_seconds_ are empty whenever the text was not in the toggle box page (e.g. total_seconds_maps is empty for survey respondents in treatment 2).

assign_variable_quartile(df, var)

Compute and assign quartile to each observation of variable var in Pandas.DataFrame df.

assign_variable_tercile(df, var)

Compute and assign tercile to each observation of variable var in Pandas.DataFrame df.

clean_beliefs_data(df)

Clean dataframe df containing questions of Qualtrics survey related to prior and posterion beliefs. The labels for questions related to confidence in beliefs are in the dictionary confDict.

clean_covariates_data(df)

Clean answers to questions about households and survey respondent.

clean_info_data(df, error)

Clean dataframe df containing questions of Qualtrics survey related to information quality. The labels for questions related to confidence in the answers are in the dictionary confDict.

Parameters:

• df (pandas.DataFrame) – Dataframe of survey responses.

• error (float or int) – Percentage points threshold for which a participants’ estimate of the compensated claims is considered incorrect.

Returns:

pandas.DataFrame.

clean_tech_data(df)

Clean dataframe df containing questions of Qualtrics survey related to technical fields (e.g.: time of survey completion, answers to consent forms…)

clean_text_evaluation(df)

Clean evaluation of treatment texts.

clean_treatment_data(df)

Clean dataframe df containing questions of Qualtrics survey related to experimental treatment(s).

clean_variables_block(qdf, rvar_df, fvar_df, block, fun)

Clean block of variables in qdf.

Parameters:

• qdf (Pandas.DataFrame) – Dataset of raw Qualtrics variables.

• rvar_df (Pandas.DataFrame) – Dataset containing the names of the raw Qualtrics variables to clean.

• fvar_df (Pandas.DataFrame) – Dataset containing the names of the final clean variables.

• block (Pandas.DataFrame) – Block of variables to clean (BLOCK in rvar_df and in fvar_df).

• fun (fun) – Function to clean the specified block of variable.

Returns:

Pandas.DataFrame

clean_wtp_data(df)

Clean data related to willingness-to-pay elicitations.

compute_belief_update(df, prior_belief, posterior_belief)

Compute difference between variables posterior_belief and prior_belief in Pandas.DataFrame df.

compute_prior_beliefs_zscore(df)

Compute z-score of prior beliefs in Pandas.DataFrame df.

The variables I use are:

• risk_standardized: Standardized measure of beliefs about

  10-year flood risk.

• damages_wins975_1000_standardized: Standardized measure

  of beliefs about damages, winsorized at the 97.5% percentile, in thoursand of euros.

• comptot_standardized: Standardized measure of beliefs about

  total compensation.

• compshare_standardized: Standardized measure of beliefs

  about compensation from the government.

The standardized beliefs (i.e. the z-scores) increase with expected net damages, i.e. the higher risk_standardized and damages_wins975_1000_standardized, and the lowest comptot_standardized and compshare_standardized, the higher the average z-score.

compute_time_spent_on_info(series)

Compute time spent on each toggle box containing information.

The Qualtrics survey is programmed in such a way that three toggle boxes are presented simultaneously to each survey respondent, and only one toggle box can remain open at any given time. Any open toggle box will be closed if the survey respondent clicks on it again or clicks on another toggle box. Moreover, each toggle box is associated to a variable storing timestamps of each time the toggle box was clicked. As a result, it is possible to compute the time spent on each toggle box from the timestamps.

For each survey respondents (whose timestamps for all toggle boxes is stored in series), this algorithm figures out the order in which the toggle boxes have been clicked, and uses it to derive the total number of seconds spent on each toggle box.

create_info_friction_indicators(df, error)

Create variables that take value 0 if survey respondents answered a question correctly and 1 otherwise. This serves to aggregated information frictions.

Parameters:

• df (pandas.DataFrame) – Dataframe of survey responses.

• error (float or int) – Percentage points threshold for which a participants’ estimate of the compensated claims is considered incorrect.

Returns:

pandas.DataFrame.

derive_wtp_for_info(df)

Derive willingness to pay for information from incentivized survey task.

format_timestamps(df)

Extract datetime objects from relevant columns in Pandas.DataFrame df, i.e. those with prefix “timestamps”. The latter columnes contains the (stringed) timestamps referring to each time each toggle box was clicked.

For example, if the first row of column “timestamps_maps” contains the value: ‘Fri Dec 23 2022 14:18:24 GMT+0100 (CET);’, this means that the first-row survey respondent clicked the toggle box containing information on floodmaps exactly one time, on Friday, December 23th 2022, at 14:18:24 Central European Time.

make_adjustment_for_pilot_data(df)

For some observations (less than 20, from the pilot), the timestamp of the submit button in the toggle boxes page is missing (my mistake, I misunderstood how the total time spent on the page is saved by Qualtrics. I thought Qualtrics saved timestamps). As a result, I need to compute the time spent on the last toggle box differently.

merge_same_question_different_treatment(df, colname)

Take columns named “colname”, “colname.1”, “colname.2”, and “colname.3” (for whatever string colname is equal to) from Pandas.DataFrame df, and merge them to one unique column named “colname”. Each row must have a well-defined value for only one of the “colname” columns, and a numpy nan elsewhere.

>>> colDF = pd.DataFrame([
        [1, np.nan, np.nan, np.nan],
        [np.nan, 1, np.nan, np.nan],
        [np.nan, np.nan, 1, np.nan],
        [np.nan, np.nan, np.nan, 1]
        ],
    columns=["c", "c.1", "c.2", "c.3"]
    )
>>> merge_same_question_different_treatment(colDF, "c")
    c
0  1.0
1  1.0
2  1.0
3  1.0

return_boxes_order(series)

Sort order of tuple of string and datetime object.

return_order_of_clicked_boxes(df)

Add columns indicating order of clicked boxes to df.

return_ordered_tuple(series)

Return tuple of string and first element of each value in Pandas.Series series.

standardize_variable(df, var)

Standardized variable var in Pandas.DataFrame df.

winsorize(df, columns, quantile)

Winsorize list of columns in Pandas.DataFrame df according to upper quantile quantile.

Parameters:

• df (Pandas.DataFrame) – Dataframe of interest.

• columns (list) – List of column(s) in df.

• quantile (number) – Upper quantile, must be between 0 and 1.

Returns:

Pandas.DataFrame with winsorized columns.