Comptes de secteurs institutionnels

Data - Insee

Author

François Geerolf

Info

source dataset .html .RData
insee CNT-2014-CSI 2024-11-15 2024-11-17
insee CNT-2014-PIB-EQB-RF 2024-11-17 2024-11-17

Données sur la macroéconomie en France

source dataset .html .RData
bdf CFT 2024-11-17 2024-07-01
insee CNA-2014-CONSO-MEN 2024-11-09 2024-11-09
insee CNA-2014-CONSO-SI 2024-11-09 2024-11-09
insee CNA-2014-CSI 2024-11-09 2024-11-09
insee CNA-2014-FBCF-BRANCHE 2024-11-17 2024-11-17
insee CNA-2014-FBCF-SI 2024-06-07 2024-11-17
insee CNA-2014-PIB 2024-11-09 2024-11-09
insee CNA-2014-RDB 2024-11-16 2024-11-16
insee CNT-2014-CB 2024-11-16 2024-11-16
insee CNT-2014-CSI 2024-11-15 2024-11-17
insee CNT-2014-OPERATIONS 2024-11-15 2024-11-15
insee CNT-2014-PIB-EQB-RF 2024-11-17 2024-11-17
insee CONSO-MENAGES-2014 2024-11-17 2024-11-17
insee conso-mensuelle 2024-06-07 2023-07-04
insee ICA-2015-IND-CONS 2024-11-17 2024-11-16
insee t_1101 2024-11-09 2022-01-02
insee t_1102 2024-11-09 2020-10-30
insee t_1105 2024-11-09 2020-10-30

Données

  • Comptes nationaux trimestriels au 2ème trimestre 2021. html

  • Produit Intérieur Brut (PIB) et ses composantes. html

  • Biens et services. html

LAST_UPDATE

Code 
`CNT-2014-CSI` %>%
  group_by(LAST_UPDATE) %>%
  summarise(Nobs = n()) %>%
  arrange(desc(LAST_UPDATE)) %>%
  print_table_conditional()
LAST_UPDATE Nobs
2024-04-30 98700

LAST_COMPILE

LAST_COMPILE
2024-11-17

Last

Code 
`CNT-2014-CSI` %>%
  filter(TIME_PERIOD == max(TIME_PERIOD)) %>%
  select(TIME_PERIOD, TITLE_FR, OBS_VALUE) %>%
  arrange(TITLE_FR) %>%
  print_table_conditional()

Compte d’exploitation

Code 
i_g("bib/insee/compte-d-exploitation.png")

SECT-INST

Code 
`CNT-2014-CSI` %>%
  left_join(`SECT-INST`,  by = "SECT-INST") %>%
  group_by(`SECT-INST`, `Sect-Inst`) %>%
  summarise(Nobs = n()) %>%
  arrange(-Nobs) %>%
  print_table_conditional()
SECT-INST Sect-Inst Nobs
S13 S13 - Administrations publiques (APU) 21000
S12 S12 - Sociétés financières 12900
S2 S2 - Ensemble Reste du Monde 12600
S11 S11 - Sociétés non financières 12000
S14 S14 - Ménages y compris entreprises individuelles 9900
S15 S15 - Institutions sans but lucratif au service des ménages 9000
S14A S14A - Entrepreneurs individuels (ensemble) 4500
S14B S14B - Ménages hors entrepreneurs individuels 3900
S1 S1 - Ensemble économie nationale 2700
S14ANF S14ANF - Entreprises individuelles non financières 2700
S1A S1A - Ensemble des entreprises non financières (sociétés et EI) 2700
S2A S2A - Ensemble des entreprises financières (sociétés et EI) 2700
S14AF S14AF - Entreprises individuelles d'auxiliaires d'assurances 2100

INDICATEUR

Code 
`CNT-2014-CSI` %>%
  left_join(INDICATEUR,  by = "INDICATEUR") %>%
  group_by(INDICATEUR, Indicateur) %>%
  summarise(Nobs = n()) %>%
  arrange(-Nobs) %>%
  print_table_conditional()
INDICATEUR Indicateur Nobs
CNT-OPERATIONS_SECTEURS_INST Opérations des secteurs institutionnels 80700
CNT-SOLDES_COMPTABLES_SECTEURS_INST Soldes comptables des secteurs institutionnels 12600
CNT-EMPLOI_INTERIEUR_SECTEURS_INST Emploi intérieur par secteur institutionnel 5400

