<jupyter_start><jupyter_text>Data wrangling Open RStudio. Open a new R script in R and **save it as** `wpa_4_LastFirst.R` (where Last and First is your last and first name). Careful about: capitalizing, last and first name order, and using `_` instead of `-`.At the top of your script, write the following (**with appropriate changes**):<jupyter_code># Assignment: WPA 4
# Name: <NAME>
# Date: 23 March 2020<jupyter_output><empty_output><jupyter_text>Load some data in R<jupyter_code>library(tidyverse)
library(haven) # NOTE: you might need to install.packages("haven") first

# Load data in R
survey_data = read_spss("https://github.com/laurafontanesi/r-seminar/raw/main/data/ccam_original.sav")

glimpse(survey_data)<jupyter_output>Rows: 20,024
Columns: 54
$ case_ID                     [3m[90m<dbl>[39m[23m 2, 3, 5, 6, 7, 8, 9, 11, 12, 14, 15, 16, 1…
$ wave                        [3m[90m<dbl+lbl>[39m[23m 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,…
$ year                        [3m[90m<dbl+lbl>[39m[23m 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,…
$ weight_wave                 [3m[90m<dbl>[39m[23m 0.54, 0.85, 0.49, 0.29, 1.29, 2.56, 0.23, …
$ weight_aggregate            [3m[90m<dbl>[39m[23m 0.2939263, 0.4626617, 0.2667109, 0.1578493…
$ happening                   [3m[90m<dbl+lbl>[39m[23m 3, 2, 2, 3, 3, 2, 3, 1, 1, 3, 3, 3, 3,…
$ cause_original              [3m[90m<dbl+lbl>[39m[23m 1, 1, 2, 2, 1, 2, 1, 1, 4, 2, 1, 1, 1,…
$ cause_other_text            [3m[90m<chr>[39m[23m "", "", "", "", "", "", "", "", "", "", ""…
$ cause_recoded               [3m[90m<dbl+lbl>[39m[23m 6, 6, 4, 4, 6, 4, 6, 6, 3, 4, 6, 6, 6,…
$ sci_consensus               [3m[90m<dbl+lbl>[39m[23m 4, 1, 2, 4, 2, 2, 2, 3, 2[...]<jupyter_text>1. What is data wrangling?Data wrangling is the process of transforming data from a raw format (in which they were collected) into another format with the intent of making it more appropriate and valuable for exploratory and confirmatory data analyses.Until now, we only worked with "tidy" datasets, i.e., dataset that were already ready for being plotted/analysed. But this is an exeption. Especially when collecting your own data, but also when accessing another researcher's data, we typically have to go through a few steps before being able to run analysese or plotting.Today's class is based on specific functions in the tidyverse that will serve exactly this purpuse.  2. Functions to know and some examplesVirtually all you need to know is in [this cheatsheet](https://rstudio.com/wp-content/uploads/2015/02/data-wrangling-cheatsheet.pdf).The more frequent functions you will use are:- `%>%`: The pipe operator, to chain functions (not recommended at the beginning).- `arrange`: order the dataframe based on values of variables.- `rename`: to rename specific columns.- `filter` and `slice`: to select subsets of rows.- `select`: to select subsets of columns.- `mutate`: make new columns based on modifications of existing ones.- `mutate_all`: make the same modification on all columns. - `mutate_if`: make the same modification on columns satisfying specific conditions. Look [here](https://dplyr.tidyverse.org/reference/mutate_all.html) for other kinds of mutate.- `summarize`: to calculate summary statistics.- `summarize_all`: to calculate summary statistics on all columns. - `summarize_if`: to calculate summary statistics on columns satisfying specific conditions. Look [here](https://dplyr.tidyverse.org/reference/summarise_all.html) for other kinds of summarize.- `group_by`: to group the data based on some variables, so that subsequent calculations are done on such variables.- `full_join`, `left_join`, `right_join`: to join separate dataframes.- `bind_rows` and `bind_cols`: to append dataframes vertically or horizontally.- `gather` and `spread`: bring wide form to long form and viceversa.- `unite`: make a column from multiple columns.- `separate`: make multiple columns from one column.<jupyter_code># only select columns of interest equivalent to data.frame[,interesting_columns]
interesting_columns = c('wave', 'year', 'happening', 'cause_recoded', 'sci_consensus', 'worry', 'harm_personally', 
                        'harm_US', 'harm_dev_countries', 'harm_future_gen', 'harm_plants_animals', 'when_harm_US',
                        'reg_CO2_pollutant', 'reg_utilities', 'fund_research', 'discuss_GW',
                        'gender', 'age_category', 'educ_category', 'income_category', 'race', 'party_x_ideo', 'region4', 'employment')

columns_to_drop = c('case_ID', 'weight_wave', 'weight_aggregate', 'cause_original', 'cause_other_text',
                    'reg_coal_emissions', 'hear_GW_media', 'age', 'generation', 'educ', 'income', 'ideology',
                    'party', 'party_w_leaners', 'registered_voter', 'region9', 'religion', 'religion_other_nonchristian',
                    'evangelical', 'service_attendance', 'marit_status', 'house_head', 'house_size',
                    'house_ages0to1', 'house_ages2to5', 'house_ages6to12', 'house_ages13to17', 'house_ages18plus',
                    'house_type', 'house_own')

new_survey_data = select(survey_data, all_of(interesting_columns))

#or:
new_survey_data = select(survey_data, -all_of(columns_to_drop))

glimpse(new_survey_data)
# sort by one variable, and only show the first 6 values
head(arrange(survey_data, weight_wave), 6)
# sort by 2 variables (one in opposite order), and only show the last 10 values
tail(arrange(survey_data, weight_wave, desc(weight_aggregate)), 10)
# another way to select rows (equivalent to data.frame[200:210,])
slice(survey_data, 200:210)
# eliminate people (i.e., rows) who didn't reply in some variables of interest (an absence of reply was coded as -1 here)
survey_data = filter(survey_data, 
                     happening > 0, 
                     cause_recoded > 0, 
                     sci_consensus > 0, 
                     worry > 0)

glimpse(survey_data)
# convert all data to integers type
survey_data = mutate_all(survey_data, 
                         as.integer)

glimpse(survey_data)
# add new columns
# note that when you use the old name, the column is replaced with the new one

survey_data = mutate(survey_data, 
                     happening_cont = happening + rnorm(mean=0, sd=.5, n=nrow(survey_data)),
                     worry_cont = worry + rnorm(mean=0, sd=.5, n=nrow(survey_data)),
                     year=recode(year, 
                                 `1` = 2008,
                                 `2` = 2010,
                                 `3` = 2011,
                                 `4` = 2012, 
                                 `5` = 2013,
                                 `6` = 2014,
                                 `7` = 2015,
                                 `8` = 2016,
                                 `9` = 2017),
                     happening_labels=recode(happening,
                                             `1` = "no",
                                             `2` = "dont know",
                                             `3` = "yes"),
                     cause_recoded=recode(cause_recoded,
                                          `1` = "dont know",
                                          `2` = "other",
                                          `3` = "not happening",
                                          `4` = "natural",
                                          `5` = "human",
                                          `6` = "natural and human"),
                     sci_consensus=recode(sci_consensus,
                                          `1` = "dont know",
                                          `2` = "disagreement",
                                          `3` = "not happening",
                                          `4` = "happening"),
                     gender=recode(gender,
                                   `1` = "male",
                                   `2` = "female"),
                     age_category_labels=recode(age_category,
                                                `1` = "18-34",
                                                `2` = "35-54",
                                                `3` = "55+"),
                     educ_category_labels=recode(educ_category,
                                                 `1` = "no highschool",
                                                 `2` = "highschool",
                                                 `3` = "college",
                                                 `4` = "bachelor or higher"),
                     income_category_labels=recode(income_category,
                                                   `1` = "less 50000",
                                                   `2` = "50000-99999",
                                                   `3` = "more 100000"),
                     race=recode(race,
                                 `1` = 'white non hisp',
                                 `2` = 'black non hisp',
                                 `3` = 'other non hisp',
                                 `4` = 'hisp'),
                     party_x_ideo=recode(party_x_ideo,
                                         `-2` = "no interest",
                                         `-1` = "refused",
                                         `1` = "liberal democrat",
                                         `2` = "moderate democrate",
                                         `3` = "independent",
                                         `4` = "moderate republican",
                                         `5` = "conservative republican"),
                     region4 = recode(region4,
                                      `1` = "northeast",
                                      `2` = "midwest",
                                      `3` = "south",
                                      `4` = "west"),
                     employment = recode(employment,
                                         `1` = "Working/as a paid employee",
                                         `2` = "Working/selfemploye",
                                         `3` = "Not working/temporary",
                                         `4` = "Not working/looking",
                                         `5` = "Not working/retired",
                                         `6` = "Not working/disabled",
                                         `7` = "Not working/other"))

glimpse(survey_data)
# get summary statistics
summarise(survey_data, 
          count=n(), 
          min_worry=min(worry_cont),
          quantile25_worry=quantile(worry_cont, .25, na.rm = TRUE),
          mean_worry=mean(worry_cont), 
          quantile75_worry=quantile(worry_cont, .75, na.rm = TRUE),
          max_worry=max(worry_cont),
          min_happening=min(happening_cont),
          quantile25_happening=quantile(happening_cont, .25, na.rm = TRUE),
          mean_happening=mean(happening_cont),
          quantile75_happening=quantile(happening_cont, .75, na.rm = TRUE),
          max_happening=max(happening_cont))
summarise(survey_data,
          mean_happening = mean(happening))
# get summary statistics per group
grouped_data = group_by(survey_data, year)

summarise(grouped_data, 
          count=n(), 
          min_worry=min(worry_cont),
          quantile25_worry=quantile(worry_cont, .25, na.rm = TRUE),
          mean_worry=mean(worry_cont), 
          quantile75_worry=quantile(worry_cont, .75, na.rm = TRUE),
          max_worry=max(worry_cont),
          min_happening=min(happening_cont),
          quantile25_happening=quantile(happening_cont, .25, na.rm = TRUE),
          mean_happening=mean(happening_cont),
          quantile75_happening=quantile(happening_cont, .75, na.rm = TRUE),
          max_happening=max(happening_cont))
# split columns
survey_data = separate(survey_data,
                       col = employment,
                       into = c('working', 'working_type'),
                       sep = '/',
                       remove = FALSE)
head(survey_data)
head(select(survey_data, employment, working, working_type), 10)
# you can also create a new column via mutate + case_when
survey_data = mutate(survey_data,
                     working_recoded = case_when(working == "Not working" & working_type == 'looking' ~ 0,
                                                 working == "Not working" & working_type != 'looking' ~ 1,
                                                 working == "Working" ~ 2))
                     
head(survey_data)
# get summary statistics per group
grouped_data = group_by(survey_data, working)

summarise(grouped_data, 
          count=n(), 
          min_worry=min(worry_cont),
          quantile25_worry=quantile(worry_cont, .25, na.rm = TRUE),
          mean_worry=mean(worry_cont), 
          quantile75_worry=quantile(worry_cont, .75, na.rm = TRUE),
          max_worry=max(worry_cont),
          min_happening=min(happening_cont),
          quantile25_happening=quantile(happening_cont, .25, na.rm = TRUE),
          mean_happening=mean(happening_cont),
          quantile75_happening=quantile(happening_cont, .75, na.rm = TRUE),
          max_happening=max(happening_cont))
# first filter, and then get summary statistics per group
not_working_data = filter(survey_data, working == "Not working")

grouped_data = group_by(not_working_data, working_type)

summarise(grouped_data, 
          count=n(), 
          min_worry=min(worry_cont),
          quantile25_worry=quantile(worry_cont, .25, na.rm = TRUE),
          mean_worry=mean(worry_cont), 
          quantile75_worry=quantile(worry_cont, .75, na.rm = TRUE),
          max_worry=max(worry_cont),
          min_happening=min(happening_cont),
          quantile25_happening=quantile(happening_cont, .25, na.rm = TRUE),
          mean_happening=mean(happening_cont),
          quantile75_happening=quantile(happening_cont, .75, na.rm = TRUE),
          max_happening=max(happening_cont))
# merge two columns
survey_data = unite(survey_data,
                    "race_gender",
                    race, 
                    gender,
                    sep = "_",
                    remove=FALSE)

head(select(survey_data, race, gender, race_gender))
# get summary statistics per group
grouped_data = group_by(survey_data, race_gender)

summarise(grouped_data, 
          count=n(), 
          min_worry=min(worry_cont),
          quantile25_worry=quantile(worry_cont, .25, na.rm = TRUE),
          mean_worry=mean(worry_cont), 
          quantile75_worry=quantile(worry_cont, .75, na.rm = TRUE),
          max_worry=max(worry_cont),
          min_happening=min(happening_cont),
          quantile25_happening=quantile(happening_cont, .25, na.rm = TRUE),
          mean_happening=mean(happening_cont),
          quantile75_happening=quantile(happening_cont, .75, na.rm = TRUE),
          max_happening=max(happening_cont))
# similar results with 2 grouping variables in this case:

# get summary statistics per group
grouped_data = group_by(survey_data, race, gender)

summarise(grouped_data, 
          count=n(), 
          min_worry=min(worry_cont),
          quantile25_worry=quantile(worry_cont, .25, na.rm = TRUE),
          mean_worry=mean(worry_cont), 
          quantile75_worry=quantile(worry_cont, .75, na.rm = TRUE),
          max_worry=max(worry_cont),
          min_happening=min(happening_cont),
          quantile25_happening=quantile(happening_cont, .25, na.rm = TRUE),
          mean_happening=mean(happening_cont),
          quantile75_happening=quantile(happening_cont, .75, na.rm = TRUE),
          max_happening=max(happening_cont))<jupyter_output>`summarise()` has grouped output by 'race'. You can override using the `.groups` argument.<jupyter_text>Do everything again but with pipes:<jupyter_code>survey_data = read_spss("https://github.com/laurafontanesi/r-seminar/raw/main/data/ccam_original.sav")

survey_data = survey_data %>%
    select(all_of(interesting_columns)) %>%
    filter(happening > 0, 
           cause_recoded > 0, 
           sci_consensus > 0, 
           worry > 0)  %>%
    mutate_all(as.integer) %>%
    mutate(happening_cont = happening + rnorm(mean=0, sd=.5, n=n()),
           worry_cont = worry + rnorm(mean=0, sd=.5, n=n()),
           year=recode(year, 
                       `1` = 2008, 
                       `2` = 2010,
                       `3` = 2011,
                       `4` = 2012, 
                       `5` = 2013,
                       `6` = 2014,
                       `7` = 2015,
                       `8` = 2016,
                       `9` = 2017),
           happening_labels=recode(happening,
                                   `1` = "no",
                                   `2` = "dont know",
                                   `3` = "yes"),
           cause_recoded=recode(cause_recoded,
                                `1` = "dont know",
                                `2` = "other",
                                `3` = "not happening",
                                `4` = "natural",
                                `5` = "human",
                                `6` = "natural and human"),
           sci_consensus=recode(sci_consensus,
                                `1` = "dont know",
                                `2` = "disagreement",
                                `3` = "not happening",
                                `4` = "happening"),
           gender=recode(gender,
                         `1` = "male",
                         `2` = "female"),
           age_category_labels=recode(age_category,
                                      `1` = "18-34",
                                      `2` = "35-54",
                                      `3` = "55+"),
           educ_category_labels=recode(educ_category,
                                       `1` = "no highschool",
                                       `2` = "highschool",
                                       `3` = "college",
                                       `4` = "bachelor or higher"),
           income_category_labels=recode(income_category,
                        `1` = "less 50000",
                        `2` = "50000-99999",
                        `3` = "more 100000"),
           race=recode(race,
                      `1` = 'white non hisp',
                      `2` = 'black non hisp',
                      `3` = 'other non hisp',
                      `4` = 'hisp'),
           party_x_ideo=recode(party_x_ideo,
                               `-2` = "no interest",
                               `-1` = "refused",
                               `1` = "liberal democrat",
                               `2` = "moderate democrate",
                               `3` = "independent",
                               `4` = "moderate republican",
                               `5` = "conservative republican"),
           region4 = recode(region4,
                            `1` = "northeast",
                            `2` = "midwest",
                            `3` = "south",
                            `4` = "west"),
           employment = recode(employment,
                               `1` = "Working/as a paid employee",
                               `2` = "Working/selfemploye",
                               `3` = "Not working/temporary",
                               `4` = "Not working/looking",
                               `5` = "Not working/retired",
                               `6` = "Not working/disabled",
                               `7` = "Not working/other")) %>%
    separate(col = employment,
             into = c('working', 'working_type'),
             sep = '/') %>%
    glimpse()  %>%
    filter(working == "Not working")  %>%
    group_by(working_type) %>%
    summarise(count=n(), 
              min_worry=min(worry_cont),
              quantile25_worry=quantile(worry_cont, .25),
              mean_worry=mean(worry_cont), 
              quantile75_worry=quantile(worry_cont, .75),
              max_worry=max(worry_cont),
              min_happening=min(happening_cont),
              quantile25_happening=quantile(happening_cont, .25),
              mean_happening=mean(happening_cont),
              quantile75_happening=quantile(happening_cont, .75),
              max_happening=max(happening_cont))
survey_data<jupyter_output><empty_output><jupyter_text>Wide and long format<jupyter_code># Create fake data:
N = 15
mean_score_students = runif(N, 5, 10)
fake_data_wide = tibble( # same as data.frame but in tidyverse!
    student = 1:N,
    age = round(rnorm(N, 30, 5)),
    score_wpa1 = mean_score_students,
    score_wpa2 = mean_score_students*0.9 + rnorm(N, 0, 0.1),
    score_wpa3 = mean_score_students*0.5 + rnorm(N, 0, 1),
    score_wpa4 = mean_score_students*0.7 + rnorm(N, 0, 0.2)
)

fake_data_wide
# transform from wide to long format
fake_data_long = gather(fake_data_wide,
                        key='wpa',
                        value='score',
                        score_wpa1:score_wpa4)
head(fake_data_long, 30)
# reorder based on student
head(arrange(fake_data_long, student), 20)
# bring back
fake_data_wide = spread(fake_data_long,
                        key='wpa',
                        value='score')
head(fake_data_wide, 5)<jupyter_output><empty_output><jupyter_text>Join dataframes<jupyter_code># Create fake data:
mean_score_students = runif(N, 5, 10)
fake_data_wide_second_batch = tibble( # same as data.frame but in tidyverse!
    student = (N+1):(2*N),
    age = round(rnorm(N, 30, 5)),
    score_wpa1 = mean_score_students,
    score_wpa2 = mean_score_students*0.9 + rnorm(N, 0, 0.1),
    score_wpa3 = mean_score_students*0.5 + rnorm(N, 0, 1)
)
fake_data_wide_second_batch
fake_data_wide = bind_rows(fake_data_wide, fake_data_wide_second_batch)
fake_data_wide
# new variables
new_info = tibble(
    student = 1:(2*N),
    score_wpa5 = mean_score_students*0.5 + rnorm(2*N, 0, 1),
    gender = rbinom(2*N, 1, .5)
)
new_info
fake_data_wide = bind_cols(fake_data_wide,
                           new_info)
fake_data_wide<jupyter_output>New names:
* student -> student...1
* student -> student...7<jupyter_text>A better way to join dataframes is to use the "join" functions:<jupyter_code># Create fake data:
N = 15
mean_score_students = runif(N, 5, 10)
first_batch = tibble(
    student = 1:N,
    age = round(rnorm(N, 30, 5)),
    score_wpa1 = mean_score_students,
    score_wpa2 = mean_score_students*0.9 + rnorm(N, 0, 0.1),
    score_wpa3 = mean_score_students*0.5 + rnorm(N, 0, 1),
    score_wpa4 = mean_score_students*0.7 + rnorm(N, 0, 0.2)
)
M = 5
mean_score_students = runif(M, 8, 10)
second_batch = tibble( 
    student = (N+1):(N+M),
    age = round(rnorm(M, 30, 5)),
    score_wpa1 = mean_score_students,
    score_wpa2 = mean_score_students*0.9 + rnorm(M, 0, 0.1),
    score_wpa5 = mean_score_students*0.5 + rnorm(M, 0, 1)
)
new_info = tibble(
    student = 1:(N+M),
    score_wpa6 = runif(N+M, 6, 10)*0.5 + rnorm(N+M, 0, 1),
    gender = rbinom(N+M, 1, .5)
)
first_batch
second_batch
new_info
full_join(first_batch, second_batch, by='student')
full_join(first_batch, second_batch, by=c('student', 'age', "score_wpa1", "score_wpa2"))
left_join(first_batch, new_info, by=c('student'))
right_join(new_info, second_batch, by=c('student'))<jupyter_output><empty_output><jupyter_text>3. Now it's your turnNow you will analyze data from Matthews et al. (2016): Why do we overestimate others' willingness to pay? The purpose of this research was to test if our beliefs about other people's affluence (i.e.; wealth) affect how much we think they will be willing to pay for items. You can find the full paper at http://journal.sjdm.org/15/15909/jdm15909.pdf.**Variables description:**Here are descriptions of the data variables (taken from the author's dataset notes available at http://journal.sjdm.org/15/15909/Notes.txt)- `id`: participant id code- `gender`: participant gender. 1 = male, 2 = female- `age`: participant age- `income`: participant annual household income on categorical scale with 8 categorical options: Less than 5,000; 15,001–25,000; 25,001–35,000; 35,001–50,000; 50,001–75,000; 75,001–100,000; 100,001–150,000; greater than 150,000.- `p1-p10`: whether the "typical" survey respondent would pay more (coded 1) or less (coded 0) than oneself, for each of the 10 products - `task`: whether the participant had to judge the proportion of other people who "have more money than you do" (coded 1) or the proportion who "have less money than you do" (coded 0)- `havemore`: participant's response when task = 1- `haveless`: participant's response when task = 0- `pcmore`: participant's estimate of the proportion of people who have more than they do (calculated as 100-haveless when task=0)<jupyter_code># load some data
matthews_data = read_csv('https://raw.githubusercontent.com/laurafontanesi/r-seminar/master/data/data_wpa4.csv')

demographics = read_csv("https://raw.githubusercontent.com/laurafontanesi/r-seminar/master/data/matthews_demographics.csv")

glimpse(matthews_data)

glimpse(demographics)<jupyter_output>[36m──[39m [1m[1mColumn specification[1m[22m [36m──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────[39m
cols(
  id = [31mcol_character()[39m,
  gender = [32mcol_double()[39m,
  age = [32mcol_double()[39m,
  income = [32mcol_double()[39m,
  p1 = [32mcol_double()[39m,
  p2 = [32mcol_double()[39m,
  p3 = [32mcol_double()[39m,
  p4 = [32mcol_double()[39m,
  p5 = [32mcol_double()[39m,
  p6 = [32mcol_double()[39m,
  p7 = [32mcol_double()[39m,
  p8 = [32mcol_double()[39m,
  p9 = [32mcol_double()[39m,
  p10 = [32mcol_double()[39m,
  task = [32mcol_double()[39m,
  havemore = [32mcol_double()[39m,
  haveless = [32mcol_double()[39m,
  pcmore = [32mcol_double()[39m
)


Warning message:
“Missing column names filled in: 'X1' [1]”

[36m──[39m [1m[1mColumn specification[1m[22m [36m────────────────────────────────[...]
{'fraction_non_alphanumeric': 0.12569960898566282, 'fraction_numerical': 0.03871808632983209, 'mean_word_length': 1.9317824230164082, 'pattern_counts': {'":': 0, '<': 40, '<?xml version=': 0, '>': 60, 'https://': 7, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 144, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '580785', 'license': 'MIT', 'n_tokens_mistral': 8905, 'n_tokens_neox': 7784, 'n_words': 1834, 'path': 'notebooks/wpa4.ipynb', 'repo_name': 'laurafontanesi/r-seminar', 'size': '26095'}