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
112
113
114
115
116
117
118
119
120
121
|
line15 <- c(113,91,117,195)
line16 <- c(107,108,118,187)
line17 <- c(106,104,113,166)
line18 <- c(107,110,116,186)
# 数据导入以及筛选
grade15 <- read_xls("E:/1R/website/grade15.xls",sheet = 1,range = "A2:I1124",col_names = TRUE) %>%
select(-(年级:班级),-班名) %>%
dplyr::filter(总分 >= sum(line15)&
!((语文>=line15[1])&(数学>=line15[2])&(英语>=line15[3])&(理综>=line15[4])))
grade16 <- read_xls("E:/1R/website/grade16.xls",sheet = 1,range = "C3:H1126",col_names = TRUE) %>%
dplyr::filter(总分 >= sum(line16)&
!((语文>=line16[1])&(数学>=line16[2])&(英语>=line16[3])&(理综>=line16[4])))
grade17 <- read_xls("E:/1R/website/grade17.xls",sheet = 1,range = "B1:G1065",col_names = TRUE) %>%
dplyr::filter(理综 <= 300) %>%
dplyr::filter(总分 >= sum(line17)&
!((语文>=line17[1])&(数学>=line17[2])&(英语>=line17[3])&(理综>=line17[4])))
grade18 <- read_xls("E:/1R/website/grade18.xls",sheet = 1,range = "C1:J989",col_names = TRUE) %>%
select(-备注) %>%
dplyr::filter(总分 >= sum(line18)&
!((语文>=line18[1])&(数学>=line18[2])&(英语>=line18[3])&(理综>=line18[4])))
# 各科数据筛选
chinese15 <- grade15 %>%
dplyr::filter(语文<line15[1]) %>%
select(语文)
chinese15$语文 <- as.numeric(chinese15$语文)
chinese16 <- grade16 %>%
dplyr::filter(语文<line16[1]) %>%
select(语文)
chinese17 <- grade17 %>%
dplyr::filter(语文<line17[1]) %>%
select(语文)
chinese18 <- grade18 %>%
dplyr::filter(语文<line18[1]) %>%
select(语文)
c <- rbind(chinese15,chinese16,chinese17,chinese18)
sdc <- rbind(-chinese15+line15[1],-chinese16+line16[1],-chinese17+line17[1],-chinese18+line18[1])
math15 <- grade15 %>%
dplyr::filter(数学<line15[2]) %>%
select(数学)
math16 <- grade16 %>%
dplyr::filter(数学<line16[2]) %>%
select(数学)
math17 <- grade17 %>%
dplyr::filter(数学<line17[2]) %>%
select(数学)
math18 <- grade18 %>%
dplyr::filter(数学<line18[2]) %>%
select(数学)
m <- rbind(math15,math16,math17,math18)
sdm <- rbind(-math15+line15[2],-math16+line16[2],-math17+line17[2],-math18+line18[2])
english15 <- grade15 %>%
dplyr::filter(英语<line15[3]) %>%
select(英语)
english16 <- grade16 %>%
dplyr::filter(英语<line16[3]) %>%
select(英语)
english17 <- grade17 %>%
dplyr::filter(英语<line17[3]) %>%
select(英语)
english18 <- grade18 %>%
dplyr::filter(英语<line18[3]) %>%
select(英语)
e <- rbind(english15,english16,english17,english18)
sde <- rbind(-english15+line15[3],-english16+line16[3],-english17+line17[3],-english18+line18[3])
science15 <- grade15 %>%
dplyr::filter(理综<line15[4]) %>%
select(理综)
science16 <- grade16 %>%
dplyr::filter(理综<line16[4]) %>%
select(理综)
science17 <- grade17 %>%
dplyr::filter(理综<line17[4]) %>%
select(理综)
science18 <- grade18 %>%
dplyr::filter(理综<line18[4]) %>%
select(理综)
s <- rbind(science15,science16,science17,science18)
sds <- rbind(-science15+line15[4],-science16+line16[4],-science17+line17[4],-science18+line18[4])
# 分差统计整理、更名
# This rename function is in plyr package!
cc <- sdc %>%
count("语文") %>%
rename(c("语文" = "分差", "freq" = "语文"))
ee <- sde %>%
count("英语") %>%
rename(c("英语" = "分差", "freq" = "英语"))
mm <- sdm %>%
count("数学") %>%
rename(c("数学" = "分差", "freq" = "数学"))
ss <- sds %>%
count("理综") %>%
rename(c("理综" = "分差", "freq" = "理综"))
# 分差数据合并
intg <- cc %>%
full_join(ee,by = "分差") %>%
full_join(mm,by = "分差") %>%
full_join(ss,by = "分差") %>%
arrange(分差)
intg[is.na(intg)] <- 0
intg <- intg %>%
melt(id = "分差") %>%
plyr::rename(c("variable" = "科目", "value" = "人数"))
# 数据可视化以及保存
hc <- hchart(intg,"column",hcaes(x = "分差", y = "人数", group = "科目")) %>%
hc_plotOptions(column= list(dataLabels = list(enabled =TRUE))) %>%
hc_title(text = "各科一本线分差分析图") %>%
hc_yAxis(title = list(text = "人数",align = "middle"),
tickInterval = 4,tickAmount = 4)
hc
htmlwidgets::saveWidget(hc,file = "oneline4.html", selfcontained = TRUE)
|