COMPTE

Code 
`CNT-2014-CSI` %>%
  left_join(COMPTE,  by = "COMPTE") %>%
  group_by(COMPTE, Compte) %>%
  summarise(Nobs = n()) %>%
  arrange(-Nobs) %>%
  print_table_conditional()
COMPTE Compte Nobs
E Emplois 49200
R Ressources 31500
SO Sans objet 18000

OPERATION

Code 
`CNT-2014-CSI` %>%
  left_join(OPERATION,  by = "OPERATION") %>%
  group_by(OPERATION, Operation) %>%
  summarise(Nobs = n()) %>%
  print_table_conditional()

NATURE

Code 
`CNT-2014-CSI` %>%
  group_by(NATURE) %>%
  summarise(Nobs = n()) %>%
  arrange(-Nobs) %>%
  print_table_conditional()
NATURE Nobs
VALEUR_ABSOLUE 98700

UNIT_MEASURE

Code 
`CNT-2014-CSI` %>%
  group_by(UNIT_MEASURE) %>%
  summarise(Nobs = n()) %>%
  print_table_conditional()
UNIT_MEASURE Nobs
ETP 2700
EUROS 93300
INDIVIDUS 2700

Ménages

Code 
`CNT-2014-CSI` %>%
  filter(`SECT-INST` == "S14",
         TIME_PERIOD == "2021-Q4") %>%
  left_join(OPERATION,  by = "OPERATION") %>%
  left_join(COMPTE,  by = "COMPTE") %>%
  select_if(~ n_distinct(.) > 1) %>%
  select(OPERATION, Operation, INDICATEUR, Compte, OBS_VALUE) %>%
  print_table_conditional()

Revenu disponible brut des ménages

1949-

Code 
`CNT-2014-CSI` %>%
  filter(`SECT-INST` == "S14",
         OPERATION %in% c("B6", "B7")) %>%
  left_join(OPERATION,  by = "OPERATION") %>%
  quarter_to_date() %>%
  ggplot + geom_line(aes(x = date, y = OBS_VALUE, color = Operation)) + 
  theme_minimal() +  xlab("") + ylab("") +
  scale_x_date(breaks = as.Date(paste0(seq(1940, 2020, 5), "-01-01")),
               labels = date_format("%Y")) +
  theme(legend.position = c(0.7, 0.2),
        legend.title = element_blank()) +
  scale_y_log10()

2017T2-

Code 
`CNT-2014-CSI` %>%
  filter(`SECT-INST` == "S14",
         OPERATION %in% c("B6", "B7")) %>%
  left_join(OPERATION,  by = "OPERATION") %>%
  quarter_to_date() %>%
  filter(date >= as.Date("2017-04-01")) %>%
  group_by(Operation) %>%
  arrange(date) %>%
  mutate(OBS_VALUE = 100*OBS_VALUE/OBS_VALUE[1]) %>%
  ggplot + geom_line(aes(x = date, y = OBS_VALUE, color = Operation)) + 
  theme_minimal() +  xlab("") + ylab("") +
  scale_x_date(breaks = as.Date(paste0(seq(1940, 2100, 1), "-01-01")),
               labels = date_format("%Y")) +
  theme(legend.position = c(0.7, 0.2),
        legend.title = element_blank()) +
  scale_y_log10()

Secteurs

B9NF - Capacité (+) ou besoin (-) de financement

Code 
`CNT-2014-CSI` %>%
  filter(OPERATION == "B9NF",
         FREQ == "T") %>%
  left_join(`SECT-INST`,  by = "SECT-INST") %>%
  quarter_to_date %>%
  left_join(gdp_quarterly, by = "date") %>%
  ggplot + geom_line(aes(x = date, y = OBS_VALUE/gdp, color = `Sect-Inst`)) +
  theme_minimal() + xlab("") + ylab("% du PIB") +
  scale_x_date(breaks = seq(1940, 2100, 5) %>% paste0("-01-01") %>% as.Date,
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-100, 500, 2),
                labels = percent_format(accuracy = 1)) +
  theme(legend.position = c(0.6, 0.8),
        legend.title = element_blank()) + 
  geom_hline(yintercept = 0, linetype = "dashed")

B2 - Excédent brut d’exploitation

