RID GroupAssignment sc_pre sc_post
1 1 Control 3.900000 3.800000
2 2 Direct 5.150000 5.263158
3 3 Indirect 4.100000 4.150000
4 4 Control 4.650000 5.100000
5 5 Direct 3.650000 3.600000
6 6 Indirect 4.350000 4.650000
7 7 Control 4.750000 4.400000
8 8 Direct 4.600000 4.650000
9 9 Indirect 4.200000 4.150000
10 10 Control 5.800000 5.750000
11 11 Direct 4.400000 4.800000
12 12 Indirect 4.100000 4.250000
13 13 Control 5.400000 5.600000
14 14 Direct 5.250000 5.350000
15 15 Indirect 5.750000 5.700000
16 16 Control 3.150000 2.600000
17 17 Direct 3.500000 3.500000
18 18 Indirect 5.050000 5.050000
19 19 Control 5.150000 5.550000
20 20 Direct 4.550000 4.700000
21 21 Indirect 1.850000 1.800000
22 22 Control 5.600000 5.450000
23 23 Direct 3.300000 3.550000
24 24 Indirect 3.650000 3.700000
25 25 Control 4.900000 5.450000
26 26 Direct 3.750000 4.300000
27 27 Indirect 4.800000 5.050000
28 28 Control 4.450000 4.850000
29 29 Direct 3.750000 3.450000
30 30 Indirect 4.350000 4.000000
31 31 Control 3.150000 3.600000
32 32 Direct 4.750000 4.950000
33 33 Indirect 3.950000 4.850000
34 34 Control 4.700000 4.450000
35 35 Direct 4.650000 5.000000
36 36 Indirect 4.250000 4.350000
37 37 Control 3.350000 3.900000
38 38 Direct 4.450000 4.450000
39 39 Indirect 5.200000 5.050000
40 40 Control 4.150000 4.000000
41 41 Direct 4.550000 4.750000
42 42 Indirect 3.450000 3.684211
43 43 Control 5.050000 5.100000
44 44 Direct 4.100000 4.100000
45 45 Indirect 5.200000 5.100000
46 46 Control 3.300000 3.200000
47 47 Direct 1.800000 2.800000
48 48 Indirect 3.200000 3.315789
49 49 Control 3.650000 3.350000
50 50 Direct 5.200000 5.250000
51 51 Indirect 3.800000 4.550000
52 52 Control 3.850000 3.450000
53 53 Direct 3.650000 4.150000
54 54 Indirect 4.950000 4.850000
55 55 Control 5.300000 5.450000
56 56 Direct 4.900000 5.000000
57 57 Indirect 3.050000 3.350000
58 58 Control 3.800000 3.300000
59 59 Direct 2.400000 2.700000
60 60 Indirect 5.100000 5.400000
61 61 Control 5.150000 5.100000
62 62 Direct 4.700000 4.944444
63 63 Indirect 4.000000 4.050000
64 64 Control 3.100000 2.900000
65 65 Direct 5.100000 5.650000
66 66 Indirect 4.600000 4.500000
67 67 Control 5.400000 5.200000
68 68 Direct 5.200000 5.450000
69 69 Indirect 5.050000 5.150000
70 70 Control 3.550000 4.200000
71 71 Direct 4.450000 4.500000
72 72 Indirect 3.700000 3.700000
73 73 Control 4.350000 4.300000
74 74 Direct 5.400000 5.650000
75 75 Indirect 5.200000 5.400000
76 76 Control 5.200000 5.200000
77 77 Direct 3.850000 4.050000
78 78 Indirect 4.600000 4.850000
79 79 Control 4.850000 5.150000
80 80 Direct 2.150000 2.350000
81 81 Indirect 3.900000 4.100000
82 82 Control 5.400000 5.555556
83 83 Direct 5.150000 5.450000
84 84 Indirect 3.700000 4.700000
85 85 Control 4.750000 4.750000
86 86 Direct 4.800000 5.150000
87 87 Indirect 4.000000 4.050000
88 88 Control 5.000000 5.350000
89 89 Direct 4.400000 4.777778
90 90 Indirect 5.300000 4.500000
91 91 Control 5.200000 5.000000
92 92 Direct 5.150000 5.300000
93 93 Indirect 3.900000 3.750000
94 94 Control 4.200000 4.050000
95 95 Direct 4.800000 5.350000
96 96 Indirect 4.500000 4.650000
97 97 Control 3.300000 3.250000
98 98 Direct 5.550000 5.650000
99 99 Indirect 5.000000 4.850000
100 100 Control 3.300000 3.050000
101 101 Direct 3.850000 3.800000
102 102 Indirect 2.750000 3.250000
103 103 Control 3.550000 4.000000
104 104 Direct 5.450000 5.450000
105 105 Indirect 5.800000 5.789474
106 106 Control 4.300000 4.450000
107 107 Direct 5.800000 5.900000
108 108 Indirect 4.600000 4.800000
109 109 Control 4.450000 4.450000
110 110 Direct 4.550000 4.900000
111 111 Indirect 2.900000 3.105263
112 112 Control 5.750000 5.450000
113 113 Direct 3.600000 3.900000
114 114 Indirect 5.400000 5.300000
115 115 Control 5.550000 3.000000
116 116 Direct 4.150000 4.150000
117 117 Indirect 5.100000 5.000000
118 118 Control 4.850000 4.650000
119 119 Direct 4.700000 4.650000
120 120 Indirect 3.500000 4.050000
121 121 Control 5.200000 5.600000
122 122 Direct 5.300000 5.600000
123 123 Indirect 4.450000 4.850000
124 124 Control 4.400000 4.300000
125 125 Direct 4.500000 4.550000
126 126 Indirect 4.800000 5.000000
127 127 Control 5.350000 5.450000
128 128 Direct 5.200000 5.450000
129 129 Indirect 4.450000 4.350000
130 130 Control 3.700000 4.050000
131 131 Direct 3.550000 3.550000
132 132 Indirect 4.578947 4.800000
133 133 Control 4.900000 4.400000
134 134 Direct 5.150000 5.800000
135 135 Indirect 5.000000 5.000000
136 136 Control 3.750000 3.700000
137 137 Direct 4.450000 4.850000
138 138 Indirect 3.050000 2.750000
139 139 Control 5.000000 5.200000
140 140 Direct 3.000000 5.200000
141 141 Indirect 4.500000 4.950000
142 142 Control 4.450000 4.550000
143 143 Direct 4.600000 4.550000
144 144 Indirect 4.700000 5.500000
145 145 Control 3.950000 3.500000
146 146 Direct 4.050000 4.300000
147 147 Indirect 4.100000 4.550000
148 148 Control 3.150000 3.700000
149 149 Direct 5.250000 5.350000
150 150 Indirect 4.050000 4.000000
151 151 Control 4.700000 4.700000
152 152 Direct 5.050000 5.100000
153 153 Indirect 5.250000 5.350000
154 154 Control 5.050000 4.900000
155 155 Direct 4.750000 4.473684
156 156 Indirect 4.100000 4.150000
157 157 Control 2.650000 2.900000
158 158 Direct 3.800000 4.263158
159 159 Indirect 3.450000 3.350000
160 160 Control 4.250000 4.250000
161 161 Direct 4.700000 5.000000
162 162 Indirect 3.750000 3.800000
163 163 Control 2.850000 2.950000
164 164 Direct 4.650000 5.050000
165 165 Indirect 4.950000 5.300000
166 166 Control 4.450000 4.850000
167 167 Direct 5.050000 5.050000
168 168 Indirect 5.150000 5.625000
169 169 Control 3.600000 3.500000
170 170 Direct 4.600000 4.750000
171 171 Indirect 3.800000 3.700000
172 172 Control 3.300000 3.550000
173 173 Direct 4.950000 5.050000
174 174 Indirect 4.700000 4.250000
175 175 Control 3.850000 3.650000
176 176 Direct 4.750000 4.700000
177 177 Indirect 5.350000 5.550000
178 178 Control 3.900000 4.250000
179 179 Direct 5.100000 5.200000
180 180 Indirect 4.300000 4.150000
181 181 Control 5.350000 5.550000
182 182 Direct 4.750000 4.850000
183 183 Indirect 5.200000 5.350000
184 184 Control 4.050000 3.900000
185 185 Direct 4.850000 5.050000
186 186 Indirect 5.300000 5.850000
187 187 Control 3.750000 3.600000
188 188 Direct 1.600000 3.600000
189 189 Indirect 5.000000 5.000000
190 190 Control 4.700000 4.950000
191 191 Direct 4.550000 4.800000
192 192 Indirect 4.650000 4.400000
193 193 Control 4.650000 5.200000
194 194 Direct 5.450000 5.450000
195 195 Indirect 4.700000 4.750000
196 196 Control 4.800000 4.200000
197 197 Direct 5.050000 5.250000
198 198 Indirect 4.450000 4.150000
199 199 Control 4.700000 5.100000
200 200 Direct 3.900000 3.210526
201 201 Indirect 3.500000 3.600000
202 202 Control 4.200000 4.500000
203 203 Direct 3.850000 3.600000
204 204 Indirect 2.050000 2.150000
205 205 Control 3.600000 3.700000
206 206 Direct 5.050000 5.300000
207 207 Indirect 4.050000 4.150000
208 208 Control 4.450000 4.550000
209 209 Direct 5.157895 5.100000
210 210 Indirect 5.150000 5.600000
211 211 Control 4.500000 4.500000
212 212 Direct 3.950000 4.450000
213 213 Indirect 5.150000 5.800000
214 214 Control 3.200000 3.000000
215 215 Direct 3.050000 3.100000
216 216 Indirect 3.500000 4.052632
217 217 Control 4.300000 4.550000
218 218 Direct 2.250000 3.500000
219 219 Indirect 3.250000 3.200000
220 220 Control 5.500000 5.750000
221 221 Direct 5.900000 6.000000
222 222 Indirect 5.350000 5.550000
223 223 Control 5.100000 5.350000
224 224 Direct 5.150000 5.350000
225 225 Indirect 3.500000 3.650000
226 226 Control 5.550000 5.700000
227 227 Direct 4.400000 4.650000
228 228 Indirect 3.100000 3.421053
229 229 Control 5.050000 5.050000
230 230 Direct 5.200000 5.100000
231 231 Indirect 4.450000 4.950000
232 232 Control 4.850000 5.105263
233 233 Direct 5.300000 5.550000
234 234 Indirect 4.150000 4.250000
235 235 Control 5.450000 5.400000
236 236 Direct 3.450000 4.000000
237 237 Indirect 5.400000 5.550000
238 238 Control 2.800000 2.900000
239 239 Direct 4.050000 3.250000
240 240 Indirect 4.800000 4.700000
241 241 Control 3.850000 3.800000
242 242 Direct 3.500000 4.000000
243 243 Indirect 3.650000 3.900000
244 244 Control 4.850000 5.250000
245 245 Direct 4.750000 5.250000
246 246 Indirect 4.250000 4.750000
247 247 Control 5.750000 5.750000
248 248 Direct 5.800000 5.650000
249 249 Indirect 5.250000 5.850000
250 250 Direct 4.350000 4.650000
251 251 Indirect 5.100000 5.100000
252 252 Control 3.850000 3.950000
253 253 Direct 4.550000 4.450000
254 254 Indirect 4.500000 4.550000
255 255 Control 3.150000 2.750000
256 256 Direct 3.900000 4.200000
257 257 Indirect 5.250000 5.700000
258 258 Control 5.450000 5.550000
259 259 Direct 4.894737 5.200000
260 260 Indirect 4.200000 4.400000
261 261 Control 5.550000 5.350000
262 262 Direct 5.400000 5.600000
263 263 Indirect 4.800000 4.900000
264 264 Control 5.100000 5.400000
265 265 Direct 4.200000 4.900000
266 266 Indirect 3.950000 3.842105
267 267 Control 4.650000 4.900000
268 268 Direct 2.350000 2.950000
269 269 Indirect 3.900000 3.950000
270 270 Control 4.650000 4.600000
271 271 Direct 3.600000 3.550000
272 272 Indirect 3.900000 4.150000
273 273 Control 5.100000 5.050000
274 274 Direct 4.250000 4.800000
275 275 Indirect 5.000000 5.150000
276 276 Control 4.850000 5.300000
277 277 Direct 4.450000 4.350000
278 278 Indirect 4.100000 3.600000
279 279 Control 5.250000 5.000000
280 280 Direct 4.500000 4.850000
281 281 Indirect 5.450000 5.350000
282 282 Control 5.450000 5.550000
283 283 Direct 4.650000 4.450000
284 300 Indirect 4.450000 4.700000Lecture 14: Reshaping data
So far
select: choose certain columnsfilter: choose certain rowssummarize: calculate summary statisticsgroup_by: group rows togethermutate: create new columnscount: count the number of rowsarrange: re-order the rowsacross: apply functions across columns
Back to the dog data
Question: What if we want to fit a model with this data?
Fitting a model
Want code that looks like this:
Problem: We don’t have a column for stage! Or a column for score!
pivot_longer
# A tibble: 568 × 4
RID GroupAssignment stage score
<int> <chr> <chr> <dbl>
1 1 Control sc_pre 3.9
2 1 Control sc_post 3.8
3 2 Direct sc_pre 5.15
4 2 Direct sc_post 5.26
5 3 Indirect sc_pre 4.1
6 3 Indirect sc_post 4.15
7 4 Control sc_pre 4.65
8 4 Control sc_post 5.1
9 5 Direct sc_pre 3.65
10 5 Direct sc_post 3.6
# ℹ 558 more rowspivot_longer
(Image from R for Data Science)
pivot_longer
Another example:
# A tibble: 260 × 38
Adult (15+) literacy rate …¹ `1975` `1976` `1977` `1978` `1979` `1980` `1981`
<chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Afghanistan NA NA NA NA 4.99 NA NA
2 Albania NA NA NA NA NA NA NA
3 Algeria NA NA NA NA NA NA NA
4 Andorra NA NA NA NA NA NA NA
5 Angola NA NA NA NA NA NA NA
6 Anguilla NA NA NA NA NA NA NA
7 Antigua and Barbuda NA NA NA NA NA NA NA
8 Argentina NA NA NA NA NA 93.6 NA
9 Armenia NA NA NA NA NA NA NA
10 Aruba NA NA NA NA NA NA NA
# ℹ 250 more rows
# ℹ abbreviated name: ¹`Adult (15+) literacy rate (%). Female`
# ℹ 30 more variables: `1982` <dbl>, `1983` <dbl>, `1984` <dbl>, `1985` <dbl>,
# `1986` <dbl>, `1987` <dbl>, `1988` <dbl>, `1989` <dbl>, `1990` <dbl>,
# `1991` <dbl>, `1992` <dbl>, `1993` <dbl>, `1994` <dbl>, `1995` <dbl>,
# `1996` <dbl>, `1997` <dbl>, `1998` <dbl>, `1999` <dbl>, `2000` <dbl>,
# `2001` <dbl>, `2002` <dbl>, `2003` <dbl>, `2004` <dbl>, `2005` <dbl>, …How might we want to restructure this data?
pivot_longer
# A tibble: 260 × 38
Adult (15+) literacy rate …¹ `1975` `1976` `1977` `1978` `1979` `1980` `1981`
<chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Afghanistan NA NA NA NA 4.99 NA NA
2 Albania NA NA NA NA NA NA NA
3 Algeria NA NA NA NA NA NA NA
4 Andorra NA NA NA NA NA NA NA
5 Angola NA NA NA NA NA NA NA
6 Anguilla NA NA NA NA NA NA NA
7 Antigua and Barbuda NA NA NA NA NA NA NA
8 Argentina NA NA NA NA NA 93.6 NA
9 Armenia NA NA NA NA NA NA NA
10 Aruba NA NA NA NA NA NA NA
# ℹ 250 more rows
# ℹ abbreviated name: ¹`Adult (15+) literacy rate (%). Female`
# ℹ 30 more variables: `1982` <dbl>, `1983` <dbl>, `1984` <dbl>, `1985` <dbl>,
# `1986` <dbl>, `1987` <dbl>, `1988` <dbl>, `1989` <dbl>, `1990` <dbl>,
# `1991` <dbl>, `1992` <dbl>, `1993` <dbl>, `1994` <dbl>, `1995` <dbl>,
# `1996` <dbl>, `1997` <dbl>, `1998` <dbl>, `1999` <dbl>, `2000` <dbl>,
# `2001` <dbl>, `2002` <dbl>, `2003` <dbl>, `2004` <dbl>, `2005` <dbl>, …pivot_longer
litF |>
rename(country = starts_with("Adult")) |>
pivot_longer(
cols = -country,
names_to = "year",
values_to = "literacy_rate"
) |>
drop_na(literacy_rate)# A tibble: 571 × 3
country year literacy_rate
<chr> <chr> <dbl>
1 Afghanistan 1979 4.99
2 Afghanistan 2011 13
3 Albania 2001 98.3
4 Albania 2008 94.7
5 Albania 2011 95.7
6 Algeria 1987 35.8
7 Algeria 2002 60.1
8 Algeria 2006 63.9
9 Angola 2001 54.2
10 Angola 2011 58.6
# ℹ 561 more rowspivot_longer
litF |>
rename(country = starts_with("Adult")) |>
pivot_longer(
cols = -country,
names_to = "year",
values_to = "literacy_rate",
values_drop_na = T
)# A tibble: 571 × 3
country year literacy_rate
<chr> <chr> <dbl>
1 Afghanistan 1979 4.99
2 Afghanistan 2011 13
3 Albania 2001 98.3
4 Albania 2008 94.7
5 Albania 2011 95.7
6 Algeria 1987 35.8
7 Algeria 2002 60.1
8 Algeria 2006 63.9
9 Angola 2001 54.2
10 Angola 2011 58.6
# ℹ 561 more rowsBack to the dog data
# A tibble: 568 × 4
RID GroupAssignment stage score
<int> <chr> <chr> <dbl>
1 1 Control sc_pre 3.9
2 1 Control sc_post 3.8
3 2 Direct sc_pre 5.15
4 2 Direct sc_post 5.26
5 3 Indirect sc_pre 4.1
6 3 Indirect sc_post 4.15
7 4 Control sc_pre 4.65
8 4 Control sc_post 5.1
9 5 Direct sc_pre 3.65
10 5 Direct sc_post 3.6
# ℹ 558 more rowsDoes the stage column only contain information about stage?
Back to the dog data
sc_data |>
pivot_longer(cols = c(sc_pre, sc_post),
names_to = c("measurement", "stage"),
names_sep = "_",
values_to = "score")# A tibble: 568 × 5
RID GroupAssignment measurement stage score
<int> <chr> <chr> <chr> <dbl>
1 1 Control sc pre 3.9
2 1 Control sc post 3.8
3 2 Direct sc pre 5.15
4 2 Direct sc post 5.26
5 3 Indirect sc pre 4.1
6 3 Indirect sc post 4.15
7 4 Control sc pre 4.65
8 4 Control sc post 5.1
9 5 Direct sc pre 3.65
10 5 Direct sc post 3.6
# ℹ 558 more rowsWorking with all the measurements
cleaned_data |>
pivot_longer(cols = -c(RID, GroupAssignment),
names_to = c("measurement", "stage"),
names_sep = "_",
values_to = "score")# A tibble: 4,544 × 5
RID GroupAssignment measurement stage score
<int> <chr> <chr> <chr> <dbl>
1 1 Control pa pre 3.2
2 1 Control pa post 3.8
3 1 Control happiness pre 2.33
4 1 Control happiness post 3.33
5 1 Control sc pre 3.9
6 1 Control sc post 3.8
7 1 Control fs pre 6.12
8 1 Control fs post 6
9 1 Control stress pre 2
10 1 Control stress post 2
# ℹ 4,534 more rowsFitting a model
long_data <- cleaned_data |>
pivot_longer(cols = -c(RID, GroupAssignment),
names_to = c("measurement", "stage"),
names_sep = "_",
values_to = "score")
lm(score ~ GroupAssignment + stage, data = long_data)
Call:
lm(formula = score ~ GroupAssignment + stage, data = long_data)
Coefficients:
(Intercept) GroupAssignmentDirect GroupAssignmentIndirect
3.16307 -0.10118 -0.04836
stagepre
0.13805 But what if I want to fit a separate model for each well-being/ill-being measurement?
pivot_longer
# A tibble: 4,544 × 5
RID GroupAssignment measurement stage score
<int> <chr> <chr> <chr> <dbl>
1 1 Control pa pre 3.2
2 1 Control pa post 3.8
3 1 Control happiness pre 2.33
4 1 Control happiness post 3.33
5 1 Control sc pre 3.9
6 1 Control sc post 3.8
7 1 Control fs pre 6.12
8 1 Control fs post 6
9 1 Control stress pre 2
10 1 Control stress post 2
# ℹ 4,534 more rowsPerhaps we want to have a column for stage, and a column for each measurement?
pivot_longer
cleaned_data |>
pivot_longer(cols = -c(RID, GroupAssignment),
names_to = c(".value", "stage"),
names_sep = "_")# A tibble: 568 × 11
RID GroupAssignment stage pa happiness sc fs stress homesick
<int> <chr> <chr> <dbl> <dbl> <dbl> <dbl> <int> <int>
1 1 Control pre 3.2 2.33 3.9 6.12 2 3
2 1 Control post 3.8 3.33 3.8 6 2 3
3 2 Direct pre 3 3.33 5.15 5.25 2 4
4 2 Direct post 3.2 4 5.26 6 1 2
5 3 Indirect pre 2.8 2.67 4.1 5.38 4 3
6 3 Indirect post 3 3.33 4.15 5.38 3 2
7 4 Control pre 4.2 3 4.65 6.38 2 1
8 4 Control post 3.8 3 5.1 6.38 2 1
9 5 Direct pre 3.4 2.67 3.65 5.88 3 2
10 5 Direct post 4 2.67 3.6 5.88 4 2
# ℹ 558 more rows
# ℹ 2 more variables: lonely <dbl>, na <dbl>pivot_wider
# A tibble: 4,544 × 5
RID GroupAssignment measurement stage score
<int> <chr> <chr> <chr> <dbl>
1 1 Control pa pre 3.2
2 1 Control pa post 3.8
3 1 Control happiness pre 2.33
4 1 Control happiness post 3.33
5 1 Control sc pre 3.9
6 1 Control sc post 3.8
7 1 Control fs pre 6.12
8 1 Control fs post 6
9 1 Control stress pre 2
10 1 Control stress post 2
# ℹ 4,534 more rowspivot_wider
long_data |>
pivot_wider(id_cols = c(RID, GroupAssignment, stage),
names_from = measurement,
values_from = score)# A tibble: 568 × 11
RID GroupAssignment stage pa happiness sc fs stress homesick
<int> <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 1 Control pre 3.2 2.33 3.9 6.12 2 3
2 1 Control post 3.8 3.33 3.8 6 2 3
3 2 Direct pre 3 3.33 5.15 5.25 2 4
4 2 Direct post 3.2 4 5.26 6 1 2
5 3 Indirect pre 2.8 2.67 4.1 5.38 4 3
6 3 Indirect post 3 3.33 4.15 5.38 3 2
7 4 Control pre 4.2 3 4.65 6.38 2 1
8 4 Control post 3.8 3 5.1 6.38 2 1
9 5 Direct pre 3.4 2.67 3.65 5.88 3 2
10 5 Direct post 4 2.67 3.6 5.88 4 2
# ℹ 558 more rows
# ℹ 2 more variables: lonely <dbl>, na <dbl>Class activity
https://sta279-f23.github.io/class_activities/ca_lecture_14.html