library("readxl")
library("dplyr")
library("stringr")
library("janitor")
library("tidyr")
library("tibble")Tidying and joining data
In this activity we’ll explore several data sets, two of which we came across in the videos on tidying and joining
- GB Bovine TB
- Elephant behaviour
- Dog morphology
- Dog activity during nursing home visits
We begin by loading some packages
(Note we do not show the messages that get printed when the packages are loaded.)
Bovine Tuberculosis
We begin by reading in the data and saving a copy so we can quickly start over if we mess anything up
#load bovine TB data
bovine <- read_xlsx("data/bovine-tb/gb-tb-stats.xlsx") |>
mutate(date = as.Date(date)) |>
rename(n_cases = n_not_otf)
# save a copy of the original data
orig_bovine <- bovineThe first line reads the data into R, the second converts the date column into something R recognises as a date, and the final line renames the n_not_otf column (“not OTF” is the terminology used in the GB bovine TB stats to mean, effectively, cases of TB. OTF stands for “officially tuberculosis free”.)
If you make a mistake and mess up the data frame, just run bovine <- orig_bovine to get back to the original data.
Take a look at the data
bovine# A tibble: 1,017 × 4
country date n_herds n_cases
<chr> <date> <dbl> <dbl>
1 England 1996-01-31 91024 355
2 England 1996-02-29 90735 392
3 England 1996-03-31 90512 440
4 England 1996-04-30 90210 454
5 England 1996-05-31 89357 434
6 England 1996-06-30 88554 403
7 England 1996-07-31 87802 387
8 England 1996-08-31 87356 379
9 England 1996-09-30 86879 313
10 England 1996-10-31 86065 274
# ℹ 1,007 more rows
NoteQuestion
Looking at the data frame bovine, is it in tidy format? Justify your answer.
TipSolution
Yes, the bovine data frame is in tidy format. The observations are at the level of monthly number of cases and number of herds per country. Hence there is one observation per row, and the two variables, n_herds and n_cases are each in their own column.
NoteQuestion
Which columns in the bovine data frame are the keys?
TipSolution
Keys uniquely identify the observations in the data. In the bovine data frame we need both country and date to uniquely identify an observation, so these are the keys.
We need to
NoteQuestion
How many cases of bovine TB were there in England in 2001? Refer to slide 8 in the “Data pivoting with tidyr” slide deck for code to add year as a variable to bovine.
TipSolution
There were 8318 cases of bovine TB in herds in England in 2001.
bovine |>
mutate(year = format(date, "%Y")) |>
filter(country == "England" & year == "2001") |>
summarise(total_cases = sum(n_cases))# A tibble: 1 × 1
total_cases
<dbl>
1 8318
NoteQuestion
What was the maxinun number of herds recorded in any one month in Scotland in 1998?
TipSolution
There maximum number of herds recorded in any one month in Scotland in 1998 was 1.8444^{4}.
bovine |>
mutate(year = format(date, "%Y")) |>
filter(country == "Scotland" & year == "1998") |>
summarise(max_herds = max(n_herds))# A tibble: 1 × 1
max_herds
<dbl>
1 18444In the next question, you will pivot the bovine data frame to a longer, suitale for import to a database.
To make sure your answer to the previous questions didn’t result in changes to bovine that would make it difficult to answer the questions below, start by resetting bovine to how it was when you started the exercise by running
bovine <- orig_bovine
NoteQuestion
Pivot the bovine data frame to make it longer, so it resembles this
# A tibble: 2,034 × 4
country date type count
<chr> <date> <chr> <dbl>
1 England 1996-01-31 n_herds 91024
2 England 1996-01-31 n_cases 355
3 England 1996-02-29 n_herds 90735
4 England 1996-02-29 n_cases 392
5 England 1996-03-31 n_herds 90512
6 England 1996-03-31 n_cases 440
7 England 1996-04-30 n_herds 90210
8 England 1996-04-30 n_cases 454
9 England 1996-05-31 n_herds 89357
10 England 1996-05-31 n_cases 434
# ℹ 2,024 more rows
CautionHint 1
To solve this task, you need to identify that the two column named n_herds and n_cases need to be pivoted into a single new column named type and that the values from these two variable need to go into a new column named count.
This means that the country and date columns are not pivoted.
CautionHint 2
Look at the required output
# A tibble: 2,034 × 4
country date type count
<chr> <date> <chr> <dbl>
1 England 1996-01-31 n_herds 91024
2 England 1996-01-31 n_cases 355
3 England 1996-02-29 n_herds 90735
4 England 1996-02-29 n_cases 392
5 England 1996-03-31 n_herds 90512
6 England 1996-03-31 n_cases 440
7 England 1996-04-30 n_herds 90210
8 England 1996-04-30 n_cases 454
9 England 1996-05-31 n_herds 89357
10 England 1996-05-31 n_cases 434
# ℹ 2,024 more rowsThe variable names "n_herds" and "n_cases" need to go to a column named type.
The values in the two variables need to go to a column named count.
Look at the help for pivot_longer() (type ?pivot_longer at the R concole prompt) to identify which arguments you need to achieve these two steps.
TipSolution
To pivot bovine to the required longer format, we would use the following code
bovine |>
pivot_longer(-c(country, date),
names_to = "type", values_to = "count")# A tibble: 2,034 × 4
country date type count
<chr> <date> <chr> <dbl>
1 England 1996-01-31 n_herds 91024
2 England 1996-01-31 n_cases 355
3 England 1996-02-29 n_herds 90735
4 England 1996-02-29 n_cases 392
5 England 1996-03-31 n_herds 90512
6 England 1996-03-31 n_cases 440
7 England 1996-04-30 n_herds 90210
8 England 1996-04-30 n_cases 454
9 England 1996-05-31 n_herds 89357
10 England 1996-05-31 n_cases 434
# ℹ 2,024 more rowsYou could also use this variant where we instead of excluding some columns from the pivot, we name the columns we want to pivot
bovine |>
pivot_longer(c(n_herds, n_cases),
names_to = "type", values_to = "count")Elephant behaviour
The data for the elephant behaviour study are arranged in two sheets, one for the day time behaviour and one for the qualitative behaviour assessment (QBA).
There are three observers per data set, with the data for each obsever stored in a separate sheet.
This doesn’t pose any real difficulties, but it does mean we have to run quite a few lines of code to load this data and arrange it as needed. You should copy and paste these two code blocks into your script but take a minute to look at what each line is doing while reading the paragraph below.
We start with the day time behaviour data: The first few lines define the file name and path to it, then load each sheet in turn, adding the observer column to each sheet with an mutate(). The final chunk creates elephant_day by binding (concatenating) the rows of the three data frames into a single data frame with bind_rows() and doing two further data wrangling steps that you hsould be familiar with.
elephant_f <- "data/elephants/daytime-behaviour-elephant.xlsx"
elephant_day_1 <- read_xlsx(elephant_f, sheet = 1) |>
mutate(observer = 1)
elephant_day_2 <- read_xlsx(elephant_f, sheet = 2) |>
mutate(observer = 2)
elephant_day_3 <- read_xlsx(elephant_f, sheet = 3) |>
mutate(observer = 3)
elephant_day <-
elephant_day_1 |>
bind_rows(elephant_day_2, elephant_day_3)|>
janitor::clean_names() |>
relocate(observer, .after = 1L)We repeat the same steps with the QBA data:
elephant_qba_f <- "data/elephants/qba-iqr-elephant.xlsx"
elephant_qba_1 <- read_xlsx(elephant_qba_f, sheet = 1) |>
mutate(observer = 1)
elephant_qba_2 <- read_xlsx(elephant_qba_f, sheet = 2) |>
mutate(observer = 2)
elephant_qba_3 <- read_xlsx(elephant_qba_f, sheet = 3) |>
mutate(observer = 3)
elephant_qba <-
elephant_qba_1 |>
bind_rows(elephant_qba_2, elephant_qba_3)|>
janitor::clean_names() |>
relocate(observer, .after = 1L)Take a look at the data; first elephant_day
elephant_day# A tibble: 84 × 18
elephant observer observation stereotypy wallowing feeding foraging
<chr> <dbl> <chr> <dbl> <dbl> <dbl> <dbl>
1 Elephant1F 1 Obs1 0 0 2 0
2 Elephant1F 1 Obs2 0 0 1 0
3 Elephant1F 1 Obs3 0 0 1 0
4 Elephant1F 1 Obs4 0 0 1 0
5 Elephant1F 1 Obs5 0 0 1 0
6 Elephant1F 1 Obs6 0 0 1 0
7 Elephant1F 1 Obs7 0 0 1 0
8 Elephant1F 1 Obs8 0 0 1 0
9 Elephant2F 1 Obs9 0 0 2 0
10 Elephant2F 1 Obs10 0 0 1 0
# ℹ 74 more rows
# ℹ 11 more variables: feedforage <dbl>, comfort <dbl>, comfortwallow <dbl>,
# environmental_interaction <dbl>, affiliative <dbl>, agonistic <dbl>,
# anticipating <dbl>, locomotion <dbl>, sleeprest <dbl>, stereoantic <dbl>,
# affilagonostic <dbl>and next elephant_qba
elephant_qba# A tibble: 72 × 15
elephant observer observation content relaxed uncomfartable agitated tense
<chr> <dbl> <chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Elephant1F 1 Obs1 8.6 8.8 0.9 0.2 1.5
2 Elephant1F 1 Obs2 7.1 7.7 0.4 1 3
3 Elephant1F 1 Obs3 7 8 0.8 0.8 1.2
4 Elephant1F 1 Obs4 6.4 7 1.3 0.7 3.2
5 Elephant1F 1 Obs5 8 9 0.9 0.3 0.3
6 Elephant1F 1 Obs6 9.1 9.5 0.4 0.3 0.7
7 Elephant1F 1 Obs7 8.6 9 0.9 0.2 0.2
8 Elephant1F 1 Obs8 8 9 0.9 0.1 1
9 Elephant2F 1 Obs9 8.4 9.7 0.3 0.5 0.5
10 Elephant2F 1 Obs10 7.9 8 0.5 1 0.3
# ℹ 62 more rows
# ℹ 7 more variables: frustrated <dbl>, wary <dbl>, playful <dbl>,
# sociable <dbl>, lively <dbl>, lethargic <dbl>, observations <chr>Next we need to create the meta data for the elephants and observers
elephant_meta <- tribble(
~elephant, ~name, ~sex, ~age,
"Elephant1F", "Winifred", "female", 45,
"Elephant2F", "Ellie", "female", 23,
"Elephant3M", "Dumbo", "male", 57
)
elephant_observer <- tribble(
~observer, ~given, ~surname, ~institution,
1, "Jan", "Lauridesen", "Aalborg Zoo",
2, "Line", "Larsen", "Zoologisk Have København",
3, "Charlotte", "Davidson", "Odense Zoo",
4, "Else", "Jacobsen", "Odense Zoo",
)This should also now be somewhat familiar. Again, please copy and paste this into your script rather than typing all the code out — this is not a course in touch typing!
We want to add the observer name and surname to the day time behaviour data, elephant_day.
First we should work out what we need from the elephant_observer data frame. We are asked to add the observer given and surname to the behaviour data.
NoteQuestion
How would you select just these columns from elephant_observer?
TipSolution
You would use the select() verb
elephant_observer |>
select(given, surname)# A tibble: 4 × 2
given surname
<chr> <chr>
1 Jan Lauridesen
2 Line Larsen
3 Charlotte Davidson
4 Else Jacobsen
NoteQuestion
Which variable(s) do we need to join the elephant_day data with the elephant_observer data? Modify your select() statement above any required key variables needed for matching rows.
TipSolution
The observer variable is the primary key in the elephant_observer data frame. It is the foreign key in the elephant_day data frame.
As we need to preserve this variable for the matching, the select() statement would become
elephant_observer |>
select(observer, given, surname)# A tibble: 4 × 3
observer given surname
<dbl> <chr> <chr>
1 1 Jan Lauridesen
2 2 Line Larsen
3 3 Charlotte Davidson
4 4 Else Jacobsen To add the observer’s name to the daytime behaviour data we need to preserve all the rows in elephant_day and this is the focal daya frame so it should be in left position. We are matching the observer code with the names in the elephant_observer data frame, so this data frame is in the right position. We will need a left join for this operation.
We can complete the task of adding the observer names with the following code
el_new <- elephant_day |>
left_join(
elephant_observer |>
select(observer, given, surname),
by = join_by(observer)
)
el_new# A tibble: 84 × 20
elephant observer observation stereotypy wallowing feeding foraging
<chr> <dbl> <chr> <dbl> <dbl> <dbl> <dbl>
1 Elephant1F 1 Obs1 0 0 2 0
2 Elephant1F 1 Obs2 0 0 1 0
3 Elephant1F 1 Obs3 0 0 1 0
4 Elephant1F 1 Obs4 0 0 1 0
5 Elephant1F 1 Obs5 0 0 1 0
6 Elephant1F 1 Obs6 0 0 1 0
7 Elephant1F 1 Obs7 0 0 1 0
8 Elephant1F 1 Obs8 0 0 1 0
9 Elephant2F 1 Obs9 0 0 2 0
10 Elephant2F 1 Obs10 0 0 1 0
# ℹ 74 more rows
# ℹ 13 more variables: feedforage <dbl>, comfort <dbl>, comfortwallow <dbl>,
# environmental_interaction <dbl>, affiliative <dbl>, agonistic <dbl>,
# anticipating <dbl>, locomotion <dbl>, sleeprest <dbl>, stereoantic <dbl>,
# affilagonostic <dbl>, given <chr>, surname <chr>It would be nice to drop the observer column and move the observer name nearer to the front of the columns, which we do using
el_new <- el_new |>
select(!observer) |>
relocate(given, surname, .after = 1L)
el_new# A tibble: 84 × 19
elephant given surname observation stereotypy wallowing feeding foraging
<chr> <chr> <chr> <chr> <dbl> <dbl> <dbl> <dbl>
1 Elephant1F Jan Lauridesen Obs1 0 0 2 0
2 Elephant1F Jan Lauridesen Obs2 0 0 1 0
3 Elephant1F Jan Lauridesen Obs3 0 0 1 0
4 Elephant1F Jan Lauridesen Obs4 0 0 1 0
5 Elephant1F Jan Lauridesen Obs5 0 0 1 0
6 Elephant1F Jan Lauridesen Obs6 0 0 1 0
7 Elephant1F Jan Lauridesen Obs7 0 0 1 0
8 Elephant1F Jan Lauridesen Obs8 0 0 1 0
9 Elephant2F Jan Lauridesen Obs9 0 0 2 0
10 Elephant2F Jan Lauridesen Obs10 0 0 1 0
# ℹ 74 more rows
# ℹ 11 more variables: feedforage <dbl>, comfort <dbl>, comfortwallow <dbl>,
# environmental_interaction <dbl>, affiliative <dbl>, agonistic <dbl>,
# anticipating <dbl>, locomotion <dbl>, sleeprest <dbl>, stereoantic <dbl>,
# affilagonostic <dbl>
NoteQuestion
Your team leader knows the elephants in this study quite well by name. To help them make sense of the QBA data, your team leader has asked you to add the name of each elephant and their age and sex to the the elephant_qba data frame.
How would you achieve this?
CautionHint 1
Start by identifying which data frame is in the left position or the right position.
CautionHint 2
Think about whether you need to preserve the rows from the left or right data frame; this will help you identify which type of mutating join to use.
CautionHint 3
Identify the primary and foreign keys that link the two data frames.
TipSolution
el_new2 <- elephant_qba |>
left_join(
elephant_meta,
by = join_by(elephant)
) |>
relocate(name, sex, age, .after = 1L)
el_new2# A tibble: 72 × 18
elephant name sex age observer observation content relaxed uncomfartable
<chr> <chr> <chr> <dbl> <dbl> <chr> <dbl> <dbl> <dbl>
1 Elephan… Wini… fema… 45 1 Obs1 8.6 8.8 0.9
2 Elephan… Wini… fema… 45 1 Obs2 7.1 7.7 0.4
3 Elephan… Wini… fema… 45 1 Obs3 7 8 0.8
4 Elephan… Wini… fema… 45 1 Obs4 6.4 7 1.3
5 Elephan… Wini… fema… 45 1 Obs5 8 9 0.9
6 Elephan… Wini… fema… 45 1 Obs6 9.1 9.5 0.4
7 Elephan… Wini… fema… 45 1 Obs7 8.6 9 0.9
8 Elephan… Wini… fema… 45 1 Obs8 8 9 0.9
9 Elephan… Ellie fema… 23 1 Obs9 8.4 9.7 0.3
10 Elephan… Ellie fema… 23 1 Obs10 7.9 8 0.5
# ℹ 62 more rows
# ℹ 9 more variables: agitated <dbl>, tense <dbl>, frustrated <dbl>,
# wary <dbl>, playful <dbl>, sociable <dbl>, lively <dbl>, lethargic <dbl>,
# observations <chr>Dog morphology
In the third part of the activity, we will briefly look at a data set of information on the morphology of dog breeds. Again, the data are somewhat messy and need a little cleaning before we cna begin working with them.
The data are in two separate sheets within the the dog-morphology.xlsx Excel workbook. First we have skull length and width measurements on several males and females from a number of dog breeds.
We load the data from the CephalicIndex sheet, clean the variable names, and then change the sex variable to have nice labels. Copy and paste this code into your script, but make sure you understand what each line is doing before you run the code:
dog_skull <- read_xlsx("data/dog-morphology/dog-morphology.xlsx",
sheet = "CephalicIndex") |>
janitor::clean_names() |>
mutate(
sex = case_when(
sex == "M" ~ "male",
sex == "F" ~ "female"
)
)The second sheet contains average weight and height data for each breed, plus the typical height and weight ranges for male and female dogs of eahc breed. This data set is messy because it doesn’t have a label for each column in the sheet. Again, copy and pste the code into your script and study it for a few minutes before you run it. When you do run the code below you will see
New names:
• `` -> `...4`
• `` -> `...6`
• `` -> `...9`
• `` -> `...11`printed in the console: This is fine! When we run janitor::clean_names() on the data these weird names ...4 etc are turned into x4 etc., which is why we use the rename() function to give names to these unlabeled columns as well as fix the labels for those that are named:
dog_hw <- read_xlsx("data/dog-morphology/dog-morphology.xlsx",
sheet = "HeightWeight") |>
janitor::clean_names() |>
rename(
weight_lower_male = weight_range_male_lowest_and_highest,
weight_upper_male = x4,
weight_lower_female = weight_range_female,
weight_upper_female = x6,
height_lower_male = height_range_male,
height_upper_male = x9,
height_lower_female = height_range_female,
height_upper_female = x11,
)New names:
• `` -> `...4`
• `` -> `...6`
• `` -> `...9`
• `` -> `...11`
NoteQuestion
Your group is planning to analyse the skull height and weight data to see if there is a relationship between skull length (width) and the size of each breed. In effect you are interested in asking if the the length or width of the skulls varies with the height or the weight of each breed in a consistent manner. You are also interested in whether the relationship between skull size and animal wiehgt and height differs between sexes.
To facilitate this analysis you need to add the average height and weight for male and female dogs of each breed to the dof_hw data frame:
dog_hw <- dog_hw |>
mutate(
male_weight = (weight_lower_male + weight_upper_male) / 2,
male_height = (height_lower_male + height_upper_male) / 2,
female_weight = (weight_lower_female + weight_upper_female) / 2,
female_height = (height_lower_female + height_upper_female) / 2
)What data wrangling code will you need to achieve this aim?
CautionHint 1
Start by preparing the dog_hw data to only contain the breed variable and the height and weight variables for both sexes.
CautionSolution for hint 1
dog_hw2 <- dog_hw |>
select(breed, starts_with("male"), starts_with("female"))
CautionHint 2
To join the dog_hw data to the dog_skull data we need to match the breed and the sex. At the moment we don’t have a sex variable in dog_hw.
Identify where in the dog_hw this sex information is stored. You’ll need to pivot the prepared dog_hw data to a longer format. Refer to the slides for guidance on how to pivot these data to extract the sex variable from the column names. And then you’ll need to pivot it wider again so that you have sex, height and weight variables.
CautionSolution for hint 2
The first pivot to a longer format, creating the sex and height/weight measurement variables is
dog_hw2 <- dog_hw2 |>
pivot_longer(
!breed,
names_to = c("sex", "variable"),
names_sep = "_",
values_to = "value"
)The second pivot, to the wide format is
dog_hw2 <- dog_hw2 |>
pivot_wider(
id_cols = c(breed, sex),
names_from = "variable",
values_from = "value"
)
CautionHint 3
Once you have pivoted the dog_hw data you should be able to join this data with the skull size data.
We want to add the breed average size data to the skull size data. Identify which data set should be in the right and left position and which kind of mutating join you will need.
There is a problem though; we don’t have heights and weights for all breeds. How would you filter the skull data so we keep only the skull measurments for breeds that we do have height and weight data for?
CautionSolution for hint 3
We solve the missing information problem using a filtering join:
dog_skull2 <- dog_skull |>
# filter the skull data, keeping only those breeds we have height and
# weight data for
semi_join(
dog_hw2,
by = join_by(breed, sex)
)Then we can do actually join the two data sets
dog_skull2 |>
left_join(
dog_hw2,
by = join_by(breed, sex)
)# A tibble: 491 × 6
breed sex skull_length skull_width weight height
<chr> <chr> <dbl> <dbl> <dbl> <dbl>
1 AMERICAN STAFFORDSHIRE TERRIER male 18.4 12.7 27.7 45.7
2 AMERICAN STAFFORDSHIRE TERRIER male 17.8 13.2 27.7 45.7
3 AMERICAN STAFFORDSHIRE TERRIER male 19.6 13.0 27.7 45.7
4 AMERICAN STAFFORDSHIRE TERRIER male 18.3 12.6 27.7 45.7
5 AMERICAN STAFFORDSHIRE TERRIER male 17.3 13.0 27.7 45.7
6 AMERICAN STAFFORDSHIRE TERRIER male 19.1 12.6 27.7 45.7
7 AMERICAN STAFFORDSHIRE TERRIER female 19.0 12.1 27.7 43.2
8 AMERICAN STAFFORDSHIRE TERRIER female 16.5 11.2 27.7 43.2
9 AMERICAN STAFFORDSHIRE TERRIER female 18.7 10.6 27.7 43.2
10 AMERICAN STAFFORDSHIRE TERRIER female 16.3 13.1 27.7 43.2
# ℹ 481 more rows
TipSolution
This is a little more involved than the previous examples, but it shows how we can do some complex data wrangling by breaking the task down into several steps and then combining them
# prepare dog_hw
dog_hw2 <- dog_hw |>
select(breed, starts_with("male"), starts_with("female")) |>
# now pivot it longer
pivot_longer(
!breed,
names_to = c("sex", "variable"),
names_sep = "_",
values_to = "value"
) |>
# now pivot wider again to get sex, height, weight as variables
pivot_wider(
id_cols = c(breed, sex),
names_from = "variable",
values_from = "value"
)Now we can join the prepared data dog_w2 with the skull data. This is also slightly more complicated because we don’t have breed average height and weight data for all the breed we have skull measurements for. Hence we do a filtering join first before the mutating join
dog_skull |>
# filter the skull data, keeping only those breeds we have height and
# weight data for
semi_join(
dog_hw2,
by = join_by(breed, sex)
) |>
# now finally do the join to bring the data together
left_join(
dog_hw2,
by = join_by(breed, sex)
) # A tibble: 491 × 6
breed sex skull_length skull_width weight height
<chr> <chr> <dbl> <dbl> <dbl> <dbl>
1 AMERICAN STAFFORDSHIRE TERRIER male 18.4 12.7 27.7 45.7
2 AMERICAN STAFFORDSHIRE TERRIER male 17.8 13.2 27.7 45.7
3 AMERICAN STAFFORDSHIRE TERRIER male 19.6 13.0 27.7 45.7
4 AMERICAN STAFFORDSHIRE TERRIER male 18.3 12.6 27.7 45.7
5 AMERICAN STAFFORDSHIRE TERRIER male 17.3 13.0 27.7 45.7
6 AMERICAN STAFFORDSHIRE TERRIER male 19.1 12.6 27.7 45.7
7 AMERICAN STAFFORDSHIRE TERRIER female 19.0 12.1 27.7 43.2
8 AMERICAN STAFFORDSHIRE TERRIER female 16.5 11.2 27.7 43.2
9 AMERICAN STAFFORDSHIRE TERRIER female 18.7 10.6 27.7 43.2
10 AMERICAN STAFFORDSHIRE TERRIER female 16.3 13.1 27.7 43.2
# ℹ 481 more rowsDog behaviour during nursing home visits
The data for this part of the activity are in the nursing-home-dogs.xlsx Excel file within the data folder. There are two sheets
# file name including path
nursing_home_f <- "data/nursing-home-dogs/nursing-home-dogs.xlsx"
# look at the sheet names
excel_sheets(nursing_home_f)[1] "Activity" "Activity_Codes"The first, Activity, contains the activity data, recorded for different nursing home residents (rows) and a range of activity types (columns). The second sheet, Activity_Codes. contains the meta data for the activity types listed in columns 2 to 13 of the Activity sheet.
Load both of these sheets into R
dog_activity <- read_xlsx(nursing_home_f, sheet = "Activity")
activity_meta <- read_xlsx(nursing_home_f, sheet = "Activity_Codes") |>
mutate(Code = as.character(Code))
NoteQuestion
We would like to reformat the data in dog_activity from the current wide format to a long format so that we can add the activity name, the Activity variable in activity_meta, in place of the current numeric code. We want to do this so we can create a table of the data for a report we are writing on the outcomes of this study.
Your tasks are to
- Pivot the
dog_activitydata frame to a new data frame nameddog_long, which will have three columns:PersonCodeValue
- Join the
dog_longdata frame with theactivity_metadata frame on thecodevariable. The resulting data frame should contain only the columnsPersonActivity(from theactivity_metadata frame), andValue
dog_and_meta - Pivot the
dog_and_metadata frame from the long form that it is in currently back to the wide format. Call this wide data framedog_wide
Task 1
CautionHint for task 1
We begin by pivoting the data with pivot_longer().
We want to pivot all the columns but Person.
We want the names of the pivoted columns to go to a new column named Code.
We want the values in the cells to go to a new column named Value
TipSolution to task 1
dog_long <- dog_activity |>
pivot_longer(
!Person, # which columns are we (not) pivoting
names_to = "Code", # name of column to hold the names of vars we pivoted
values_to = "Value" # name of column to hold the data from the cells
)
dog_long# A tibble: 288 × 3
Person Code Value
<chr> <chr> <dbl>
1 Bjarne 1 5
2 Bjarne 2 1
3 Bjarne 3 8
4 Bjarne 4 7
5 Bjarne 5 11
6 Bjarne 6 2
7 Bjarne 7 4
8 Bjarne 8 10
9 Bjarne 9 9
10 Bjarne 10 3
# ℹ 278 more rowsTask 2
CautionHint for task 2
Now we want to join the dog_long data frame with the activity_meta data frame.
We want to join on the Code column.
We want to keep all the data from the dog_long data frame so we want a left join.
TipSolution to task 2
dog_and_meta <- dog_long |>
left_join(
activity_meta,
by = join_by(Code)
) |>
select(-Code, -Description) |>
relocate(Activity, .after = 1L)
dog_and_meta# A tibble: 288 × 3
Person Activity Value
<chr> <chr> <dbl>
1 Bjarne Brushing part 1 5
2 Bjarne Hiding game with cups 1
3 Bjarne Treats in a bottle 8
4 Bjarne Hide treat in the room 7
5 Bjarne Dice game 11
6 Bjarne Compare fur of dog to fur/skin brought from another animal 2
7 Bjarne Hide treats in green ball 4
8 Bjarne Dog is reading 10
9 Bjarne Standard training 9
10 Bjarne Dog game 1 3
# ℹ 278 more rowsTask 3
CautionHint for task 3
Finally, we need to pivot dog_and_meta from the long format that is in, to the wide format it was originally.
The id_cols identifying a unique observation in the wide format are just Person.
The values in the Activity column should become the names in the wide format.
The values in the Value column should fill the cells of the data frame in the wide format.
TipSolution to task 3
dog_wide <- dog_and_meta |>
pivot_wider(
id_cols = Person,
names_from = "Activity",
values_from = "Value"
)
dog_wide# A tibble: 24 × 13
Person `Brushing part 1` `Hiding game with cups` `Treats in a bottle`
<chr> <dbl> <dbl> <dbl>
1 Bjarne 5 1 8
2 Bente 5 6 2
3 Birthe 5 11 9
4 Aase 7 6 3
5 Else 8 4 10
6 Mona 4 5 6
7 Karen 11 5 1
8 Inger 6 3 2
9 Kurt 1 4 3
10 Bodil 2 11 5
# ℹ 14 more rows
# ℹ 9 more variables: `Hide treat in the room` <dbl>, `Dice game` <dbl>,
# `Compare fur of dog to fur/skin brought from another animal` <dbl>,
# `Hide treats in green ball` <dbl>, `Dog is reading` <dbl>,
# `Standard training` <dbl>, `Dog game 1` <dbl>, `Dog game 2` <dbl>,
# `Brushing part 2` <dbl>