% du PIB

Tous

Code 
`CNT-2014-CSI` %>%
  filter(OPERATION == "B2",
         FREQ == "T") %>%
  left_join(`SECT-INST`,  by = "SECT-INST") %>%
  quarter_to_date %>%
  left_join(gdp_quarterly, by = "date") %>%
  ggplot + geom_line(aes(x = date, y = OBS_VALUE/gdp, color = `Sect-Inst`)) +
  theme_minimal() + xlab("") + ylab("% du PIB") +
  scale_x_date(breaks = seq(1940, 2100, 5) %>% paste0("-01-01") %>% as.Date,
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-100, 500, 2),
                labels = percent_format(accuracy = 1)) +
  theme(legend.position = c(0.6, 0.7),
        legend.title = element_blank()) + 
  geom_hline(yintercept = 0, linetype = "dashed")

S1A, S1, S2A

Code 
`CNT-2014-CSI` %>%
  filter(OPERATION == "B2",
        `SECT-INST` %in% c("S1A", "S1", "S2A"),
         FREQ == "T") %>%
  left_join(`SECT-INST`,  by = "SECT-INST") %>%
  quarter_to_date %>%
  left_join(gdp_quarterly, by = "date") %>%
  ggplot + geom_line(aes(x = date, y = OBS_VALUE/gdp, color = `Sect-Inst`)) +
  theme_minimal() + xlab("") + ylab("% du PIB") +
  scale_x_date(breaks = seq(1940, 2100, 5) %>% paste0("-01-01") %>% as.Date,
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-100, 500, 2),
                labels = percent_format(accuracy = 1)) +
  theme(legend.position = c(0.6, 0.9),
        legend.title = element_blank()) + 
  geom_hline(yintercept = 0, linetype = "dashed")

% de la VA du secteur

All

Code 
`CNT-2014-CSI` %>%
  filter(OPERATION %in% c("B2", "B1")) %>%
  left_join(OPERATION, by = "OPERATION") %>%
  left_join(`SECT-INST`, by = "SECT-INST") %>%
  quarter_to_date() %>%
  select(date, OBS_VALUE, OPERATION, `SECT-INST`, `Sect-Inst`) %>%
  spread(OPERATION, OBS_VALUE) %>%
  ggplot + geom_line(aes(x = date, y = B2/B1, color = `Sect-Inst`)) + 
  theme_minimal() +  xlab("") + ylab("Excédent d'exploitation / VA") +
  scale_x_date(breaks = as.Date(paste0(seq(1940, 2100, 5), "-01-01")),
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(0, 100, 5),
                     labels = scales::percent_format(accuracy = 1)) + 
  theme(legend.position = c(0.6, 0.7),
        legend.title = element_blank())

S1A, S1, S2A

Code 
`CNT-2014-CSI` %>%
  filter(OPERATION %in% c("B2", "B1"),
        `SECT-INST` %in% c("S1A", "S1", "S2A")) %>%
  left_join(OPERATION, by = "OPERATION") %>%
  left_join(`SECT-INST`, by = "SECT-INST") %>%
  quarter_to_date() %>%
  select(date, OBS_VALUE, OPERATION, `SECT-INST`, `Sect-Inst`) %>%
  spread(OPERATION, OBS_VALUE) %>%
  ggplot + geom_line(aes(x = date, y = B2/B1, color = `Sect-Inst`)) + 
  theme_minimal() +  xlab("") + ylab("Excédent d'exploitation / VA") +
  scale_x_date(breaks = as.Date(paste0(seq(1940, 2100, 5), "-01-01")),
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(0, 100, 5),
                     labels = scales::percent_format(accuracy = 1)) + 
  theme(legend.position = c(0.35, 0.2),
        legend.title = element_blank())

S11, S12, S14

Code 
`CNT-2014-CSI` %>%
  filter(OPERATION == "B9NF",
         FREQ == "T",
         `SECT-INST` %in% c("S11", "S12", "S14")) %>%
  left_join(`SECT-INST`,  by = "SECT-INST") %>%
  quarter_to_date %>%
  left_join(gdp_quarterly, by = "date") %>%
  ggplot + geom_line(aes(x = date, y = OBS_VALUE/gdp, color = `Sect-Inst`)) +
  theme_minimal() + xlab("") + ylab("% du PIB") +
  scale_x_date(breaks = seq(1940, 2100, 5) %>% paste0("-01-01") %>% as.Date,
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-100, 500, 2),
                labels = percent_format(accuracy = 1)) +
  theme(legend.position = c(0.6, 0.9),
        legend.title = element_blank()) + 
  geom_hline(yintercept = 0, linetype = "dashed")

