forked from markrt/eurovision_2022
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathimport_convert_data.R
112 lines (104 loc) · 4.95 KB
/
import_convert_data.R
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
# i've copied and pasted a bunch of data from here
# https://eurovision.tv/event/turin-2022/grand-final/results/united-kingdom
# (and equivalents for all other countries)
# into an excel sheet
# this script converts it into a long csv for anyone to use
# load packages
library(tidyverse)
library(readxl)
# define path
path <- "results_2022.xlsx"
# load data
mad <- path %>%
excel_sheets() %>%
set_names() %>%
map_df(read_excel,
path = path,
.id = "points_from")
# rename columns, export
mad %>%
select(-Juror) %>%
rename(juror_a_ranking =
A,
juror_b_ranking =
B,
juror_c_ranking =
C,
juror_d_ranking =
D,
juror_e_ranking =
E,
jury_rank =
`Jury rank`,
televoting_rank =
`Televoting rank`,
points_to =
"Country"
) %>%
write_csv("eurovision_2022_clean.csv")
# clean up the jury_rank and televoting_rank columns,
# add points
mad %>%
select(-Juror) %>%
rename(juror_a_ranking =
A,
juror_b_ranking =
B,
juror_c_ranking =
C,
juror_d_ranking =
D,
juror_e_ranking =
E,
jury_rank =
`Jury rank`,
televote_rank =
`Televoting rank`,
points_to =
"Country"
) %>%
mutate(jury_rank = as.numeric(str_squish(str_sub(jury_rank, -4, -3)))) %>%
mutate(televote_rank = as.numeric(str_squish(str_sub(televote_rank, -4, -3)))) %>%
mutate(jury_points = if_else(jury_rank == 1,
12,
if_else(jury_rank == 2,
10,
if_else(jury_rank == 3,
8,
if_else(jury_rank == 4,
7,
if_else(jury_rank == 5,
6,
if_else(jury_rank == 6,
5,
if_else(jury_rank == 7,
4,
if_else(jury_rank == 8,
3,
if_else(jury_rank == 9,
2,
if_else(jury_rank == 10,
1,
0)))))))))))%>%
mutate(televote_points = if_else(televote_rank == 1,
12,
if_else(televote_rank == 2,
10,
if_else(televote_rank == 3,
8,
if_else(televote_rank == 4,
7,
if_else(televote_rank == 5,
6,
if_else(televote_rank == 6,
5,
if_else(televote_rank == 7,
4,
if_else(televote_rank == 8,
3,
if_else(televote_rank == 9,
2,
if_else(televote_rank == 10,
1,
0))))))))))) %>%
write_csv("eurovision_2022_clean_with_points.csv")