Coding for Applied Economists

Data Management

Assignment

Learning Objectives

Following this assignment students should be able to:

  • Understand data structure
  • Label variables
  • Create and assign label values to data
  • Create new variables
  • Manipulate existing variables
  • Append and marge two data sets together

Reading

Topics

  • Types of data
  • Labeling data
  • Creating and manipulating data
  • Joining data

Readings

Lecture Notes

  1. Types of Data and Keeping Things Tidy
  2. Labeling Variables and Data
  3. Manipulating Data

Exercises

  1. Messy Data (10 pts)

    A lot of real data isn’t very tidy, mostly because most scientists aren’t taught about how to structure their data in a way that is easy to analyze.

    Download an untidy version of the World Bank data on GDP, which includes countries, years, and meta data.

    Think about what could be improved about this data and write down answers to the following questions:

    1. Describe three things about this data that are not tidy and how you could fix each of those issues.
    2. Could this data easily be imported into a programming language or a database in its current form?
    3. Do you think it’s a good idea to enter the data like this and clean it up later, or to have a good data structure for analysis by the time data is being entered? Why?

  2. Clean Up (10 pts)

    Convert the World Bank data downloaded in Exercise 1 into a more tidy format.

  3. Label Variables (10 pts)

    Download the World Bank’s Living Standards Measurement Survey (LSMS) household data set. The data includes the following variables (among others):

    • Identifiers & structure
      • country – country code
      • wave – survey wave (e.g., 1, 2)
      • hhid – household ID
      • eaid – enumeration area ID (cluster)
      • season – season of interview (e.g., lean, harvest)
    • Location
      • urban – urban (=1) or rural (=0)
      • admin_1, admin_2, admin_3 – administrative units
      • lat_modified, lon_modified – household GPS coordinates
      • geocoords_id – identifier for GPS cluster
    • Household characteristics
      • hh_size – household size (number of members)
      • hh_shock – indicator that hh experienced a shock
      • hh_primary_education – indicator: head has at least primary schooling
      • hh_electricity_access – indicator: hh has electricity
      • hh_dependency_ratio – dependents / working-age members
      • hh_formal_education – years of formal education of the head
      • nonfarm_enterprise – indicator: household runs a nonfarm enterprise
      • nb_fallow_plots – number of fallow plots
      • nb_plots – number of cultivated plots
      • share_kg_sold – share of agricultural production sold
    • Welfare
      • totcons_LCU – total consumption (local currency)
      • totcons_USD – total consumption (USD)
      • cons_quint – consumption quintile (1 = poorest, 5 = richest)
      • hh_asset_index – asset index
      • hdds – household dietary diversity score

    Apply the following labels to the corresponding variable

    1. Country -> country
    2. Wave number -> wave
    3. Agricultural season -> season
    4. Administrative level 1 -> admin_1
    5. Administrative level 2 -> admin_2
    6. Administrative level 3 -> admin_3
    7. Household size -> hh_size
    8. Was the household negatively impacted by a shock over the past 12 months? -> hh_shock
    9. Did anyone in the household complete primary school? -> hh_primary_education
    10. Does the household have access to electricity? -> hh_electricity_access
    11. Household dependency ratio -> hh_dependency_ratio
    12. Does anyone in the household posses any formal education? -> hh_formal_education
    13. Does anyone in household own a non-farm enterprise? -> nonfarm_enterprise
    14. Number of fallow plots under household management -> nb_fallow_plots
    15. Number of cultivated plots under household management -> nb_plots
    16. Share of harvest output (in kg) sold -> share_kg_sold
    17. Consumption aggregate per capita, in LCU -> totcons_LCU
    18. Consumption aggregate per capita, in USD -> totcons_USD
    19. Household consumption quintile -> cons_quint
    20. Household asset index -> hh_asset_index
    21. Household dietary diversity index -> hdds

  4. Label Values (10 pts)

    Using the World Bank’s LSMS data,

    1. Define a value label set called yesno where 0 = No and 1 = Yes

    2. Assign the value label set yesno to the following variables:

    • hh_shock
    • hh_primary_education
    • hh_electricity_access
    • hh_formal_education
    • nonfarm_enterprise

    Defining value labels when there are just a couple options (yes/no, male/female) is fairly straightforward, especially when variables are already binary (0/1). But sometimes a variable already exists that has the information we want, just not in the form we want. In that case, we can create a new, intermediate variable, whose values are based on the existing variable. This allows us to change one form of data (strings) into another form (numeric) and then apply labels.

    3. Create a variable called sector that equals 0 if urban = Rural and equals 1 if urban = Urban.

    • Label that variable “EA is rural or urban”
    • Define a value label set called sec_lbl where 0 = Rural and 1 = Urban
    • Assign the value label set sec_lbl to the variable sector
    • Drop urban and place sector after eaid (use help order if you need help with the order command)

    Defining value labels in this way when there are a lot of options can become very tedious very quickly. Luckily, Stata has a decode/encode command that can sometimes creatly reduce the amount of coding you have to do to define and assign a label.

    You will often have a variable which is a string that you would like to convert to a numeric. As an example, you can think of a variable called state that takes values of the name of each state in the U.S. In order to use this information in a regression, we need to convert the string to a numeric, like we did with sector. But with 50 state names, it will take a long time to 1) create a new variable that takes a unique numerical value for each state name and 2) define a label set for every value.

    In this case, we can use encode to change the strings to numerical values and then use the string text as a value label for each numerical value. The syntax for the command is:

     encode varname, gen(newvar)

    where varname is the name of the existing string variable and newvar is the new, numerical variable you want to create.

    4. Encode the variable country so that it is a numerical variable and uses the string values as labels.

    • Call the new variable you create Country
    • Drop the old variable country
    • Rename Country as country
    • Use the command order to place country as the first variable in the data set

    5. Encode the variable admin_1 so that it is a numerical variable and uses the string values as labels. Follow the same steps as in 4.4 and use order to place the new admin_1 immediately in front of admin_2.

  5. Create Variables (10 pts)

    Using the World Bank’s LSMS data,

    1. Create a new variable called tot_plots that combines cultivated and fallow plots using egen. Label the new variable with “Total number of plots under household management”. What is the mean number of plots owned by a household?

    2. Compute the mean total consumption in dollars (meancons_USD) for each consumption quintile using the by() option along with egen. Label the new variable with “Mean consumption per quntile (USD)”. Using tab, what is the mean consumption for the highest and lowest quintile?

    3. Find the maximum household dietary diversity score (hdds) separately for households with and without electricity access. Call this new variable max_hdds and label it “Max HDDS by electricity access”. (To get this, use sum and and the bysort prefix, which can be abbreviated bys. So, the line of code would start bys hh_electricity_access: sum).

  6. Change Variables (10 pts)

    Before undertaking this exercise, make sure you save the household-level version of that data that is currently in Stata’s memory. Save the file into your data folder and call it household_all.dta. Make sure you use the option replace so that when you run the .do file again it writes over any pre-existing versions of the data.

    Using the World Bank’s LSMS data, use collapse to create an EA-level dataset (grouped by eaid and sector) that contains the following EA-level variables:

    • Mean household size: based on hh_size
    • Share of households with electricity: based on hh_electricity_access
    • Share of households with a nonfarm enterprise: based on nonfarm_enterprise
    • Mean total consumption in USD: based on totcons_USD

    After the collapse, rename these variables as

    • ea_hh_size
    • ea_electricity_access
    • ea_nonfarm_enterprise
    • ea_totcons_USD

    1. What is the average EA-level household size in urban and rural areas?

    2. What percentage of urban households, on average, have electricity access? What percentage of rural households, on average, have electricity access?

    3. What percentage of urban households, on average, have a non-farm enterprise? What percentage of rural households, on average, have a non-farm enterprise?

    4. Is average EA-level consumption (mean_totcons_usd) higher in urban or rural areas?

    Save this EA-level file as ea_summary.dta.

  7. Append Data (10 pts)

    Using the World Bank’s LSMS data, we are going to simulate the common case where survey data arrive as separate files by wave. In fact, this is how the LSMS data comes, I’ve just given you a data set in which all the waves and all the countries have already been appended into a single data set. You will create wave-specific datasets and then append them.

    Start by re-loading the household data (household_all.dta). Create a data set that contains only observations from wave 1 using the keep if command. Save this file as hh_wave1.dta.

    1. How many observations came from the waves that are NOT wave 1?

    Now, re-open the full dataset and keep only wave 2 observations and save this as hh_wave2.dta.

    2. How many observations came from the waves that are NOT wave 2?

    Finally, append the data set that contains just wave 1 to the data set that contains just wave 2 using append.

    3. How many observations are in the combined wave 1 and wave 2 data set?

  8. Merge Data (20 pts)

    Using the World Bank’s LSMS data, we are going to practice merging datasets that represent different levels of aggregation (EA-level and household-level). Start by loading the ea_summary.dta file that you created in exercise 6. This will be the master file in memory.

    1. Use isid to determine what variable(s) uniquely identify the data. What are these variable(s)?

    Think through the structure of the EA and household data:

    • In the EA summary dataset, each eaid–sector combination should appear once.
    • In the household dataset, each eaid appears many times (one row per household).

    This suggests a one-to-many merge: many households per EA, one EA-level summary per EA. Now merge the household data into the EA data.

    2. How many matched and unmatched observations are there?

    Now each household observation contains both:

    • Household-level variables (e.g., totcons_USD, hh_dependency_ratio), and
    • EA-level averages (mean_totcons_usd, mean_dep_ratio, etc.).

    3. Create a consumption gap variable called cons_gap that is total household consumption (USD) minus mean consumption at the EA. This measures how much a household’s consumption differs from the average consumption in its EA. Is the consumption gap larger for rural households or urban households?

    **Save this merged data set as household_ea.dta.

  9. Check That Your Code Runs (10 pts)

    Sometimes you think you’re code runs, but it only actually works because of something else you did previously. To make sure it actually runs you should save your work and then run it in a clean environment.

    Follow these steps in to make sure your code really runs:

    1. Restart Stata by closing the instance of Stata that you have open.

    2. When you reopen Stata, type in the command line clear all. Or, better yet, put clear all at the top of your .do file, after the preamble but before any commands.

    3. Rerun your entire homework assignment by clicking the Execute button to make sure it runs from start to finish and produces the expected results.

    4. Make sure that you saved your code with the name of the assignment number (i.e., assignment_01). You should see the file in your git repo on your machine.

    5. Make sure that your code will run on other computers (relevant for all assignments after the first assignment)

      • No cd. Use a project.do file instead to set directories
      • Use only relative paths in files other than the project.do file
      • Use / not \ for paths

  10. Challenge 3 (Challenge - 20 pts)

    Using the household_ea.dta data set that you created in exercise 8, we are going to dig a bit more into the consumption gap between households in rural and urban EAs. We will walk through a number of steps that require you to use the concepts and commands from earlier in Assignment 3.

    • Start by using gen to create a variable called gap that equals 1 if the household’s consumption gap is negative. Then replace the value of gap so that it equals 0 if the household’s consumption gap is positive.
      • Hint: there are a number of households with missing values because either the household or EA data was missing. For reasons that are not important, Stata treates a missing value (the sysmis .) as positive infinity. So if you were to just type replace gen = 0 if cons_gap > 0 Stata will assign a 0 to household above the mean consumption but also to all households with missing values. So you need to add an additional conditional about cons_gap not equalling .
    • Label the variable as Indicator for sign of consumption gap. Also define a value label called gap_lbl where 0 is Positive Gap and 1 is Negative Gap. Assign this value label to the gap variable.
    • Use egen to create a variable called below that totals up all households with gap = 1. Do this by eaid and sector. Label the variable Number of households below mean consumption.
    • Use egen to create a variable called above that totals up all households with gap = 0. Do this by eaid and sector. Label the variable Number of households above mean consumption.
      • Hint: when having Stata total up households with a positive gap, you need to explicitly tell Stata to count the households where gap == 0.
    • Use egen to create a variable called tot_hh that counts up all households in an EA and sector. Label the variable Total number of households in EA.
    • Use gen to create a variable called share_below that is the percentage of households with a negative consumption gap within an EA and sector. Label the variable Percentage of households in EA below mean consumption.

    Now that we have all these variables created and labeled, we can ask questions about where the gap is most negative or most positive using bys in combination with sum.

    1. Is the share of households with a negative consumption gap greater in urban or rural EAs?
    2. Which country has the largest share of households with a negative consumption gap?
    3. Which country has the smallest share of households with a negative consumption gap?

Assignment submission checklist

Solutions

  1. Messy Data
  2. Clean Up
  3. Label Variables
  4. Label Values 1, 2, 3
  5. Create Variables
  6. Change Variables
  7. Append Data
  8. Merge Data
  9. Check That Your Code Runs
  10. Challenge 3