S12 - Sociétés financières

D1/B1G - Rémunération des salariés

Code 
`CNT-2014-CSI` %>%
  filter(`SECT-INST` == "S12",
         OPERATION %in% c("D1", "B1")) %>%
  quarter_to_date() %>%
  select(date, OBS_VALUE, OPERATION) %>%
  spread(OPERATION, OBS_VALUE) %>%
  ggplot + geom_line(aes(x = date, y = D1/B1)) + 
  theme_minimal() +  xlab("") + ylab("Part de la rémunération des salariés des SNF") +
  scale_x_date(breaks = as.Date(paste0(seq(1940, 2020, 5), "-01-01")),
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-0, 90, 5),
                     labels = scales::percent_format(accuracy = 1))

B2/B1G - Excédent d’exploitation

Code 
`CNT-2014-CSI` %>%
  filter(`SECT-INST` == "S12",
         OPERATION %in% c("B2", "B1")) %>%
  quarter_to_date() %>%
  select(date, OBS_VALUE, OPERATION) %>%
  spread(OPERATION, OBS_VALUE) %>%
  ggplot + geom_line(aes(x = date, y = B2/B1)) + 
  theme_minimal() +  xlab("") + ylab("") +
  scale_x_date(breaks = as.Date(paste0(seq(1940, 2020, 5), "-01-01")),
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(0, 90, 5),
                     labels = scales::percent_format(accuracy = 1))

(D292+D39)/B1

All

Code 
`CNT-2014-CSI` %>%
  filter(`SECT-INST` == "S12",
         OPERATION %in% c("D292", "B1", "D39")) %>%
  quarter_to_date() %>%
  select(date, OBS_VALUE, OPERATION) %>%
  spread(OPERATION, OBS_VALUE) %>%
  ggplot + geom_line(aes(x = date, y = (D292+D39)/B1)) + 
  theme_minimal() +  xlab("") + ylab("Impôts - Subventions sur la production") +
  scale_x_date(breaks = as.Date(paste0(seq(1940, 2020, 5), "-01-01")),
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-2, 90, 1),
                     labels = scales::percent_format(accuracy = 1)) + 
  geom_hline(yintercept = 0, linetype = "dashed", color = "red")

2000-

Code 
`CNT-2014-CSI` %>%
  filter(`SECT-INST` == "S12",
         OPERATION %in% c("D292", "B1", "D39")) %>%
  quarter_to_date() %>%
  filter(date >= as.Date("2000-01-01")) %>%
  select(date, OBS_VALUE, OPERATION) %>%
  spread(OPERATION, OBS_VALUE) %>%
  ggplot + geom_line(aes(x = date, y = (D292+D39)/B1)) + 
  theme_minimal() +  xlab("") + ylab("Impôts - Subventions sur la production") +
  scale_x_date(breaks = as.Date(paste0(seq(1940, 2020, 5), "-01-01")),
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-2, 90, 1),
                     labels = scales::percent_format(accuracy = 1)) + 
  geom_hline(yintercept = 0, linetype = "dashed", color = "red")

Investissement et taux de marge

Investissement total

Code 
`CNT-2014-PIB-EQB-RF` %>%
  filter(FREQ == "T",
         VALORISATION == "V",
         OPERATION %in% c( "P51", "PIB")) %>%
  quarter_to_date %>%
  select(date, OPERATION, OBS_VALUE) %>%
  spread(OPERATION, OBS_VALUE) %>%
  transmute(date,
         value = P51/PIB,
         Variable = "Investissement (% du PIB)") %>%
  bind_rows(`CNT-2014-CSI` %>%
              filter(`SECT-INST` == "S11",
                     OPERATION %in% c("B2", "B1")) %>%
              quarter_to_date() %>%
              select(date, OBS_VALUE, OPERATION) %>%
              spread(OPERATION, OBS_VALUE) %>%
              transmute(date, value = B2/B1,
                        Variable = "Taux de marge")) %>%
  ggplot(.) + theme_minimal() + ylab("") + xlab("") +
  geom_line(aes(x = date, y = value, color = Variable)) +
  theme(legend.title = element_blank(),
        legend.position = c(0.3, 0.9)) +
  scale_x_date(breaks = seq(1950, 2100, 10) %>% paste0("-01-01") %>% as.Date,
               labels = date_format("%Y")) +
  scale_color_manual(values = viridis(3)[1:2]) +
  scale_y_continuous(breaks = 0.01*seq(0, 100, 2),
                     labels = scales::percent_format(accuracy = 1)) + 
    geom_rect(data = data_frame(start = as.Date("1983-01-01"), 
                                end = as.Date("1992-12-31")), 
              aes(xmin = start, xmax = end, ymin = -Inf, ymax = +Inf), 
              fill = viridis(4)[4], alpha = 0.2)

