## A.7 Solutions (07)

Here are the solutions of the exercises on detecting and creating tidy data of Chapter 7 (Section 7.4).

### A.7.1 Exercise 1

#### Four messes and one tidy table

This exercise asks you to inspect some tables and turn some messy ones into tidy data.

The four tables t_1 to t_4 are available in the ds4psy package . Alternatively, you can load csv-versions of these files from the following links:

For each of these files:

• Describe the data (i.e., its dimensions, observations, variables, DVs and IVs).

• Transform any non-tidy table one into a tidy one.

• Verify the equality of the resulting tidy tables.

#### Solution

Get data files:

# from ds4psy:
t_1 <- ds4psy::t_1
t_2 <- ds4psy::t_2
t_3 <- ds4psy::t_3
t_4 <- ds4psy::t_4

1. t_1

t_1 <- ds4psy::t_1  # from ds4psy package
dim(t_1)  # 8 x 9
#> [1] 8 9

knitr::kable(t_1, caption = "Table t_1.")
Table A.19: Table t_1.
Ann f 31 color shape red 73 circle 64
Bea f 18 shape color blue 18 circle 42
Cat f 42 color shape red 31 square 41
Deb f 18 shape color blue 35 square 51
Ed m 21 color shape red 71 circle 44
Fred m 63 shape color blue 56 circle 63
Gary m 22 color shape red 60 square 98
Hans m 31 shape color blue 40 square 41

Analysis/Description of t_1:

• t_1 is an 8 x 9 table, containing the data from 8 people (rows). Each person is described by her/his name, gender, and age.

• The 2 task_# columns indicate the order in which the person performed 2 tasks (color vs. shape).

• Both tasks exist in 2 versions (color in red or blue, shape in circle or square).

• The 2 dependent variables appear to be the times (in seconds or minutes?) the person spent on each of those tasks.

Note:

• The task_2 column is redundant, as task_1 would suffice to specify the order of both tasks (or vice versa).

• A tidy dataset would feature only 1 DV of time and an additional key variable that specifies the task_type (color vs. shape).

Tidying t_1:

t_1_tidy <- t_1 %>%
gather(key = "key", value = "time", color_time, shape_time) %>%
separate(key, c("type", "rest"), sep = "_") %>%
arrange(name)
t_1_tidy
#> # A tibble: 16 × 8
#>    name  gender   age task_1 color shape  type   time
#>    <chr> <chr>  <dbl> <chr>  <chr> <chr>  <chr> <dbl>
#>  1 Ann   f         31 color  red   circle color    73
#>  2 Ann   f         31 color  red   circle shape    64
#>  3 Bea   f         18 shape  blue  circle color    18
#>  4 Bea   f         18 shape  blue  circle shape    42
#>  5 Cat   f         42 color  red   square color    31
#>  6 Cat   f         42 color  red   square shape    41
#>  7 Deb   f         18 shape  blue  square color    35
#>  8 Deb   f         18 shape  blue  square shape    51
#>  9 Ed    m         21 color  red   circle color    71
#> 10 Ed    m         21 color  red   circle shape    44
#> 11 Fred  m         63 shape  blue  circle color    56
#> 12 Fred  m         63 shape  blue  circle shape    63
#> 13 Gary  m         22 color  red   square color    60
#> 14 Gary  m         22 color  red   square shape    98
#> 15 Hans  m         31 shape  blue  square color    40
#> 16 Hans  m         31 shape  blue  square shape    41

2. t_2

t_2 <- ds4psy::t_2  # from ds4psy package
dim(t_2)  # 8 x 5
#> [1] 8 5

knitr::kable(t_2, caption = "Table t_2.")
Table A.20: Table t_2.
Ann f:31 color red = 73 circle = 64
Bea f:18 shape blue = 18 circle = 42
Cat f:42 color red = 31 square = 41
Deb f:18 shape blue = 35 square = 51
Ed m:21 color red = 71 circle = 44
Fred m:63 shape blue = 56 circle = 63
Gary m:22 color red = 60 square = 98
Hans m:31 shape blue = 40 square = 41

Analysis/Description of t_2:

• t_2 is an 8 x 5 table, and essentially are more compact version of t_1 (with fewer columns).

• The variables details, color_time and shape_time each contain 2 variables and corresponding values.

• The order of tasks is specified by task_1 only (i.e., the 2nd task is implied as the other one).

Tidying t_2:

t_2_1 <- t_2 %>%
separate(details, c("gender", "age"), sep = ":") %>%
separate(color_time, c("color", "c_time"), sep = " = ") %>%
separate(shape_time, c("shape", "s_time"), sep = " = ")
# t_2_1

t_2_tidy <- t_2_1 %>%
gather(key = "key", value = "time", c_time, s_time) %>%
separate(key, c("type", "rest"), sep = "_") %>%
arrange(name)
t_2_tidy
#> # A tibble: 16 × 8
#>    name  gender age   task_1 color shape  type  time
#>    <chr> <chr>  <chr> <chr>  <chr> <chr>  <chr> <chr>
#>  1 Ann   f      31    color  red   circle c     73
#>  2 Ann   f      31    color  red   circle s     64
#>  3 Bea   f      18    shape  blue  circle c     18
#>  4 Bea   f      18    shape  blue  circle s     42
#>  5 Cat   f      42    color  red   square c     31
#>  6 Cat   f      42    color  red   square s     41
#>  7 Deb   f      18    shape  blue  square c     35
#>  8 Deb   f      18    shape  blue  square s     51
#>  9 Ed    m      21    color  red   circle c     71
#> 10 Ed    m      21    color  red   circle s     44
#> 11 Fred  m      63    shape  blue  circle c     56
#> 12 Fred  m      63    shape  blue  circle s     63
#> 13 Gary  m      22    color  red   square c     60
#> 14 Gary  m      22    color  red   square s     98
#> 15 Hans  m      31    shape  blue  square c     40
#> 16 Hans  m      31    shape  blue  square s     41

