YEAR TWO DATA

Load the data needed to process the second year survey results.

Required R Packages

This chapter will utilize the following packages:

library( haven )
library( dplyr )
library( tidyr )
library( epoxy )
library( memisc )
library( labelled )

Load the Data

# LOAD DATA DICTIONARY 
dd <- 
  readxl::read_xlsx( 
   "../data-dictionaries/dd-nptrends-wave-02.xlsx", 
   sheet = "data dictionary" )

# USE RAW VNAME IF VNAME IS EMPTY:
dd$vname[ is.na(dd$vname) ] <- 
   dd$vname_raw[ is.na(dd$vname) ]

# LOAD QUALTRICS SURVEY DATA 
fpath <- "DATA-PREP/02-year-two/01-data-raw/"
fname <- "wave-02-qualtrics-download-29mar23.csv"
survey_df  <- readr::read_csv( paste0( fpath, fname ) )
survey_df  <- survey_df[ -(1:2), ]                      # drop qualtrics headers  

fname <- "YEAR-02-COMPLETE-CASE-CODES.csv" 
cases <- read.csv( paste0(fpath,fname) )

cases <- dplyr::select( cases, EIN, Completion_Status )
survey_df <- merge( survey_df, cases, by="EIN", all.x=T )

survey_df <- 
  survey_df %>% 
  dplyr::filter( Completion_Status %in% c("Complete","Partial_keep") ) %>%
  dplyr::select( - Completion_Status )

Note the raw data is challenging because it contains qualtrics encodings and missing values need context to be used correctly (e.g. were the questions skipped by skip logic, or by the respondent?).

survey_df[ 1:6, 51:55 ] %>% pander::pander()   # data peek
PeopleServed#2_2_1 DemandNextYear Staff#1_1_1 Staff#1_2_1 Staff#1_3_1
-99 Increase 60 30 -99
-99 Increase 3 4 9
-99 Increase 3 1 10
-99 Increase 13 2 11
N/A Increase 1 -99 14
N/A Increase 1 8 11

Data Workflow

The following chapters describe the workflow used to import qualtrics data and apply cleaning and transformation steps to prepare the restricted use file and public use file for subsequent analysis:

  1. Renaming columns
  2. Drop nuisance columns (survey deployment attributes)
  3. Add meaningful labels to response values
  4. Dropping duplicates, incomplete responses and test responses

Renaming Columns

Columns referencing survey questions are renamed with the help of a data dictionary to improve readability.

torename <- 
  dd %>% 
  dplyr::select( vname, vname_raw ) %>% 
  tidyr::drop_na()

Examples:

[1] "DemandNextYear" "Staff#1_1_1"    "Staff#1_2_1"    "Staff#1_3_1"

Give the data meaningful names so that it is easier to work with.

survey_df <- 
  survey_df %>% 
  dplyr::rename_at(
     vars( torename$vname_raw ), 
     ~ torename$vname )

Examples:

[1] "Dmnd_NxtYear"         "Staff_Fulltime_2021"  "Staff_Parttime_2021" 
[4] "Staff_Boardmmbr_2021"

Drop Nuisance Fields

Many of the exported qualtrics fields contain non-useful metadata or are empty. These have been labeled as “DROP” in the group field. Remove these for convenience.

# SELECT COLUMNS TO DROP: 
DROP_THESE <- dd$vname[ dd$group == "DROP" ] |> na.omit()

survey_df <- 
  survey_df %>% 
  dplyr::select( -any_of( DROP_THESE ) )

Add Survey Weights

# ADD SURVEY WEIGHTS 
fpath <- "DATA-PREP/00-sample-framework/"
fname <- "year2wt.csv"
wt2  <- readr::read_csv( paste0( fpath, fname ) )

survey_df <- merge( survey_df, wt2, by.x="EIN", by.y="ein", all.x=TRUE )

Groups of Variables

Each group of survey questions comes with its own set of valid inputs that must be recoded separately. For example, “N/A”’s are options for some survey questions and not for others, and some survey questions allow for manual text inputs.

The below code chunk separates all survey questions into their respective categories before further separating each category into numeric, text or NA inputs.

NA questions here refer to “Check here if not applicable to your organization” questions in the survey, where a “C” indicates that the respondent has checked the N/A box.

  • 15 questions about CHANGES TO PROGRAMS AND SERVICES
  • 4 questions about the NUMBER OF PEOPLE EACH ORGANIZATION SERVES
  • 1 question about OVERALL PROGRAM DEMAND
  • 27 questions about STAFF NUMBERS
  • 2 questions about DONOR AND VOLUNTEER IMPORTANCE
  • 11 questions about CHANGES TO LEADERSHIP
  • 26 questions about THE RACE AND GENDER OF CEOS AND BOARD CHAIRS
  • 8 questions about CHANGES TO ORGANIZATIONAL FINANCES
  • 2 questions about CARES FUNDING
  • 2 questions about FINANCIAL RESERVES
  • 9 questions about REVENUE SOURCES
  • 26 questions about FUNDRAISING SOURCES
  • 2 questions about DONOR TYPES IN FUNDRAISING
  • 7 questions about FUNDRAISING YIELDS
  • 11 questions about FUNDRAISING STRATEGY CHANGES
  • 1 questions about MAJOR GIFT AMOUNTS
  • 13 questions about EXTERNAL AFFAIRS
  • 1 questions about FUTURE CONCERNS