Sociétés non Financières

Code 
`CNT-2014-PIB-EQB-RF` %>%
  filter(FREQ == "T",
         VALORISATION == "V",
         OPERATION %in% c( "P51S", "PIB")) %>%
  quarter_to_date %>%
  select(date, OPERATION, OBS_VALUE) %>%
  spread(OPERATION, OBS_VALUE) %>%
  transmute(date,
         value = P51S/PIB,
         Variable = "Investissement des Sociétés non Financières (% du PIB)") %>%
  bind_rows(`CNT-2014-CSI` %>%
              filter(`SECT-INST` == "S11",
                     OPERATION %in% c("B2", "B1")) %>%
              quarter_to_date() %>%
              select(date, OBS_VALUE, OPERATION) %>%
              spread(OPERATION, OBS_VALUE) %>%
              transmute(date, value = B2/B1,
                        Variable = "Taux de marge des Sociétés non Financières")) %>%
  ggplot(.) + theme_minimal() + ylab("") + xlab("") +
  geom_line(aes(x = date, y = value, color = Variable)) +
  theme(legend.title = element_blank(),
        legend.position = c(0.3, 0.35)) +
  scale_x_date(breaks = seq(1950, 2100, 10) %>% paste0("-01-01") %>% as.Date,
               labels = date_format("%Y")) +
  scale_color_manual(values = viridis(3)[1:2]) +
  scale_y_continuous(breaks = 0.01*seq(0, 100, 2),
                     labels = scales::percent_format(accuracy = 1)) + 
    geom_rect(data = data_frame(start = as.Date("1983-01-01"), 
                                end = as.Date("1992-12-31")), 
              aes(xmin = start, xmax = end, ymin = -Inf, ymax = +Inf), 
              fill = viridis(4)[4], alpha = 0.2)

Marges: comparer différents secteurs

B2/B1G - Excédent d’exploitation

S1A - Entreprises non financières

B2/B1G - Excédent d’exploitation

All

Code 
`CNT-2014-CSI` %>%
  filter(`SECT-INST` == "S1A",
         OPERATION %in% c("B2", "B1")) %>%
  left_join(OPERATION, by = "OPERATION") %>%
  quarter_to_date() %>%
  select(date, OBS_VALUE, OPERATION) %>%
  spread(OPERATION, OBS_VALUE) %>%
  ggplot + geom_line(aes(x = date, y = B2/B1)) + 
  theme_minimal() +  xlab("") + ylab("Excédent d'exploitation / VA, Sociétés non financières") +
  scale_x_date(breaks = as.Date(paste0(seq(1940, 2100, 5), "-01-01")),
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-2, 90, 1),
                     labels = scales::percent_format(accuracy = 1)) + 
    geom_rect(data = data_frame(start = as.Date("2017-07-01"), 
                                end = as.Date("2021-12-31")), 
              aes(xmin = start, xmax = end, ymin = -Inf, ymax = +Inf), 
              fill = viridis(4)[4], alpha = 0.2)

5 ans