Verify the equality of tidy tables:
Let’s make sure that t_1_tidy and t_2_tidy really are equal…

## Check equality:
# t_1_tidy
# t_2_tidy
all.equal(t_1_tidy, t_2_tidy)  # => different variable types
#> [1] "Component \"age\": Modes: numeric, character"
#> [2] "Component \"age\": target is numeric, current is character"
#> [3] "Component \"type\": 16 string mismatches"
#> [4] "Component \"time\": Modes: numeric, character"
#> [5] "Component \"time\": target is numeric, current is character"

# Create equality:
# (a) Change variable types in t_2_tidy:
t_2_tidy$gender <- as.character(t_2_tidy$gender)
t_2_tidy$age <- as.numeric(t_2_tidy$age)
t_2_tidy$color <- as.character(t_2_tidy$color)
t_2_tidy$shape <- as.character(t_2_tidy$shape)
t_2_tidy$time <- as.numeric(t_2_tidy$time)

# (b) Rename values of type:
t_2_tidy$type[t_2_tidy$type == "c"] <- "color"
t_2_tidy$type[t_2_tidy$type == "s"] <- "shape"

# t_2_tidy

## Check equality:
# t_1_tidy
# t_2_tidy
all.equal(t_1_tidy, t_2_tidy)  # => TRUE
#> [1] TRUE

3. t_3

t_3 <- ds4psy::t_3  # from ds4psy package
dim(t_3)  # 16 x 6
#> [1] 16  6

knitr::kable(t_3, caption = "Table t_3.")
Table A.21: Table t_3.
name gender age position task time
Ann f 31 1 red 73
Ann f 31 2 circle 64
Bea f 18 1 circle 42
Bea f 18 2 blue 18
Cat f 42 1 red 31
Cat f 42 2 square 41
Deb f 18 1 square 51
Deb f 18 2 blue 35
Ed m 21 1 red 71
Ed m 21 2 circle 44
Fred m 63 1 circle 63
Fred m 63 2 blue 56
Gary m 22 1 red 60
Gary m 22 2 square 98
Hans m 31 1 square 41
Hans m 31 2 blue 40

Analysis/Description of t_3:

• t_3 is a 16 x 6 table.

• Each person appears twice, and task position, task type, and task time are described by 3 variables.

• The task variable contains implicit information on task type:

• values of “red” and “blue” are of type “color,”
• values of “circle” and “square” are of type “shape.”

Tidying t_3:

To tidy t_3, we need to flesh out the task variable into multiple columns that denote the type, color and shape of each task.

# t_3

