setwd("C:/Users/Mac/Desktop")
library(openxlsx)
library(ggplot2)
#1
d1=read.xlsx("covid.xlsx", sheet = 1)
d2=read.xlsx("covid.xlsx", sheet = 2)
covidData=rbind(d1,d2)
colnames(covidData)

#2
#format modification
covidData$date=as.Date(covidData$date-43852,origin="2020-01-31")
library(lubridate)
#Extract year and month
covidData$year=year(covidData$date)
covidData$month=month(covidData$date)
covidData$year_month=paste(covidData$year,covidData$month,sep="-")
#The cycle adds up the new deaths and new cases in each country separately, each month  
ee=unique(covidData$year_month)
gg=unique(covidData$location)
f3=NULL
for(j in gg){
  for(i in ee){
    f1=covidData[covidData$year_month==i&covidData$location==j,]
    new_cases=sum(f1$new_cases,na.rm=T)
    new_deaths=sum(f1$new_deaths,na.rm=T)
    f2=c(new_cases,new_deaths)
    f2=cbind(f2,c(paste("new_cases",j),paste("new_deaths",j)),i)
    f3=rbind(f3,f2)
  }
}
#Convert to data frame format and give column titles
f3=as.data.frame(f3)
colnames(f3)=c("value","index","Date")
f3$Date=ym(f3$Date)
f3$value=as.numeric(f3$value)
#Painting
ggplot(f3)+geom_line(aes(Date,value,color=index))


#3
#Extract the corresponding 4 months
date=c("2020-4","2020-10","2021-4","2021-10")
covid_19=NULL
#Figure out the maximum and minimum number of new cases in each country in each month
for(j in gg){
  for(i in date){
    f1=covidData[covidData$year_month==i&covidData$location==j,]
    min=min(f1$new_cases,na.rm=T)
    max=max(f1$new_cases,na.rm=T)
    median=median(f1$new_cases,na.rm=T)
    mean=round(mean(f1$new_cases,na.rm=T),2)
    sd=round(sd(f1$new_cases,na.rm=T),2)
    interval=paste(min,max,sep="~")
    city=j
    Date=i
    f2=cbind(Date,city,min,max,median,mean,sd,interval)
    covid_19=rbind(covid_19,f2)
  }
}
covid_19=as.data.frame(covid_19)
covid_19

#4
#data extraction
new1=covidData[covidData$year_month=="2020-10"&covidData$location=="United Kingdom",]$new_cases
new2=covidData[covidData$year_month=="2021-10"&covidData$location=="United Kingdom",]$new_cases
#carry out T test
# Null hypothesis: There is no difference in the number of new cases in the UK between October 2020 and October 2021  
# Alternative hypothesis: There is a difference in the number of new cases in the UK between October 2020 and October 2021  
t.test(new1,new2)
# P < 0.05, rejecting the null hypothesis, indicates a significant difference in the number of new cases in the UK between October 2020 and October 2021  

#5
#data extraction
new=covidData[covidData$location=="China",]
nn=new[new$year_month=="2020-4"|new$year_month=="2020-10"|new$year_month=="2021-4"|new$year_month=="2021-10",]
#analysis of variance
# Null hypothesis: There is no difference in the number of new cases in China in April 2020, October 2020, April 2021, and October 2021  
# Alternative hypothesis: There is a difference in the number of new cases in China in April 2020, October 2020, April 2021 and October 2021  
aov1 <- aov(new_cases~year_month,nn) 
summary(aov1)
# P < 0.05, reject the original false color, indicates a significant difference in the number of new cases in China in April 2020, October 2020, April 2021 and October 2021