Code 
`CNT-2014-CSI` %>%
  filter(`SECT-INST` == "S1A",
         OPERATION %in% c("B2", "B1")) %>%
  left_join(OPERATION, by = "OPERATION") %>%
  quarter_to_date() %>%
  select(date, OBS_VALUE, OPERATION) %>%
  spread(OPERATION, OBS_VALUE) %>%
  mutate(obsValue = B2/B1,
         obsValue_mean = zoo::rollmean(obsValue, 16, fill = NA, align = "right")) %>%
  ggplot + geom_line(aes(x = date, y = obsValue_mean)) + 
  theme_minimal() +  xlab("") + ylab("Excédent d'exploitation / VA, SNF, moyenne sur 4 ans") +
  scale_x_date(breaks = as.Date(paste0(seq(1940, 2100, 5), "-01-01")),
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-2, 90, 1),
                     labels = scales::percent_format(accuracy = 1)) + 
    geom_rect(data = data_frame(start = as.Date("2014-08-26"), 
                                end = as.Date("2021-12-31")), 
              aes(xmin = start, xmax = end, ymin = -Inf, ymax = +Inf), 
              fill = viridis(4)[4], alpha = 0.2) + 
  geom_vline(xintercept = as.Date("2014-08-26"), linetype = "dashed") + 
  geom_vline(xintercept = as.Date("2017-05-07"), linetype = "dashed")

S11 - Sociétés non financières

D1/B1G - Rémunération des salariés

Code 
`CNT-2014-CSI` %>%
  filter(`SECT-INST` == "S11",
         OPERATION %in% c("D1", "B1")) %>%
  quarter_to_date() %>%
  select(date, OBS_VALUE, OPERATION) %>%
  spread(OPERATION, OBS_VALUE) %>%
  ggplot + geom_line(aes(x = date, y = D1/B1)) + 
  theme_minimal() +  xlab("") + ylab("Part de la rémunération des salariés des SNF") +
  scale_x_date(breaks = as.Date(paste0(seq(1940, 2020, 5), "-01-01")),
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-2, 90, 1),
                     labels = scales::percent_format(accuracy = 1))

B2/B1G - Excédent d’exploitation

Taux de marge en 2021

Code 
ig_b("insee", "ENTFRA23", "F9", "table1")

All

1949-

Code 
`CNT-2014-CSI` %>%
  filter(`SECT-INST` == "S11",
         OPERATION %in% c("B2", "B1")) %>%
  left_join(OPERATION, by = "OPERATION") %>%
  quarter_to_date() %>%
  select(date, OBS_VALUE, OPERATION) %>%
  spread(OPERATION, OBS_VALUE) %>%
  mutate(value = B2/B1) %>%
  ggplot + geom_line(aes(x = date, y = value)) + 
  theme_minimal() +  xlab("") + ylab("Excédent d'exploitation / VA, Sociétés non financières") +
  scale_x_date(breaks = as.Date(paste0(seq(1940, 2100, 5), "-01-01")),
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-2, 90, 1),
                     labels = scales::percent_format(accuracy = 1)) + 
    geom_rect(data = data_frame(start = as.Date("2017-07-01"), 
                                end = as.Date("2021-12-31")), 
              aes(xmin = start, xmax = end, ymin = -Inf, ymax = +Inf), 
              fill = viridis(4)[4], alpha = 0.2)

1990-

Code 
`CNT-2014-CSI` %>%
  filter(`SECT-INST` == "S11",
         OPERATION %in% c("B2", "B1")) %>%
  left_join(OPERATION, by = "OPERATION") %>%
  quarter_to_date() %>%
  filter(date >= as.Date("1990-01-01")) %>%
  select(date, OBS_VALUE, OPERATION) %>%
  spread(OPERATION, OBS_VALUE) %>%
  mutate(value = B2/B1) %>%
  ggplot + geom_line(aes(x = date, y = value)) + 
  theme_minimal() +  xlab("") + ylab("Excédent d'exploitation / VA, Sociétés non financières") +
  scale_x_date(breaks = as.Date(paste0(seq(1940, 2100, 5), "-01-01")),
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-2, 90, 1),
                     labels = scales::percent_format(accuracy = 1))

1 an