# Create a new variable task type:
t_3$type <- NA # initialize variable t_3$type[t_3$task == "red" | t_3$task == "blue"]      <- "color"
t_3$type[t_3$task == "circle" | t_3$task == "square"] <- "shape" # Create 2 new variables color and shape: t_3$color <- NA  # initialize variable
t_3$shape <- NA # initialize variable t_3$color[t_3$task == "red" | t_3$task == "blue"] <- t_3$task[t_3$task == "red" | t_3$task == "blue"] t_3$shape[t_3$task == "circle" | t_3$task == "square"] <- t_3$task[t_3$task == "circle" | t_3$task == "square"] t_3_tidy <- t_3 %>% select(name:position, type:shape, time) # sort columns and drop task variable t_3_tidy #> # A tibble: 16 × 8 #> name gender age position type color shape time #> <chr> <chr> <dbl> <dbl> <chr> <chr> <chr> <dbl> #> 1 Ann f 31 1 color red <NA> 73 #> 2 Ann f 31 2 shape <NA> circle 64 #> 3 Bea f 18 1 shape <NA> circle 42 #> 4 Bea f 18 2 color blue <NA> 18 #> 5 Cat f 42 1 color red <NA> 31 #> 6 Cat f 42 2 shape <NA> square 41 #> 7 Deb f 18 1 shape <NA> square 51 #> 8 Deb f 18 2 color blue <NA> 35 #> 9 Ed m 21 1 color red <NA> 71 #> 10 Ed m 21 2 shape <NA> circle 44 #> 11 Fred m 63 1 shape <NA> circle 63 #> 12 Fred m 63 2 color blue <NA> 56 #> 13 Gary m 22 1 color red <NA> 60 #> 14 Gary m 22 2 shape <NA> square 98 #> 15 Hans m 31 1 shape <NA> square 41 #> 16 Hans m 31 2 color blue <NA> 40 4. t_4 t_4 <- ds4psy::t_4 # from ds4psy package # t_4 <- readr::read_csv(t_4_path) # online path dim(t_4) # 16 x 8 #> [1] 16 8 knitr::kable(t_4, caption = "Table t_4.") Table A.22: Table t_4. name gender age position blue red circle square Ann f 31 1 NA 73 NA NA Ann f 31 2 NA NA 64 NA Bea f 18 1 NA NA 42 NA Bea f 18 2 18 NA NA NA Cat f 42 1 NA 31 NA NA Cat f 42 2 NA NA NA 41 Deb f 18 1 NA NA NA 51 Deb f 18 2 35 NA NA NA Ed m 21 1 NA 71 NA NA Ed m 21 2 NA NA 44 NA Fred m 63 1 NA NA 63 NA Fred m 63 2 56 NA NA NA Gary m 22 1 NA 60 NA NA Gary m 22 2 NA NA NA 98 Hans m 31 1 NA NA NA 41 Hans m 31 2 40 NA NA NA Analysis/Description of t_4: • t_4 is a 16 x 8 table. • Each person appears twice, and the task position is encoded by a variable. • The task time information is distributed across 4 columns (blue, red, circle, and square), which also encode task type and color or shape. Tidying t_4: Here are 2 different solutions for tidying t_4: t_4 #> # A tibble: 16 × 8 #> name gender age position blue red circle square #> <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> #> 1 Ann f 31 1 NA 73 NA NA #> 2 Ann f 31 2 NA NA 64 NA #> 3 Bea f 18 1 NA NA 42 NA #> 4 Bea f 18 2 18 NA NA NA #> 5 Cat f 42 1 NA 31 NA NA #> 6 Cat f 42 2 NA NA NA 41 #> 7 Deb f 18 1 NA NA NA 51 #> 8 Deb f 18 2 35 NA NA NA #> 9 Ed m 21 1 NA 71 NA NA #> 10 Ed m 21 2 NA NA 44 NA #> 11 Fred m 63 1 NA NA 63 NA #> 12 Fred m 63 2 56 NA NA NA #> 13 Gary m 22 1 NA 60 NA NA #> 14 Gary m 22 2 NA NA NA 98 #> 15 Hans m 31 1 NA NA NA 41 #> 16 Hans m 31 2 40 NA NA NA # Solution 1: Gather 4 time variables + filter out non-NA cases: -------- t_4_mod <- t_4 %>% gather(key = "task", value = "time", blue:square) %>% filter(!is.na(time)) %>% arrange(name, position) t_4_mod #> # A tibble: 16 × 6 #> name gender age position task time #> <chr> <chr> <dbl> <dbl> <chr> <dbl> #> 1 Ann f 31 1 red 73 #> 2 Ann f 31 2 circle 64 #> 3 Bea f 18 1 circle 42 #> 4 Bea f 18 2 blue 18 #> 5 Cat f 42 1 red 31 #> 6 Cat f 42 2 square 41 #> 7 Deb f 18 1 square 51 #> 8 Deb f 18 2 blue 35 #> 9 Ed m 21 1 red 71 #> 10 Ed m 21 2 circle 44 #> 11 Fred m 63 1 circle 63 #> 12 Fred m 63 2 blue 56 #> 13 Gary m 22 1 red 60 #> 14 Gary m 22 2 square 98 #> 15 Hans m 31 1 square 41 #> 16 Hans m 31 2 blue 40 # Verify that t_4_mod is equal to t_3 (from above): all.equal(t_3, t_4_mod) #> [1] "Attributes: < Length mismatch: comparison on first 2 components >" #> [2] "Attributes: < Component \"class\": 3 string mismatches >" #> [3] "Length mismatch: comparison on first 6 components" # From here on, cleaning t_4_mod is equal to cleaning t_3 (see above). # Solution 2: Fleshing out info in 4 time columns by hand (base R): -------- t_4[is.na(t_4)] <- 0 # replace NA values by values of 0 t_4$time <- t_4$blue + t_4$red + t_4$circle + t_4$square

# Create 2 new variables color and shape:
t_4$color <- NA # initialize variable t_4$shape <- NA  # initialize variable
t_4$color[t_4$blue > 0] <- "blue"
t_4$color[t_4$red > 0] <- "red"
t_4$shape[t_4$circle > 0] <- "circle"
t_4$shape[t_4$square > 0] <- "square"

