I have data frame in R called data:
data
# A tibble: 192 × 4
Year Category Favor Percentage
<dbl> <chr> <chr> <dbl>
1 2002 A Good 35.8
2 2002 A Mediocre 31.9
3 2002 A Bad 45.3
4 2002 B Good 51.3
5 2002 B Mediocre 42.3
6 2002 B Bad 26.4
7 2002 C Good 64.4
8 2002 C Mediocre 33.4
9 2002 C Bad 24.2
10 2002 D Good 56.2
I want to pivot wider it in order to be like ideally like the following :
| category | Bad - 1998 | Bad - 1999 | ... | Bad - 2002 | Medriocre - 1998 | ... | Medriocre -2002 | Good - 1998 ...Good - 2002 |
|---|---|---|---|---|---|---|---|---|
| A | ||||||||
| B | ||||||||
| C | ||||||||
| D | ||||||||
| E | ||||||||
| F | ||||||||
| G | ||||||||
| H | ||||||||
| I | ||||||||
| J | ||||||||
| K | ||||||||
| L | ||||||||
| M | ||||||||
| N | ||||||||
| O | ||||||||
| P |
i.e the Category column to be the first column and then starting from second Bad and 1998 third column Bad- 1999, fourth Bad- 2001,fifth Bad- 2002, sixth Medriocre - 1998,Medriocre - 1999,Medriocre - 2001,Medriocre - 2002,Good - 1998,Good - 1999,Good - 2001 and finally the column Good - 2002.
How can I do it in R using tidyverse functions ?
Data
dput(data)
structure(list(Year = c(2002, 2002, 2002, 2002, 2002, 2002, 2002,
2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002,
2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002,
2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002,
2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 1998, 1998, 1998,
1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998,
1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998,
1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998,
1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998, 1998,
1998, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999,
1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999,
1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999,
1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999, 1999,
1999, 1999, 1999, 1999, 1999, 2001, 2001, 2001, 2001, 2001, 2001,
2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001,
2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001,
2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001,
2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001), Category = c("A",
"A", "A", "B", "B", "B", "C", "C", "C", "D", "D", "D", "E", "E",
"E", "F", "F", "F", "G", "G", "G", "H", "H", "H", "I", "I", "I",
"J", "J", "J", "K", "K", "K", "L", "L", "L", "M", "M", "M", "N",
"N", "N", "O", "O", "O", "P", "P", "P", "A", "A", "A", "B", "B",
"B", "C", "C", "C", "D", "D", "D", "E", "E", "E", "F", "F", "F",
"G", "G", "G", "H", "H", "H", "I", "I", "I", "J", "J", "J", "K",
"K", "K", "L", "L", "L", "M", "M", "M", "N", "N", "N", "O", "O",
"O", "P", "P", "P", "A", "A", "A", "B", "B", "B", "C", "C", "C",
"D", "D", "D", "E", "E", "E", "F", "F", "F", "G", "G", "G", "H",
"H", "H", "I", "I", "I", "J", "J", "J", "K", "K", "K", "L", "L",
"L", "M", "M", "M", "N", "N", "N", "O", "O", "O", "P", "P", "P",
"A", "A", "A", "B", "B", "B", "C", "C", "C", "D", "D", "D", "E",
"E", "E", "F", "F", "F", "G", "G", "G", "H", "H", "H", "I", "I",
"I", "J", "J", "J", "K", "K", "K", "L", "L", "L", "M", "M", "M",
"N", "N", "N", "O", "O", "O", "P", "P", "P"), Favor = c("Good",
"Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre",
"Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad",
"Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good",
"Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre",
"Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad",
"Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good",
"Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre",
"Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad",
"Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good",
"Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre",
"Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad",
"Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good",
"Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre",
"Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad",
"Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good",
"Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre",
"Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad",
"Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good",
"Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre",
"Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad",
"Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good",
"Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre",
"Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad",
"Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good",
"Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre",
"Bad", "Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad",
"Good", "Mediocre", "Bad", "Good", "Mediocre", "Bad", "Good",
"Mediocre", "Bad"), Percentage = c(36.85, 36.88, 46.28, 60.28,
45.3, 36.42, 70.44, 37.39, 34.17, 58.23, 48.77, 34.99, 67.53,
33.46, 32.01, 35.96, 35.33, 62.71, 46.84, 32.92, 42.24, 83.21,
26.67, 16.11, 65.91, 23.94, 46.15, 81.83, 23.86, 27.31, 74.32,
35.09, 33.59, 71.91, 30.92, 28.17, 62.84, 33.06, 27.1, 63.05,
44.81, 26.15, 76.68, 35.99, 23.33, 49.19, 34.58, 42.23, 55.21,
35.37, 34.42, 64.48, 36.53, 33.99, 70.81, 27.1, 31.09, 51.36,
43, 37.65, 64.57, 34.37, 29.06, 35.55, 28.44, 56.01, 56.84, 33.36,
38.8, 79.74, 35.74, 22.52, 66.86, 29.99, 44.15, 89.57, 27.79,
14.64, 82.49, 27.37, 27.14, 75.92, 33.39, 18.69, 69.8, 34.69,
29.51, 75.2, 42.63, 29.17, 90.33, 36.72, 13.95, 52.73, 30, 38.27,
68.14, 40.61, 33.25, 66.2, 33.99, 31.81, 80.38, 26.48, 21.13,
50.5, 40.36, 37.14, 74.17, 31.78, 29.04, 44.91, 35.24, 43.85,
69.23, 35.44, 32.33, 86.44, 24.11, 17.46, 69.69, 33.06, 40.25,
86.37, 21.21, 21.42, 80.11, 35.57, 32.32, 77.2, 32.03, 19.77,
72.98, 28.08, 20.94, 70.81, 29.12, 24.07, 88.14, 22.31, 16.55,
67.49, 44.16, 32.35, 69.03, 39.45, 28.52, 71.97, 37.6, 25.43,
79.06, 38.4, 19.55, 68.94, 37.03, 30.03, 80.74, 30.59, 30.67,
49.07, 45.79, 47.14, 60.1, 27.55, 34.36, 88.54, 30.2, 20.26,
59.42, 22.98, 43.61, 86.84, 16.73, 14.43, 77.42, 22.07, 22.52,
78.85, 23.88, 17.28, 78.22, 39.57, 27.22, 80.17, 26.21, 20.63,
94.63, 28.66, 13.71, 65.86, 31.97, 32.16)), row.names = c(NA,
-192L), class = c("tbl_df", "tbl", "data.frame"))
Probably you can try
data %>%
arrange(
Category,
factor(Favor, levels = c("Bad", "Mediocre", "Good")),
Year
) %>%
pivot_wider(
names_from = c(Favor, Year),
values_from = Percentage,
names_sep = "-"
)
which gives
# A tibble: 16 × 13
Category `Bad-1998` `Bad-1999` `Bad-2001` `Bad-2002` `Mediocre-1998`
<chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 A 34.4 33.2 28.5 46.3 35.4
2 B 34.0 31.8 25.4 36.4 36.5
3 C 31.1 21.1 19.6 34.2 27.1
4 D 37.6 37.1 30.0 35.0 43
5 E 29.1 29.0 30.7 32.0 34.4
6 F 56.0 43.8 47.1 62.7 28.4
7 G 38.8 32.3 34.4 42.2 33.4
8 H 22.5 17.5 20.3 16.1 35.7
9 I 44.2 40.2 43.6 46.2 30.0
10 J 14.6 21.4 14.4 27.3 27.8
11 K 27.1 32.3 22.5 33.6 27.4
12 L 18.7 19.8 17.3 28.2 33.4
13 M 29.5 20.9 27.2 27.1 34.7
14 N 29.2 24.1 20.6 26.2 42.6
15 O 14.0 16.6 13.7 23.3 36.7
16 P 38.3 32.4 32.2 42.2 30
# ℹ 7 more variables: `Mediocre-1999` <dbl>, `Mediocre-2001` <dbl>,
# `Mediocre-2002` <dbl>, `Good-1998` <dbl>, `Good-1999` <dbl>,
# `Good-2001` <dbl>, `Good-2002` <dbl>