Code 
`CNT-2014-CSI` %>%
  filter(`SECT-INST` == "S11",
         OPERATION %in% c("B2", "B1")) %>%
  left_join(OPERATION, by = "OPERATION") %>%
  quarter_to_date() %>%
  select(date, OBS_VALUE, OPERATION) %>%
  spread(OPERATION, OBS_VALUE) %>%
  mutate(obsValue = B2/B1,
         obsValue_mean = zoo::rollmean(obsValue, 4, fill = NA, align = "right")) %>%
  ggplot + geom_line(aes(x = date, y = obsValue_mean)) + 
  theme_minimal() +  xlab("") + ylab("Excédent d'exploitation / VA, SNF, moyenne sur 1 an") +
  scale_x_date(breaks = as.Date(paste0(seq(1940, 2100, 5), "-01-01")),
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-2, 90, 1),
                     labels = scales::percent_format(accuracy = 1)) + 
    geom_rect(data = data_frame(start = as.Date("2014-08-26"), 
                                end = as.Date("2021-12-31")), 
              aes(xmin = start, xmax = end, ymin = -Inf, ymax = +Inf), 
              fill = viridis(4)[4], alpha = 0.2) + 
  geom_vline(xintercept = as.Date("2014-08-26"), linetype = "dashed") + 
  geom_vline(xintercept = as.Date("2017-05-07"), linetype = "dashed")

2 ans

Code 
`CNT-2014-CSI` %>%
  filter(`SECT-INST` == "S11",
         OPERATION %in% c("B2", "B1")) %>%
  left_join(OPERATION, by = "OPERATION") %>%
  quarter_to_date() %>%
  select(date, OBS_VALUE, OPERATION) %>%
  spread(OPERATION, OBS_VALUE) %>%
  mutate(obsValue = B2/B1,
         obsValue_mean = zoo::rollmean(obsValue, 8, fill = NA, align = "right")) %>%
  ggplot + geom_line(aes(x = date, y = obsValue_mean)) + 
  theme_minimal() +  xlab("") + ylab("Excédent d'exploitation / VA, SNF, moyenne sur 2 ans") +
  scale_x_date(breaks = as.Date(paste0(seq(1940, 2100, 5), "-01-01")),
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-2, 90, 1),
                     labels = scales::percent_format(accuracy = 1)) + 
    geom_rect(data = data_frame(start = as.Date("2014-08-26"), 
                                end = as.Date("2021-12-31")), 
              aes(xmin = start, xmax = end, ymin = -Inf, ymax = +Inf), 
              fill = viridis(4)[4], alpha = 0.2) + 
  geom_vline(xintercept = as.Date("2014-08-26"), linetype = "dashed") + 
  geom_vline(xintercept = as.Date("2017-05-07"), linetype = "dashed")

3 ans

Code 
`CNT-2014-CSI` %>%
  filter(`SECT-INST` == "S11",
         OPERATION %in% c("B2", "B1")) %>%
  left_join(OPERATION, by = "OPERATION") %>%
  quarter_to_date() %>%
  select(date, OBS_VALUE, OPERATION) %>%
  spread(OPERATION, OBS_VALUE) %>%
  mutate(obsValue = B2/B1,
         obsValue_mean = zoo::rollmean(obsValue, 12, fill = NA, align = "right")) %>%
  ggplot + geom_line(aes(x = date, y = obsValue_mean)) + 
  theme_minimal() +  xlab("") + ylab("Excédent d'exploitation / VA, SNF, moyenne sur 3 ans") +
  scale_x_date(breaks = as.Date(paste0(seq(1940, 2100, 5), "-01-01")),
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-2, 90, 1),
                     labels = scales::percent_format(accuracy = 1)) + 
    geom_rect(data = data_frame(start = as.Date("2014-08-26"), 
                                end = as.Date("2021-12-31")), 
              aes(xmin = start, xmax = end, ymin = -Inf, ymax = +Inf), 
              fill = viridis(4)[4], alpha = 0.2) + 
  geom_vline(xintercept = as.Date("2014-08-26"), linetype = "dashed") + 
  geom_vline(xintercept = as.Date("2017-05-07"), linetype = "dashed")

4 ans

Code 
`CNT-2014-CSI` %>%
  filter(`SECT-INST` == "S11",
         OPERATION %in% c("B2", "B1")) %>%
  left_join(OPERATION, by = "OPERATION") %>%
  quarter_to_date() %>%
  select(date, OBS_VALUE, OPERATION) %>%
  spread(OPERATION, OBS_VALUE) %>%
  mutate(obsValue = B2/B1,
         obsValue_mean = zoo::rollmean(obsValue, 16, fill = NA, align = "right")) %>%
  ggplot + geom_line(aes(x = date, y = obsValue_mean)) + 
  theme_minimal() +  xlab("") + ylab("Excédent d'exploitation / VA, SNF, moyenne sur 4 ans") +
  scale_x_date(breaks = as.Date(paste0(seq(1940, 2100, 5), "-01-01")),
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-2, 90, 1),
                     labels = scales::percent_format(accuracy = 1)) + 
    geom_rect(data = data_frame(start = as.Date("2014-08-26"), 
                                end = as.Date("2021-12-31")), 
              aes(xmin = start, xmax = end, ymin = -Inf, ymax = +Inf), 
              fill = viridis(4)[4], alpha = 0.2) + 
  geom_vline(xintercept = as.Date("2014-08-26"), linetype = "dashed") + 
  geom_vline(xintercept = as.Date("2017-05-07"), linetype = "dashed")