# Create a new variable task type:
t_4$type <- NA # initialize variable t_4$type[!is.na(t_4$color)] <- "color" t_4$type[!is.na(t_4$shape)] <- "shape" t_4_tidy <- t_4 %>% select(name:position, type, color, shape, time) t_4_tidy #> # A tibble: 16 × 8 #> name gender age position type color shape time #> <chr> <chr> <dbl> <dbl> <chr> <chr> <chr> <dbl> #> 1 Ann f 31 1 color red <NA> 73 #> 2 Ann f 31 2 shape <NA> circle 64 #> 3 Bea f 18 1 shape <NA> circle 42 #> 4 Bea f 18 2 color blue <NA> 18 #> 5 Cat f 42 1 color red <NA> 31 #> 6 Cat f 42 2 shape <NA> square 41 #> 7 Deb f 18 1 shape <NA> square 51 #> 8 Deb f 18 2 color blue <NA> 35 #> 9 Ed m 21 1 color red <NA> 71 #> 10 Ed m 21 2 shape <NA> circle 44 #> 11 Fred m 63 1 shape <NA> circle 63 #> 12 Fred m 63 2 color blue <NA> 56 #> 13 Gary m 22 1 color red <NA> 60 #> 14 Gary m 22 2 shape <NA> square 98 #> 15 Hans m 31 1 shape <NA> square 41 #> 16 Hans m 31 2 color blue <NA> 40 Verify the equality of tidy tables: Let’s make sure that t_3_tidy and t_4_tidy really are equal… # Verify equality: -------- all.equal(t_3_tidy, t_4_tidy) #> [1] TRUE ### A.7.2 Exercise 2 #### Moving stocks from wide to long to wide Let’s enter and transform some stock-related data.74 The following table shows the start and end price of three stocks on three days (d1, d2, d3): Table A.23: Start and end prices of 3 shares on 3 days. stock d1_start d1_end d2_start d2_end d3_start d3_end Amada 2.5 3.6 3.5 4.2 4.4 2.8 Betix 3.3 2.9 3.0 2.1 2.3 2.5 Cevis 4.2 4.8 4.6 3.1 3.2 3.7 1. Create a tibble st that contains this data in this (wide) format. 2. Transform st into a longer table st_long that contains 18 rows and only one numeric variable for all stock prices. Adjust this table so that the day and time appear as 2 separate columns. 3. Create a (line) graph that shows the three stocks’ end prices (on the y-axis) over the three days (on the x-axis). 4. Spread st_long into a wider table that contains start and end prices as two distinct variables (columns) for each stock and day. #### Solution # library(tidyverse) # 1. Enter stock data (in wide format) as a tibble: ---- st <- tribble( ~stock, ~d1_start, ~d1_end, ~d2_start, ~d2_end, ~d3_start, ~d3_end, #-----|----------|--------|----------|--------|----------|--------| "Amada", 2.5, 3.6, 3.5, 4.2, 4.4, 2.8, "Betix", 3.3, 2.9, 3.0, 2.1, 2.3, 2.5, "Cevis", 4.2, 4.8, 4.6, 3.1, 3.2, 3.7 ) dim(st) #> [1] 3 7 ## Note data structure: ## 2 nested factors: day (1 to 3), type (start or end). # 2. Change from wide to long format ---- # that contains the day (d1, d2, d3) and type (start vs. end) as separate columns: st_long <- st %>% gather(d1_start:d3_end, key = "key", value = "val") %>% separate(key, into = c("day", "time")) %>% arrange(stock, day, time) # optional: arrange rows st_long #> # A tibble: 18 × 4 #> stock day time val #> <chr> <chr> <chr> <dbl> #> 1 Amada d1 end 3.6 #> 2 Amada d1 start 2.5 #> 3 Amada d2 end 4.2 #> 4 Amada d2 start 3.5 #> 5 Amada d3 end 2.8 #> 6 Amada d3 start 4.4 #> 7 Betix d1 end 2.9 #> 8 Betix d1 start 3.3 #> 9 Betix d2 end 2.1 #> 10 Betix d2 start 3 #> 11 Betix d3 end 2.5 #> 12 Betix d3 start 2.3 #> 13 Cevis d1 end 4.8 #> 14 Cevis d1 start 4.2 #> 15 Cevis d2 end 3.1 #> 16 Cevis d2 start 4.6 #> 17 Cevis d3 end 3.7 #> 18 Cevis d3 start 3.2 # 3. Plot the end values (on the y-axis) of the 3 stocks over 3 days (x-axis): ---- st_long %>% filter(time == "end") %>% ggplot(aes(x = day, y = val, color = stock, shape = stock)) + geom_point(size = 5) + geom_line(aes(group = stock), size = 1, alpha = 2/3) + labs(title = "End prices of each stock (by day)", x = "Day", y = "End price", shape = "Stock:", color = "Stock:") + # scale_color_manual(values = c("steelblue", "firebrick", "forestgreen")) + scale_color_manual(values = usecol(pal = c(Seeblau, Seegruen, Pinky))) + # assuming library("unikn") theme_bw() # 4. Change st_long into a wider format that lists start and end as 2 distinct variables (columns): ---- st_long %>% spread(key = time, value = val) %>% mutate(day_nr = parse_integer(str_sub(day, 2, 2))) # optional: get day_nr as integer variable #> # A tibble: 9 × 5 #> stock day end start day_nr #> <chr> <chr> <dbl> <dbl> <int> #> 1 Amada d1 3.6 2.5 1 #> 2 Amada d2 4.2 3.5 2 #> 3 Amada d3 2.8 4.4 3 #> 4 Betix d1 2.9 3.3 1 #> 5 Betix d2 2.1 3 2 #> 6 Betix d3 2.5 2.3 3 #> 7 Cevis d1 4.8 4.2 1 #> 8 Cevis d2 3.1 4.6 2 #> 9 Cevis d3 3.7 3.2 3 ### A.7.3 Exercise 3 In this exercise, we use tidyr to solve a problem that prevented us from creating a plot in the tibble chapter (Chapter 5). #### A posPsy tibble reloaded In Exercise 3 of Chapter 5, we created a tibble of mean depression scores (my_tbl and my_tbl_2) in 2 different ways (by entering the data directly into R with tibble() and by using dplyr to compute a summary table from the posPsy_wide data). Table A.24: Mean depression scores by intervention and occasion. intervention mn_cesd_0 mn_cesd_1 mn_cesd_2 mn_cesd_3 mn_cesd_4 mn_cesd_5 1 15.1 15.3 13.6 12.0 11.2 13.5 2 16.2 14.6 11.4 12.5 13.4 14.6 3 16.1 12.3 14.8 13.9 14.9 13.0 4 12.8 9.9 9.5 9.1 7.7 10.2 See Section B.1 of Appendix B for details on the data. When trying to create a plot that shows the trends of mean depression scores (over different occasions by intervention) we noted that it is impossible to directly plot the values of my_tbl_2. For plotting the mean depression scores with ggplot we would need these scores as 1 dependent variable, rather than as six different variables. Earlier, we solved this problem by creating an alternative tibble my_tbl_3 — which expressed mean_cesd as a function of occasion and intervention (in long format) — from the raw data in posPsy_long. Given our new skills in tidyr, we now are in a position to transform my_tbl_2 (or my_tbl) into the required format of my_tbl_3. Thus, your task is: 1. Re-create one of the original tibbles (either my_tbl or my_tbl_2) and use tidyr to transform it into the long format of my_tbl_3. #### Solution # Load data: ---- posPsy_wide <- ds4psy::posPsy_wide # from ds4psy package # posPsy_wide <- read_csv(file = "http://rpository.com/ds4psy/data/posPsy_data_wide.csv") # online # dim(posPsy_wide) # 295 294 # Re-create tibble: ---- my_tbl_2 <- posPsy_wide %>% group_by(intervention) %>% summarise(mn_cesd_0 = round(mean(cesdTotal.0, na.rm = TRUE), 1), mn_cesd_1 = round(mean(cesdTotal.1, na.rm = TRUE), 1), mn_cesd_2 = round(mean(cesdTotal.2, na.rm = TRUE), 1), mn_cesd_3 = round(mean(cesdTotal.3, na.rm = TRUE), 1), mn_cesd_4 = round(mean(cesdTotal.4, na.rm = TRUE), 1), mn_cesd_5 = round(mean(cesdTotal.5, na.rm = TRUE), 1) ) # my_tbl_2 # Transform from wide into long format: ---- my_tbl_3 <- my_tbl_2 %>% gather(key = "key", value = "mean_cesd", mn_cesd_0:mn_cesd_5) %>% separate(key, c("mn", "cesd", "occasion"), sep = "_") %>% select(occasion, intervention, mean_cesd) # my_tbl_3 # Change some variable types: my_tbl_3$occasion     <- as.integer(my_tbl_3$occasion) # as integer my_tbl_3$intervention <- as.factor(my_tbl_3$intervention) # as factor # Check: my_tbl_3 #> # A tibble: 24 × 3 #> occasion intervention mean_cesd #> <int> <fct> <dbl> #> 1 0 1 15.1 #> 2 0 2 16.2 #> 3 0 3 16.1 #> 4 0 4 12.8 #> 5 1 1 15.3 #> 6 1 2 14.6 #> 7 1 3 12.3 #> 8 1 4 9.9 #> 9 2 1 13.6 #> 10 2 2 11.4 #> # … with 14 more rows 1. Now do the reverse: Use the long version my_tbl_3 to (re-)create a wider version my_tbl_4 that is equal to my_tbl_2. #### Solution my_tbl_4 <- my_tbl_3 %>% spread(key = occasion, value = mean_cesd) my_tbl_4 #> # A tibble: 4 × 7 #> intervention 0 1 2 3 4 5 #> <fct> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> #> 1 1 15.1 15.3 13.6 12 11.2 13.5 #> 2 2 16.2 14.6 11.4 12.5 13.4 14.6 #> 3 3 16.1 12.3 14.8 13.9 14.9 13 #> 4 4 12.8 9.9 9.5 9.1 7.7 10.2 # Change names of some variables (columns 2:7): names(my_tbl_4)[2:ncol(my_tbl_4)] <- paste0("mn_cesd_", names(my_tbl_4)[2:ncol(my_tbl_4)]) # Change some variable types: my_tbl_4$intervention <- as.double(my_tbl_4$intervention) # back to number ## Verify equality: # my_tbl_4 # my_tbl_2 all.equal(my_tbl_4, my_tbl_2) #> [1] TRUE ### A.7.4 Exercise 4 #### Wide and long psychology In previous chapters, we have seen 2 sets of data for the positive psychology experiment: (See Section B.1 of Appendix B for details on the data.) Both of these datasets contain the same information, but one is in long format and one in wide format. With tidyr, we are able to transform the long format into wide format (and vice versa) on our own. #### 1. From long to wide • Load the file posPsy_AHI_CESD_corrected.csv into a tibble posPsy_long. To make things simpler, drop all columns except id, occasion, intervention, and ahiTotal. • Transform the resulting table from long to wide format (spreading ahiTotal values over different occasions). #### Solution ## Load data: ---- posPsy_long <- ds4psy::posPsy_long # from ds4psy package # posPsy_long <- read_csv(file = "http://rpository.com/ds4psy/data/posPsy_AHI_CESD_corrected.csv") # online dim(posPsy_long) # 990 x 50 #> [1] 990 50 ## Drop most colums: ---- df_long <- posPsy_long %>% select(id, occasion, intervention, ahiTotal) ## Check: # df_long dim(df_long) # 990 x 4 #> [1] 990 4 ## Spread to wide format: ---- df_wide <- df_long %>% spread(occasion, ahiTotal, convert = TRUE) ## Check: dim(df_wide) # 295 x 8 #> [1] 295 8 # Fix some variable names: names(df_wide)[3:8] <- paste0("occ_", names(df_wide)[3:8]) ## Check: df_wide #> # A tibble: 295 × 8 #> id intervention occ_0 occ_1 occ_2 occ_3 occ_4 occ_5 #> <dbl> <dbl> <int> <int> <int> <int> <int> <int> #> 1 1 4 63 73 NA NA NA NA #> 2 2 1 73 89 89 93 80 77 #> 3 3 4 77 NA 85 NA NA NA #> 4 4 3 60 67 NA 56 61 NA #> 5 5 2 41 41 47 47 47 NA #> 6 6 1 62 NA 67 66 NA NA #> 7 7 3 67 NA NA NA NA NA #> 8 8 2 59 45 38 48 43 NA #> 9 9 1 48 NA NA NA NA NA #> 10 10 2 58 NA NA NA NA NA #> # … with 285 more rows #### 2. From wide to long • Load the file posPsy_data_wide.csv into a tibble posPsy_wide and drop all variables that contain values of individual happiness or depression items (i.e., all score variables not containing “Total” in their names). • Then transform this wide format tibble into long format. Your result table should contain all demographic information (in separate columns), the type of scale (ahiTotal vs. cesdTotal), number of occasion (0 to 5), and the scale value (as dependent variable). Hint: First gather all Total variables into a single value variable, then separate the key column into 2 variables scale and occasion. #### Solution ## Load data: ---- posPsy_wide <- read_csv(file = "http://rpository.com/ds4psy/data/posPsy_data_wide.csv") # online dim(posPsy_wide) # 295 x 294 #> [1] 295 294 ## Drop unnecessary columns and gather: ---- df_long <- posPsy_wide %>% # drop unnecessary columns: select(id, intervention, sex, age, educ, income, contains("Total")) %>% gather(-id, -intervention, -sex, -age, -educ, -income, key = "score.occasion", value = "value") ## Check: dim(df_long) # 3540 x 8 #> [1] 3540 8 # df_long ## Separate key column score.occasion: ---- df_long <- df_long %>% separate(score.occasion, into = c("scale", "occasion")) ## Check: dim(df_long) # 3540 x 9 #> [1] 3540 9 # df_long # Fix some variables: df_long$occasion <- as.integer(df_long\$occasion)

