-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathdatatables.R
160 lines (151 loc) · 8.03 KB
/
datatables.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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
getcompagnies <- function(x=c("2014","2015","2016")){
somme_by_type <- avantage %>% filter(year(avantage$date) %in% x ) %>%
# unite(col = med, benef_nom, benef_prenom, sep = " ") %>%
group_by(denomination_sociale) %>%
summarise(n = n(),
TotalAmount = sum(montant, na.rm = TRUE),
MinAmount = min(montant, na.rm = TRUE),
MaxAmount = max(montant, na.rm = TRUE),
MedianTransactionAmount=median(montant, na.rm=T),
Q1=quantile(montant,probs = 0.25, na.rm=T),
Q3=quantile(montant,probs = 0.75, na.rm=T),
MeanTransactionAmount=round(TotalAmount/n,2)
) %>%
arrange(desc(TotalAmount))
return(somme_by_type)
}
compagnies_all <- getcompagnies()
write.csv2(compagnies_all,"top_compagnies.csv", row.names=F)
getsector <- function(x=c("2014","2015","2016")){
somme_by_type <- avantage %>% filter(year(avantage$date) %in% x ) %>%
# unite(col = med, benef_nom, benef_prenom, sep = " ") %>%
group_by(ent_secteur_lab) %>%
summarise(n = n(),
TotalAmount = sum(montant, na.rm = TRUE),
MinAmount = min(montant, na.rm = TRUE),
MaxAmount = max(montant, na.rm = TRUE),
MedianTransactionAmount=median(montant, na.rm=T),
Q1=quantile(montant,probs = 0.25, na.rm=T),
Q3=quantile(montant,probs = 0.75, na.rm=T),
MeanTransactionAmount=round(TotalAmount/n,2)
) %>%
arrange(desc(TotalAmount))
return(somme_by_type)
}
sector_all <- getsector()
sector_2014 <- getsector("2014")
sector_2015 <- getsector("2015")
sector_2016 <- getsector("2016")
write.csv2(sector_all,"sector_all.csv", row.names=F)
write.csv2(sector_2014,"sector_2014.csv", row.names=F)
write.csv2(sector_2015,"sector_2015.csv", row.names=F)
write.csv2(sector_2016,"sector_2016.csv", row.names=F)
#
getcategory <- function(x=c("2014","2015","2016")){
somme_by_type <- avantage %>% filter(year(avantage$date) %in% x ) %>%
# unite(col = med, benef_nom, benef_prenom, sep = " ") %>%
group_by(LIB_BENEF_CATEGORIE3) %>%
summarise(n = n(),
TotalAmount = sum(montant, na.rm = TRUE),
MinAmount = min(montant, na.rm = TRUE),
MaxAmount = max(montant, na.rm = TRUE),
MedianTransactionAmount=median(montant, na.rm=T),
Q1=quantile(montant,probs = 0.25, na.rm=T),
Q3=quantile(montant,probs = 0.75, na.rm=T),
MeanTransactionAmount=round(TotalAmount/n,2)
) %>%
arrange(desc(TotalAmount))
return(somme_by_type)
}
category_all <- getcategory()
category_2014 <- getcategory("2014")
category_2015 <- getcategory("2015")
category_2016 <- getcategory("2016")
write.csv2(category_all,"category_all.csv", row.names=F)
write.csv2(category_2014,"category_2014.csv", row.names=F)
write.csv2(category_2015,"category_2015.csv", row.names=F)
write.csv2(category_2016,"category_2016.csv", row.names=F)
#
avantage %>% filter(year(avantage$date) %in% c("2014","2015","2016"), benef_categorie_code=="[PRS]", is.na(LIB_BENEF_QUALITE2)) %>%
View()
getpro <- function(x=c("2014","2015","2016")){
somme_by_type <- avantage %>% mutate(LIB_BENEF_QUALITE2=ifelse(is.na(avantage$LIB_BENEF_QUALITE2),"Others",avantage$LIB_BENEF_QUALITE2)) %>%
filter(year(avantage$date) %in% x, benef_categorie_code=="[PRS]") %>%
# unite(col = med, benef_nom, benef_prenom, sep = " ") %>%
group_by(LIB_BENEF_QUALITE2) %>%
summarise(n = n(),
TotalAmount = sum(montant, na.rm = TRUE),
MinAmount = min(montant, na.rm = TRUE),
MaxAmount = max(montant, na.rm = TRUE),
MedianTransactionAmount=median(montant, na.rm=T),
Q1=quantile(montant,probs = 0.25, na.rm=T),
Q3=quantile(montant,probs = 0.75, na.rm=T),
MeanTransactionAmount=round(TotalAmount/n,2)
) %>%
arrange(desc(TotalAmount))
return(somme_by_type)
}
pro_all <- getpro()
pro_2014 <- getpro("2014")
pro_2015 <- getpro("2015")
pro_2016 <- getpro("2016")
write.csv2(pro_all,"pro_all.csv", row.names=F)
write.csv2(pro_2014,"pro_2014.csv", row.names=F)
write.csv2(pro_2015,"pro_2015.csv", row.names=F)
write.csv2(pro_2016,"pro_2016.csv", row.names=F)
# Flow chart ####
# All transactions ####
sum(category_all$TotalAmount)
sum(category_all$n)
# Not individuals benefits ####
sum(category_all$TotalAmount[2:8], na.rm = T)
sum(category_all$n[2:8], na.rm = T)
# All health professionnals ####
sum(pro_all$TotalAmount)
sum(pro_all$n)
# Other health professionnals ####
sum(pro_all[2:10,"n"])
sum(pro_all[2:10,"TotalAmount"])
# Med all ####
pro_all[1,c("TotalAmount","n")]
medecins %>% filter(year(medecins$date) %in% c("2014","2015","2016"), !benef_pays_code=="[FR]") %>% summarise(amount=sum(montant,na.rm=T),n=n())
medecins %>% filter(year(medecins$date) %in% c("2014","2015","2016"), benef_pays_code=="[FR]") %>% summarise(amount=sum(montant,na.rm=T),n=n())
# French MD ####
medecins %>% filter(year(medecins$date) %in% c("2014","2015","2016"), benef_pays_code=="[FR]",identification==0) %>% summarise(amount=sum(montant,na.rm=T),n=n())
medecins %>% filter(year(medecins$date) %in% c("2014","2015","2016"), benef_pays_code=="[FR]",identification==1) %>% summarise(amount=sum(montant,na.rm=T),n=n())
# Describe id impossible vs possible ####
follow_global_amount <- medecins %>% mutate(year_mnt=year(medecins$date))%>% filter(year(medecins$date) %in% c("2014","2015","2016"), benef_pays_code=="[FR]") %>%
group_by(year_mnt,identification) %>% summarise(amount=sum(montant,na.rm=T)) %>% ungroup() %>%
spread(.,identification,amount) %>% rename("No"=`0`,"Yes"=`1`) %>% mutate(freqMiss=round(100*No/(No+Yes),1))
follow_global_transact <- medecins %>% mutate(year_mnt=year(medecins$date))%>% filter(year(medecins$date) %in% c("2014","2015","2016"), benef_pays_code=="[FR]") %>%
group_by(year_mnt,identification) %>% summarise(N=n() ) %>% ungroup() %>%
spread(.,identification,N) %>% rename("No"=`0`,"Yes"=`1`) %>% mutate(freqMiss=round(100*No/(No+Yes),1))
write.csv2(follow_global_amount,"follow_global_amount.csv", row.names=F)
write.csv2(follow_global_transact,"follow_global_transact.csv", row.names=F)
follow_evo_amount <- medecins %>% mutate(year_mnt=year(medecins$date))%>% filter(year(medecins$date) %in% c("2014","2015","2016"), benef_pays_code=="[FR]") %>%
group_by(year_mnt,identification,ent_secteur_lab) %>% summarise(amount=sum(montant,na.rm=T)) %>% ungroup() %>%
spread(.,identification,amount) %>% rename("No"=`0`,"Yes"=`1`) %>% mutate(freqMiss=round(100*No/(No+Yes),1))
follow_evo_transact <- medecins %>% mutate(year_mnt=year(medecins$date))%>% filter(year(medecins$date) %in% c("2014","2015","2016"), benef_pays_code=="[FR]") %>%
group_by(year_mnt,identification,ent_secteur_lab) %>% summarise(N=n() ) %>% ungroup() %>%
spread(.,identification,N) %>% rename("No"=`0`,"Yes"=`1`) %>% mutate(freqMiss=round(100*No/(No+Yes),1))
write.csv2(follow_evo_amount,"follow_evo_amount.csv", row.names=F)
write.csv2(follow_evo_transact,"follow_evo_transact.csv", row.names=F)
getspe <- function(x=c("2014","2015","2016")){
somme_by_type <- medecins_ok %>% # mutate(LIB_BENEF_QUALITE2=ifelse(is.na(avantage$LIB_BENEF_QUALITE2),"Others",avantage$LIB_BENEF_QUALITE2)) %>%
filter(year(medecins_ok$date) %in% x) %>%
# unite(col = med, benef_nom, benef_prenom, sep = " ") %>%
group_by(LIB_BENEF_SPECIALITE2) %>%
summarise(n = n(),
TotalAmount = sum(montant, na.rm = TRUE),
MinAmount = min(montant, na.rm = TRUE),
MaxAmount = max(montant, na.rm = TRUE),
MedianTransactionAmount=median(montant, na.rm=T),
Q1=quantile(montant,probs = 0.25, na.rm=T),
Q3=quantile(montant,probs = 0.75, na.rm=T),
MeanTransactionAmount=round(TotalAmount/n,2)
) %>%
arrange(desc(TotalAmount))
return(somme_by_type)
}
spe_all <- getspe()
write.csv2(spe_all,"spe_all.csv", row.names=F)