5 ans

Code 
`CNT-2014-CSI` %>%
  filter(`SECT-INST` == "S11",
         OPERATION %in% c("B2", "B1")) %>%
  left_join(OPERATION, by = "OPERATION") %>%
  quarter_to_date() %>%
  select(date, OBS_VALUE, OPERATION) %>%
  spread(OPERATION, OBS_VALUE) %>%
  mutate(obsValue = B2/B1,
         obsValue_mean = zoo::rollmean(obsValue, 20, fill = NA, align = "right")) %>%
  ggplot + geom_line(aes(x = date, y = obsValue_mean)) + 
  theme_minimal() +  xlab("") + ylab("Excédent d'exploitation / VA, SNF, moyenne sur 5 ans") +
  scale_x_date(breaks = as.Date(paste0(seq(1940, 2100, 5), "-01-01")),
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-2, 90, 1),
                     labels = scales::percent_format(accuracy = 1)) + 
    geom_rect(data = data_frame(start = as.Date("2014-08-26"), 
                                end = as.Date("2021-12-31")), 
              aes(xmin = start, xmax = end, ymin = -Inf, ymax = +Inf), 
              fill = viridis(4)[4], alpha = 0.2) + 
  geom_vline(xintercept = as.Date("2014-08-26"), linetype = "dashed") + 
  geom_vline(xintercept = as.Date("2017-05-07"), linetype = "dashed")

P51/B1G

Code 
`CNT-2014-CSI` %>%
  filter(`SECT-INST` == "S11",
         OPERATION %in% c("P51", "B1")) %>%
  quarter_to_date() %>%
  select(date, OBS_VALUE, OPERATION) %>%
  spread(OPERATION, OBS_VALUE) %>%
  ggplot + geom_line(aes(x = date, y = P51/B1)) + 
  theme_minimal() +  xlab("") + ylab("") +
  scale_x_date(breaks = as.Date(paste0(seq(1940, 2020, 5), "-01-01")),
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-2, 90, 1),
                     labels = scales::percent_format(accuracy = 1))

(D292+D39)/B1

All

Code 
`CNT-2014-CSI` %>%
  filter(`SECT-INST` == "S11",
         OPERATION %in% c("D292", "B1", "D39")) %>%
  quarter_to_date() %>%
  select(date, OBS_VALUE, OPERATION) %>%
  spread(OPERATION, OBS_VALUE) %>%
  ggplot + geom_line(aes(x = date, y = (D292+D39)/B1)) + 
  theme_minimal() +  xlab("") + ylab("Impôts - Subventions sur la production") +
  scale_x_date(breaks = as.Date(paste0(seq(1940, 2020, 5), "-01-01")),
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-2, 90, 1),
                     labels = scales::percent_format(accuracy = 1)) + 
  geom_hline(yintercept = 0, linetype = "dashed", color = "red")

2000-

Code 
`CNT-2014-CSI` %>%
  filter(`SECT-INST` == "S11",
         OPERATION %in% c("D292", "B1", "D39")) %>%
  quarter_to_date() %>%
  filter(date >= as.Date("2000-01-01")) %>%
  select(date, OBS_VALUE, OPERATION) %>%
  spread(OPERATION, OBS_VALUE) %>%
  ggplot + geom_line(aes(x = date, y = (D292+D39)/B1)) + 
  theme_minimal() +  xlab("") + ylab("Impôts - Subventions sur la production") +
  scale_x_date(breaks = as.Date(paste0(seq(1940, 2020, 5), "-01-01")),
               labels = date_format("%Y")) +
  scale_y_continuous(breaks = 0.01*seq(-2, 90, 1),
                     labels = scales::percent_format(accuracy = 1)) + 
  geom_hline(yintercept = 0, linetype = "dashed", color = "red")