df_long
#> # A tibble: 3,540 × 9
#>       id intervention   sex   age  educ income scale    occasion value
#>    <dbl>        <dbl> <dbl> <dbl> <dbl>  <dbl> <chr>       <int> <dbl>
#>  1     1            4     2    35     5      3 ahiTotal        0    63
#>  2     2            1     1    59     1      1 ahiTotal        0    73
#>  3     3            4     1    51     4      3 ahiTotal        0    77
#>  4     4            3     1    50     5      2 ahiTotal        0    60
#>  5     5            2     2    58     5      2 ahiTotal        0    41
#>  6     6            1     1    31     5      1 ahiTotal        0    62
#>  7     7            3     1    44     5      2 ahiTotal        0    67
#>  8     8            2     1    57     4      2 ahiTotal        0    59
#>  9     9            1     1    36     4      3 ahiTotal        0    48
#> 10    10            2     1    45     4      3 ahiTotal        0    58
#> # … with 3,530 more rows

### A.7.5 Exercise 5

#### Plotting relatives

This exercise relies on the main dataset for false positive psychology (see Section B.2 of Appendix B for details on the data and corresponding information):

• Load the data file falsePosPsy_all (78 x 19 variables):
# Import dataset
falsePosPsy_all <- ds4psy::falsePosPsy_all  # from ds4psy package
# falsePosPsy_all <- read_csv("http://rpository.com/ds4psy/data/falsePosPsy_all.csv")  # online

