Revision | e77d3859a5ff38494417b61d5544f5bf69462d02 (tree) |
---|---|
Time | 2024-04-24 18:39:37 |
Author | Lorenzo Isella <lorenzo.isella@gmai...> |
Commiter | Lorenzo Isella |
A simple script to get SMEs and broadband statistics from the scoreboard.
@@ -0,0 +1,107 @@ | ||
1 | +rm(list=ls()) | |
2 | + | |
3 | +library(tidyverse) | |
4 | +library(janitor) | |
5 | +library(openxlsx) | |
6 | + | |
7 | +source("/home/lorenzo/myprojects-hg/R-codes/stat_lib.R") | |
8 | + | |
9 | + | |
10 | +df <- readRDS("../scoreboard.RDS") | |
11 | + | |
12 | +df_obj_gber <- df |> | |
13 | + filter(expenditure_year==max(expenditure_year)) |> | |
14 | + find_text_filter_col(case_type, "(?i)exemption") |> | |
15 | + group_by(scoreboard_objective, expenditure_year ) |> | |
16 | + summarise(expenditure_mio_eur=sum(aid_element_eur, na.rm=T)) |> | |
17 | + ungroup() |> | |
18 | + arrange(expenditure_year, desc(expenditure_mio_eur )) | |
19 | + | |
20 | +save_excel(df_obj_gber, "scoreboard_gber_smes.xlsx", "GBER expenditure") | |
21 | + | |
22 | + | |
23 | + | |
24 | +df_obj_non_gber <- df |> | |
25 | + filter(expenditure_year==max(expenditure_year)) |> | |
26 | + find_text_filter_col(case_type, "(?i)notified") |> | |
27 | + group_by(scoreboard_objective, expenditure_year ) |> | |
28 | + summarise(expenditure_mio_eur=sum(aid_element_eur, na.rm=T)) |> | |
29 | + ungroup() |> | |
30 | + arrange(expenditure_year, desc(expenditure_mio_eur )) | |
31 | + | |
32 | +save_excel(df_obj_non_gber, "scoreboard_gber_smes.xlsx", "non-GBER expenditure") | |
33 | + | |
34 | + | |
35 | +df_obj <- df |> | |
36 | + filter(## is.na(gber_section), | |
37 | + expenditure_year==max(expenditure_year)) |> | |
38 | + group_by(scoreboard_objective, expenditure_year ) |> | |
39 | + summarise(expenditure_mio_eur=sum(aid_element_eur, na.rm=T)) |> | |
40 | + ungroup() |> | |
41 | + arrange(expenditure_year, desc(expenditure_mio_eur )) | |
42 | + | |
43 | + | |
44 | +save_excel(df_obj, "scoreboard_gber_smes.xlsx", "Total expenditure") | |
45 | + | |
46 | +#################################################################### | |
47 | +#################################################################### | |
48 | +#################################################################### | |
49 | + | |
50 | + | |
51 | +df_band <- df |> | |
52 | + filter(expenditure_year>=2011) |> | |
53 | + find_text_filter_col(all_intq , "(?i)broadband") | |
54 | + | |
55 | +df_band_year <- df_band |> | |
56 | + group_by(expenditure_year) |> | |
57 | + summarise(expenditure_mio_eur=sum(aid_element_eur, na.rm=T)) |> | |
58 | + ungroup() |> | |
59 | + arrange(expenditure_year) | |
60 | + | |
61 | + | |
62 | +df_band_year_ms <- df_band |> | |
63 | + group_by(expenditure_year, member_state) |> | |
64 | + summarise(expenditure_mio_eur=sum(aid_element_eur, na.rm=T)) |> | |
65 | + ungroup() |> | |
66 | + arrange(expenditure_year, member_state) | |
67 | + | |
68 | + | |
69 | + | |
70 | +df_band_year_case_type <- df_band |> | |
71 | + group_by(expenditure_year, case_type) |> | |
72 | + summarise(expenditure_mio_eur=sum(aid_element_eur, na.rm=T)) |> | |
73 | + ungroup() |> | |
74 | + arrange(expenditure_year, case_type) | |
75 | + | |
76 | + | |
77 | +df_band_year_obj <- df_band |> | |
78 | + group_by(expenditure_year, scoreboard_objective) |> | |
79 | + summarise(expenditure_mio_eur=sum(aid_element_eur, na.rm=T)) |> | |
80 | + ungroup() |> | |
81 | + arrange(expenditure_year, scoreboard_objective) | |
82 | + | |
83 | + | |
84 | + | |
85 | +df_band_year_obj_gber <- df_band |> | |
86 | + group_by(expenditure_year, primary_objective, all_gber_obj) |> | |
87 | + summarise(expenditure_mio_eur=sum(aid_element_eur, na.rm=T)) |> | |
88 | + ungroup() |> | |
89 | + arrange(expenditure_year,primary_objective, all_gber_obj) | |
90 | + | |
91 | + | |
92 | +save_excel(df_band_year, "broadband.xlsx" ,"expenditure_year") | |
93 | + | |
94 | +save_excel(df_band_year_ms, "broadband.xlsx", "expenditure_year_MS") | |
95 | + | |
96 | +save_excel(df_band_year_case_type, "broadband.xlsx", "expenditure_year_case") | |
97 | + | |
98 | +save_excel(df_band_year_obj, "broadband.xlsx", "expenditure_year_objective") | |
99 | + | |
100 | +save_excel(df_band_year_obj_gber, "broadband.xlsx", "expenditure_year_primary_gber") | |
101 | + | |
102 | + | |
103 | + | |
104 | + | |
105 | + | |
106 | + | |
107 | +print("So far so good") |