library(tidyverse)
library(dplyr)

data <- read.csv('D:\\apc\\global country\\global prevalence\\prevalence female digestive disease.csv',header = T)
colnames(data)
EAPC <- data %>% filter(age=='Age-standardized') %>% 
  filter(metric== 'Rate') %>% 
  filter(measure=='Prevalence') %>% .[,c(2,7,8)]
head(EAPC) 

a <- EAPC %>% filter(location_name=='African Region')
head(a)

a$y <- log(a$y)
head(a$y)

mod_simp_reg<-lm(y~year,data=a)
summary(mod_simp_reg)

summary(mod_simp_reg)[["coefficients"]]

summary(mod_simp_reg)[["coefficients"]][2,1] ##斜率

summary(mod_simp_reg)[["coefficients"]][2,2]

## 率的平均变化???
(exp(summary(mod_simp_reg)[["coefficients"]][2,1])-1)*100

## 可信区间 mean+-1.96*se
(exp(summary(mod_simp_reg)[["coefficients"]][2,1]-1.96*summary(mod_simp_reg)[["coefficients"]][2,2])-1)*100

(exp(summary(mod_simp_reg)[["coefficients"]][2,1]+1.96*summary(mod_simp_reg)[["coefficients"]][2,2])-1)*100

EAPC <- data %>% filter(age=='Age-standardized') %>% 
  filter(metric== 'Rate') %>% 
  filter(measure=='Prevalence') %>% .[,c(2,7,8)]

EAPC_cal <- data.frame(location=unique(EAPC$location_name),
                       EAPC=rep(0,times=length(unique(EAPC$location))),
                       LCI=rep(0,times=length(unique(EAPC$location))),
                       UCI=rep(0,times=length(unique(EAPC$location))))

for (i in 1:length(unique(EAPC$location))){
  country_cal <- as.character(EAPC_cal[i,1])
  a <- subset(EAPC, EAPC$location==country_cal)
  a$y <- log(a$val)
  mod_simp_reg<-lm(y~year,data=a)
  estimate <- (exp(summary(mod_simp_reg)[["coefficients"]][2,1])-1)*100
  low <- (exp(summary(mod_simp_reg)[["coefficients"]][2,1]-1.96*summary(mod_simp_reg)[["coefficients"]][2,2])-1)*100
  high <- (exp(summary(mod_simp_reg)[["coefficients"]][2,1]+1.96*summary(mod_simp_reg)[["coefficients"]][2,2])-1)*100
  EAPC_cal[i,2] <- estimate
  EAPC_cal[i,3] <- low
  EAPC_cal[i,4] <- high
}

EAPC_cal <- EAPC_cal %>% mutate(EAPC=round(EAPC,2),
                                LCI=round(LCI,2),
                                UCI=round(UCI,2))
EAPC_cal <- EAPC_cal %>% mutate(EAPC_CI = paste(EAPC, LCI,sep = '\n(')) %>% 
  mutate(EAPC_CI = paste(EAPC_CI, UCI,sep = ' to ')) %>% 
  mutate(EAPC_CI = paste0(EAPC_CI, ')'))
head(EAPC_cal)

write.csv(EAPC_cal,"prevalence female digestive disease table1.csv")