# Check:
dim(falsePosPsy_all)  # 78 x 19
#> [1] 78 19
# falsePosPsy_all

#### Parents’ age?

Let’s see whether we can detect some relationship between the parents’ age values.

1. Plot the relationship between the age values of each participant’s dad and mom (e.g., as a scatterplot).

#### Solution

df <- falsePosPsy_all  # copy the data

ggplot(df, aes(x = dad, y = mom)) +
geom_point(alpha = 1/2) +
geom_smooth(se = FALSE, color = unikn::Seeblau) +  # assuming library('unikn')
theme_bw()

1. Plot the age distributions of moms vs. dads (i.e., the distributions of age values among moms vs. dads) using histograms or geom_bar.

(Hint: As both ages are in two separate variables, you need to use gather to collect the ages of both parents in one variable.)

#### Solution

df %>%
gather(key = "parent", value = "age", dad, mom) %>%
select(study:aged365, parent, age) %>%
ggplot(aes(x = age, fill = parent)) +
geom_bar(position = "dodge") +
facet_wrap(~parent) +
scale_y_continuous(limits = c(0, 10),
breaks = c(1:10)) +
labs(title = "Age distributions of parents") +
# scale_fill_manual(values = c("steelblue3", "sienna2")) +
scale_fill_manual(values = usecol(c(Seeblau, Peach))) +  # assuming library('unikn')
theme_bw()

1. Can you think of a way of plotting the relationship between (or difference between) the age of both parents for each participant?

(Hint: Again, you need to use gather to collect the ages of both parents into one variable.)

#### Solution

df_long <- df %>%
mutate(similar_ages = (abs(dad - mom) < 5)) %>%  # similar_ages if difference below 5 years
select(study:mom, similar_ages) %>%
gather(key = "parent", value = "age", dad:mom)
# df_long

ggplot(df_long, aes(x = parent, y = age)) +
geom_line(aes(group = ID, color = similar_ages), alpha = .3, size = 2) +
facet_grid(~similar_ages) +
labs(title = "Relations between parents' ages") +
# scale_color_manual(values = c("red3", "black")) +
scale_color_manual(values = usecol(c(Bordeaux, Petrol))) +  # assuming library('unikn')
theme_bw()

We see that most parents are of a similar age (i.e., have an age difference smaller than 5 years). Of the parents with larger age differences it is mostly the dads older than the moms, except for one case in the other direction.

### A.7.6 Exercise 6

#### Experiment with wider data

This is a bonus task — for the ambitious or curious — which requires transforming a dataset with multiple dependent variables into tidy data. This task extends beyond the scope of the current chapter, but can be solved with our current base R and tidyverse commands. (See Section 7.2.6 for additional information.)

#### Data

The data table exp_wide contains data from $$n = 10$$ participants. Each participant completed two tasks (and the task position p was randomized). For each task, we measured two dependent variables: The correctness c of the response, and the time t (in msec) to complete the task:

