Tidy Data

• Each column = one variable (convention is to have “fixed” columns first)

• Each row = one observation

• Each cell = one value

library(tidyverse)

Registered S3 methods overwritten by 'ggplot2':
  method         from
  [.quosures     rlang
  c.quosures     rlang
  print.quosures rlang
── Attaching packages ─────────────────────────────────────── tidyverse 1.2.1 ──
✔ ggplot2 3.1.1     ✔ purrr   0.3.2
✔ tibble  2.1.2     ✔ dplyr   0.8.1
✔ tidyr   0.8.3     ✔ stringr 1.4.0
✔ readr   1.3.1     ✔ forcats 0.4.0
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()

Data set

n <- 4
df <- data.frame(
    pid = c(1,3,4,5),
    desc = paste(sample(c('M', 'F'), n, replace=T),
                    '-',
                    sample(10:70, n),
                    sep=''),
    visit1 = rpois(n, lambda = 20),
    visit2 = rpois(n, lambda=10)
)

df[3,3] = NA

df

A data.frame: 4 × 4

pid	desc	visit1	visit2
<dbl>	<fct>	<int>	<int>
1	F-20	17	6
3	F-61	24	9
4	F-48	NA	10
5	M-57	14	11

Gather

df %>%
gather(visit, measurement, -pid, -desc)

A data.frame: 8 × 4

pid	desc	visit	measurement
<dbl>	<fct>	<chr>	<int>
1	F-20	visit1	17
3	F-61	visit1	24
4	F-48	visit1	NA
5	M-57	visit1	14
1	F-20	visit2	6
3	F-61	visit2	9
4	F-48	visit2	10
5	M-57	visit2	11

df %>%
gather(key=visit, value=measurement, visit1:visit2)

A data.frame: 8 × 4

pid	desc	visit	measurement
<dbl>	<fct>	<chr>	<int>
1	F-20	visit1	17
3	F-61	visit1	24
4	F-48	visit1	NA
5	M-57	visit1	14
1	F-20	visit2	6
3	F-61	visit2	9
4	F-48	visit2	10
5	M-57	visit2	11

Separate

df %>%
gather(key=visit, value=measurement, visit1:visit2) %>%
separate(desc, sep='-', into=c("sex", "age"))

A data.frame: 8 × 5

pid	sex	age	visit	measurement
<dbl>	<chr>	<chr>	<chr>	<int>
1	F	20	visit1	17
3	F	61	visit1	24
4	F	48	visit1	NA
5	M	57	visit1	14
1	F	20	visit2	6
3	F	61	visit2	9
4	F	48	visit2	10
5	M	57	visit2	11

Clean-up and type coercion

df %>%
gather(key=visit, value=measurement, visit1:visit2) %>%
separate(desc, sep='-', into=c("sex", "age")) %>%
mutate(age=as.integer(age),
       visit=str_remove(visit, "visit"),
       visit=as.integer(visit)) %>%
drop_na(measurement) -> df1

df1

A data.frame: 7 × 5

	pid	sex	age	visit	measurement
	<dbl>	<chr>	<int>	<int>	<int>
1	1	F	20	1	17
2	3	F	61	1	24
4	5	M	57	1	14
5	1	F	20	2	6
6	3	F	61	2	9
7	4	F	48	2	10
8	5	M	57	2	11

Joins

names <- data.frame(
    pid = 1:6,
    first = c( "bob", "dan","ann", "liz", "joe", "jen"),
    last = c("lim", "tan", "liu", "nguyn", "smith", "finkelstein")
)

names

A data.frame: 6 × 3

pid	first	last
<int>	<fct>	<fct>
1	bob	lim
2	dan	tan
3	ann	liu
4	liz	nguyn
5	joe	smith
6	jen	finkelstein

inner_join(df, names, by = "pid")

A data.frame: 4 × 6

pid	desc	visit1	visit2	first	last
<dbl>	<fct>	<int>	<int>	<fct>	<fct>
1	F-20	17	6	bob	lim
3	F-61	24	9	ann	liu
4	F-48	NA	10	liz	nguyn
5	M-57	14	11	joe	smith

left_join(df, names, by = "pid")

A data.frame: 4 × 6

pid	desc	visit1	visit2	first	last
<dbl>	<fct>	<int>	<int>	<fct>	<fct>
1	F-20	17	6	bob	lim
3	F-61	24	9	ann	liu
4	F-48	NA	10	liz	nguyn
5	M-57	14	11	joe	smith

right_join(df, names, by = "pid")

A data.frame: 6 × 6

pid	desc	visit1	visit2	first	last
<dbl>	<fct>	<int>	<int>	<fct>	<fct>
1	F-20	17	6	bob	lim
2	NA	NA	NA	dan	tan
3	F-61	24	9	ann	liu
4	F-48	NA	10	liz	nguyn
5	M-57	14	11	joe	smith
6	NA	NA	NA	jen	finkelstein

full_join(df, names, by = "pid")

A data.frame: 6 × 6

pid	desc	visit1	visit2	first	last
<dbl>	<fct>	<int>	<int>	<fct>	<fct>
1	F-20	17	6	bob	lim
3	F-61	24	9	ann	liu
4	F-48	NA	10	liz	nguyn
5	M-57	14	11	joe	smith
2	NA	NA	NA	dan	tan
6	NA	NA	NA	jen	finkelstein

Exercise

1. Using the who data set, summarize the total count for each method of TB diagnosis across all years for which there is data for countries that begin wiht ‘Z’.

help(who)

who %>% sample_n(5)

A tibble: 5 × 60

country	iso2	iso3	year	new_sp_m014	new_sp_m1524	new_sp_m2534	new_sp_m3544	new_sp_m4554	new_sp_m5564	⋯	newrel_m4554	newrel_m5564	newrel_m65	newrel_f014	newrel_f1524	newrel_f2534	newrel_f3544	newrel_f4554	newrel_f5564	newrel_f65
<chr>	<chr>	<chr>	<int>	<int>	<int>	<int>	<int>	<int>	<int>	⋯	<int>	<int>	<int>	<int>	<int>	<int>	<int>	<int>	<int>	<int>
Iceland	IS	ISL	1990	NA	NA	NA	NA	NA	NA	⋯	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA
West Bank and Gaza Strip	PS	PSE	2006	0	1	3	4	1	1	⋯	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA
Vanuatu	VU	VUT	2006	1	5	3	1	4	4	⋯	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA
Israel	IL	ISR	1988	NA	NA	NA	NA	NA	NA	⋯	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA
Senegal	SN	SEN	1982	NA	NA	NA	NA	NA	NA	⋯	NA	NA	NA	NA	NA	NA	NA	NA	NA	NA

colnames(who)

1. 'country'
2. 'iso2'
3. 'iso3'
4. 'year'
5. 'new_sp_m014'
6. 'new_sp_m1524'
7. 'new_sp_m2534'
8. 'new_sp_m3544'
9. 'new_sp_m4554'
10. 'new_sp_m5564'
11. 'new_sp_m65'
12. 'new_sp_f014'
13. 'new_sp_f1524'
14. 'new_sp_f2534'
15. 'new_sp_f3544'
16. 'new_sp_f4554'
17. 'new_sp_f5564'
18. 'new_sp_f65'
19. 'new_sn_m014'
20. 'new_sn_m1524'
21. 'new_sn_m2534'
22. 'new_sn_m3544'
23. 'new_sn_m4554'
24. 'new_sn_m5564'
25. 'new_sn_m65'
26. 'new_sn_f014'
27. 'new_sn_f1524'
28. 'new_sn_f2534'
29. 'new_sn_f3544'
30. 'new_sn_f4554'
31. 'new_sn_f5564'
32. 'new_sn_f65'
33. 'new_ep_m014'
34. 'new_ep_m1524'
35. 'new_ep_m2534'
36. 'new_ep_m3544'
37. 'new_ep_m4554'
38. 'new_ep_m5564'
39. 'new_ep_m65'
40. 'new_ep_f014'
41. 'new_ep_f1524'
42. 'new_ep_f2534'
43. 'new_ep_f3544'
44. 'new_ep_f4554'
45. 'new_ep_f5564'
46. 'new_ep_f65'
47. 'newrel_m014'
48. 'newrel_m1524'
49. 'newrel_m2534'
50. 'newrel_m3544'
51. 'newrel_m4554'
52. 'newrel_m5564'
53. 'newrel_m65'
54. 'newrel_f014'
55. 'newrel_f1524'
56. 'newrel_f2534'
57. 'newrel_f3544'
58. 'newrel_f4554'
59. 'newrel_f5564'
60. 'newrel_f65'

who %>%
select(-iso2, -iso3) %>%
gather(key=group, value=n, starts_with('new')) -> who1

who1 %>% head

A tibble: 6 × 4

country	year	group	n
<chr>	<int>	<chr>	<int>
Afghanistan	1980	new_sp_m014	NA
Afghanistan	1981	new_sp_m014	NA
Afghanistan	1982	new_sp_m014	NA
Afghanistan	1983	new_sp_m014	NA
Afghanistan	1984	new_sp_m014	NA
Afghanistan	1985	new_sp_m014	NA

who1 %>%
mutate(group = str_replace(group, "newrel", "new_rel")) -> who2

who2 %>% head

A tibble: 6 × 4

country	year	group	n
<chr>	<int>	<chr>	<int>
Afghanistan	1980	new_sp_m014	NA
Afghanistan	1981	new_sp_m014	NA
Afghanistan	1982	new_sp_m014	NA
Afghanistan	1983	new_sp_m014	NA
Afghanistan	1984	new_sp_m014	NA
Afghanistan	1985	new_sp_m014	NA

who2 %>%
separate(group, sep="_", into=c("type", "method", "age_group")) -> who3

who3 %>% head

A tibble: 6 × 6

country	year	type	method	age_group	n
<chr>	<int>	<chr>	<chr>	<chr>	<int>
Afghanistan	1980	new	sp	m014	NA
Afghanistan	1981	new	sp	m014	NA
Afghanistan	1982	new	sp	m014	NA
Afghanistan	1983	new	sp	m014	NA
Afghanistan	1984	new	sp	m014	NA
Afghanistan	1985	new	sp	m014	NA

who3 %>%
drop_na(n) -> who4

who4 %>% head

A tibble: 6 × 6

country	year	type	method	age_group	n
<chr>	<int>	<chr>	<chr>	<chr>	<int>
Afghanistan	1997	new	sp	m014	0
Afghanistan	1998	new	sp	m014	30
Afghanistan	1999	new	sp	m014	8
Afghanistan	2000	new	sp	m014	52
Afghanistan	2001	new	sp	m014	129
Afghanistan	2002	new	sp	m014	90

who4 %>%
   filter(str_detect(country, '^Z')) %>%
   group_by(country, method) %>%
   summarize(count=sum(n))

A grouped_df: 8 × 3

country	method	count
<chr>	<chr>	<int>
Zambia	ep	19082
Zambia	rel	40638
Zambia	sn	37054
Zambia	sp	167064
Zimbabwe	ep	41719
Zimbabwe	rel	32899
Zimbabwe	sn	152573
Zimbabwe	sp	133224