Table A.25: Example data from an experiment containing 2 tasks (and 2 DVs).
subj p_1 p_2 c_1 c_2 t_1 t_2
1 1 2 FALSE FALSE 4873.7 9230.0
2 1 2 FALSE FALSE 3963.9 2948.8
3 2 1 FALSE TRUE 2868.4 8348.3
4 2 1 FALSE FALSE 2561.3 1290.2
5 1 2 TRUE FALSE 5762.2 9330.7
6 1 2 FALSE FALSE 4873.7 9230.0
7 2 1 FALSE TRUE 3963.9 2948.8
8 1 2 TRUE TRUE 2868.4 8348.3
9 2 1 TRUE TRUE 2561.3 1290.2
10 2 1 TRUE FALSE 5762.2 9330.7

1. Import the data from ds4psy::exp_wide (or http://rpository.com/ds4psy/data/exp_wide.csv) into a tibble exp_wide.

2. Use 2 different ways to transform (or reshape) exp_wide into a table of tidy data exp_tidy.

3. Verify the equality of both solutions.

#### Solution

1. Solution 1 by using stats::reshape():

Note that reshape() assumes a data frame, rather than a tibble, which is why we will convert it with as.data.frame() and convert the result into a tibble with as_tibble().

## Load data: ----
exp_wide <- ds4psy::exp_wide  # from ds4psy package
dim(exp_wide)  # 10 x 7
#> [1] 10  7

# Turn into a data frame:
exp_wide <- as.data.frame(exp_wide)

## Use reshape to transform into long format:
exp_long <- stats::reshape(data = exp_wide,
direction = "long",
varying = list(p = 2:3, # 1st set of variables to combine into 1
c = 4:5, # 2nd set of variables to combine into 1
t = 6:7),
v.names = c("position", "correct", "time")
)

exp_tidy_1 <- exp_long %>%
select(subj, position, correct, time) %>%
arrange(subj, position)

exp_tidy_1 <- as_tibble(exp_tidy_1)
exp_tidy_1
#> # A tibble: 20 × 4
#>     subj position correct  time
#>    <dbl>    <dbl> <lgl>   <dbl>
#>  1     1        1 FALSE   4874.
#>  2     1        2 FALSE   9230
#>  3     2        1 FALSE   3964.
#>  4     2        2 FALSE   2949.
#>  5     3        1 TRUE    8348.
#>  6     3        2 FALSE   2868.
#>  7     4        1 FALSE   1290.
#>  8     4        2 FALSE   2561.
#>  9     5        1 TRUE    5762.
#> 10     5        2 FALSE   9331.
#> 11     6        1 FALSE   4874.
#> 12     6        2 FALSE   9230
#> 13     7        1 TRUE    2949.
#> 14     7        2 FALSE   3964.
#> 15     8        1 TRUE    2868.
#> 16     8        2 TRUE    8348.
#> 17     9        1 TRUE    1290.
#> 18     9        2 TRUE    2561.
#> 19    10        1 FALSE   9331.
#> 20    10        2 TRUE    5762.
1. Solution 2 by using multiple tidyr commands:
## Load data: ----
exp_wide <- ds4psy::exp_wide  # from ds4psy package
dim(exp_wide)  # 10 x 7
#> [1] 10  7
exp_wide
#> # A tibble: 10 × 7
#>     subj   p_1   p_2 c_1   c_2     t_1   t_2
#>    <dbl> <dbl> <dbl> <lgl> <lgl> <dbl> <dbl>
#>  1     1     1     2 FALSE FALSE 4874. 9230
#>  2     2     1     2 FALSE FALSE 3964. 2949.
#>  3     3     2     1 FALSE TRUE  2868. 8348.
#>  4     4     2     1 FALSE FALSE 2561. 1290.
#>  5     5     1     2 TRUE  FALSE 5762. 9331.
#>  6     6     1     2 FALSE FALSE 4874. 9230
#>  7     7     2     1 FALSE TRUE  3964. 2949.
#>  8     8     1     2 TRUE  TRUE  2868. 8348.
#>  9     9     2     1 TRUE  TRUE  2561. 1290.
#> 10    10     2     1 TRUE  FALSE 5762. 9331.

## Gather all 6 dependent variables (from wide into long format): --------
exp_long <- exp_wide %>%
gather(key = "key", value = "value", p_1:t_2) %>%
separate(col = key, into = c("var", "pos"))
exp_long
#> # A tibble: 60 × 4
#>     subj var   pos   value
#>    <dbl> <chr> <chr> <dbl>
#>  1     1 p     1         1
#>  2     2 p     1         1
#>  3     3 p     1         2
#>  4     4 p     1         2
#>  5     5 p     1         1
#>  6     6 p     1         1
#>  7     7 p     1         2
#>  8     8 p     1         1
#>  9     9 p     1         2
#> 10    10 p     1         2
#> # … with 50 more rows

## Deal with 3 sub-tables (of different DVs): --------

# 1st slice of 20 rows: Position information
exp_pos <- exp_long %>%
filter(var == "p") %>%   # filter 20 rows with position info
select(subj, pos, value) %>%
rename(position = value) %>%
arrange(subj, pos)  # ensure order
exp_pos
#> # A tibble: 20 × 3
#>     subj pos   position
#>    <dbl> <chr>    <dbl>
#>  1     1 1            1
#>  2     1 2            2
#>  3     2 1            1
#>  4     2 2            2
#>  5     3 1            2
#>  6     3 2            1
#>  7     4 1            2
#>  8     4 2            1
#>  9     5 1            1
#> 10     5 2            2
#> 11     6 1            1
#> 12     6 2            2
#> 13     7 1            2
#> 14     7 2            1
#> 15     8 1            1
#> 16     8 2            2
#> 17     9 1            2
#> 18     9 2            1
#> 19    10 1            2
#> 20    10 2            1

# 2nd slice of 20 rows: Correctness information
exp_cor <- exp_long %>%
filter(var == "c") %>%  # filter 20 rows with correctness info
select(subj, pos, value) %>%
mutate(correct = as.logical(value)) %>%  # correct as Boolean value!
select(subj, pos, correct) %>%
arrange(subj, pos)  # ensure order
exp_cor
#> # A tibble: 20 × 3
#>     subj pos   correct
#>    <dbl> <chr> <lgl>
#>  1     1 1     FALSE
#>  2     1 2     FALSE
#>  3     2 1     FALSE
#>  4     2 2     FALSE
#>  5     3 1     FALSE
#>  6     3 2     TRUE
#>  7     4 1     FALSE
#>  8     4 2     FALSE
#>  9     5 1     TRUE
#> 10     5 2     FALSE
#> 11     6 1     FALSE
#> 12     6 2     FALSE
#> 13     7 1     FALSE
#> 14     7 2     TRUE
#> 15     8 1     TRUE
#> 16     8 2     TRUE
#> 17     9 1     TRUE
#> 18     9 2     TRUE
#> 19    10 1     TRUE
#> 20    10 2     FALSE

# 3rd slice of 20 rows: Time information
exp_time <- exp_long %>%
filter(var == "t") %>%
select(subj, pos, value) %>%
mutate(time = as.numeric(value)) %>%  # time as numeric value!
select(subj, pos, time) %>%
arrange(subj, pos)  # ensure order
exp_time
#> # A tibble: 20 × 3
#>     subj pos    time
#>    <dbl> <chr> <dbl>
#>  1     1 1     4874.
#>  2     1 2     9230
#>  3     2 1     3964.
#>  4     2 2     2949.
#>  5     3 1     2868.
#>  6     3 2     8348.
#>  7     4 1     2561.
#>  8     4 2     1290.
#>  9     5 1     5762.
#> 10     5 2     9331.
#> 11     6 1     4874.
#> 12     6 2     9230
#> 13     7 1     3964.
#> 14     7 2     2949.
#> 15     8 1     2868.
#> 16     8 2     8348.
#> 17     9 1     2561.
#> 18     9 2     1290.
#> 19    10 1     5762.
#> 20    10 2     9331.

# Combine all 3 sub-tables again: --------
exp_pos_cor      <- left_join(exp_pos, exp_cor)
exp_pos_cor_time <- left_join(exp_pos_cor, exp_time)

# Select and arrange:
exp_tidy_2 <- exp_pos_cor_time %>%
select(subj, position, correct, time) %>%
arrange(subj, position)

exp_tidy_2
#> # A tibble: 20 × 4
#>     subj position correct  time
#>    <dbl>    <dbl> <lgl>   <dbl>
#>  1     1        1 FALSE   4874.
#>  2     1        2 FALSE   9230
#>  3     2        1 FALSE   3964.
#>  4     2        2 FALSE   2949.
#>  5     3        1 TRUE    8348.
#>  6     3        2 FALSE   2868.
#>  7     4        1 FALSE   1290.
#>  8     4        2 FALSE   2561.
#>  9     5        1 TRUE    5762.
#> 10     5        2 FALSE   9331.
#> 11     6        1 FALSE   4874.
#> 12     6        2 FALSE   9230
#> 13     7        1 TRUE    2949.
#> 14     7        2 FALSE   3964.
#> 15     8        1 TRUE    2868.
#> 16     8        2 TRUE    8348.
#> 17     9        1 TRUE    1290.
#> 18     9        2 TRUE    2561.
#> 19    10        1 FALSE   9331.
#> 20    10        2 TRUE    5762.
1. Verify the equality of both solutions:
all.equal(exp_tidy_1, exp_tidy_2)  # TRUE (qed).
#>  [1] "Attributes: < Names: 1 string mismatch >"
#>  [2] "Attributes: < Length mismatch: comparison on first 2 components >"
#>  [3] "Attributes: < Component 2: Modes: list, numeric >"
#>  [4] "Attributes: < Component 2: names for target but not for current >"
#>  [5] "Attributes: < Component 2: Length mismatch: comparison on first 4 components >"
#>  [6] "Attributes: < Component 2: Component 1: Modes: list, numeric >"
#>  [7] "Attributes: < Component 2: Component 1: names for target but not for current >"
#>  [8] "Attributes: < Component 2: Component 1: Length mismatch: comparison on first 1 components >"
#>  [9] "Attributes: < Component 2: Component 1: Component 1: Modes: character, numeric >"
#> [10] "Attributes: < Component 2: Component 1: Component 1: Lengths: 2, 1 >"
#> [11] "Attributes: < Component 2: Component 1: Component 1: target is character, current is numeric >"
#> [12] "Attributes: < Component 2: Component 2: Modes: character, numeric >"
#> [13] "Attributes: < Component 2: Component 2: Lengths: 3, 1 >"
#> [14] "Attributes: < Component 2: Component 2: target is character, current is numeric >"
#> [15] "Attributes: < Component 2: Component 3: Modes: character, numeric >"
#> [16] "Attributes: < Component 2: Component 3: target is character, current is numeric >"
#> [17] "Attributes: < Component 2: Component 4: Modes: character, numeric >"
#> [18] "Attributes: < Component 2: Component 4: target is character, current is numeric >"

This concludes our exercises on tidying data with tidyr.