How to aggregate data from years to decades and plot them?

Question

This is the graph that I would like to reproduce:

but for that I have to change the years column because on the graph the x axis is in decades. By what means could I accomplish this ?

This is what I did to extract the data from the site (https://ourworldindata.org/famines) :

library(rvest)
library(dplyr)
library(tidyr)
library(ggplot2)
col_link <- "https://ourworldindata.org/famines#famines-by-world-region-since-1860"
col_page <- read_html(col_link)
col_table <- col_page %>% html_nodes("table#tablepress-73") %>% 
  html_table() %>% . [[1]]
data1 <- col_table %>%
  select(Year, `Excess Mortality midpoint`)

 Year      `Excess Mortality midpoint`
   <chr>     <chr>                      
 1 1846–52   1,000,000                  
 2 1860-1    2,000,000                  
 3 1863-67   30,000                     
 4 1866-7    961,043                    
 5 1868      100,000                    
 6 1868-70   1,500,000                  
 7 1870–1871 1,000,000                  
 8 1876–79   750,000                    
 9 1876–79   7,176,346                  
10 1877–79   11,000,000                 
# ... with 67 more rows

Answer 1

First, strsplit, make a proper year matrix, combine back with famines divided by number of years and reshape to long format (lines 1:6). Next, aggregate sums by decade and barplot it.

r <- strsplit(data1$Year, '-|–|, ') |>
  rapply(\(y) unlist(lapply(y, \(x) f(max(as.numeric(y)), x))), how='r') |>
  {\(.) t(sapply(., \(x) `length<-`(x, max(lengths(.)))))}() |>
  {\(.) cbind(`colnames<-`(., paste0('year.', seq_len(dim(.)[2]))),
         n=dim(.)[2] - rowSums(is.na(.)))}() |>
  {\(.) data.frame(., f=as.numeric(gsub('\\D', '', 
                                        data1$`Excess Mortality midpoint`))/
               .[, 'n'])}()|>
  reshape(1:3, direction='long') |>
  stats:::aggregate.formula(formula=f ~ as.integer(substr(year, 1, 3)), 
                            FUN=sum) |>
  t() 


## plot
op <- par(mar=c(5, 5, 4, 2)+.1)  ## set/store old pars

b <- barplot(r, axes=FALSE, ylim=c(0, max(r[2, ])*1.05),
        main='Famine victims', )
abline(h=asq, col='lightgrey', lty=3)
barplot(r, names.arg=paste0(r[1, ], '0s'), col='#20254c',
        cex.names=.8, axes=FALSE, add=TRUE)
asq <- seq(0, max(axTicks(2)), 2e6)
axis(2, asq, labels=FALSE)
mtext(paste(asq/1e6, 'Million'), 2, 1, at=asq, las=2)
text(b, r[2, ] + 5e5, labels=formatC(r[2, ], format='d', big.mark=','), cex=.7)
box()

par(op)  ## restore old pars

In line 2, I used this helper function f() to fill up the pseudo-years:

f <- \(x1, x2, n1=nchar(x1)) {
  u <- lapply(list(x1, x2), as.character)
  s <- c(n1 - nchar(u[[2]]) + 1L, n1)
  as.integer(`substr<-`(u[[1]], s[1], s[2], u[[2]]))
}

You can refine the aggregation method yourself to make the result exactly look like the original, but maybe this is better :)

Answer 2

Firstly, to convert the periods to decades, you need to extract a year for each period, based on which the calculation will be made. From your comment above, it looks like you need to extract the end year for each period. Given the data, regular expressions are used below to do this (and packages dplyr and stringr).

col_table <- col_table %>% 
  mutate(Year = case_when(
    grepl("^\\d{4}$",Year) ~ Year,
    grepl("\\d{4}[–-]\\d{4}",Year) ~ str_sub(Year, start= -4),
    grepl("\\d{4}[–-]\\d{2}$",Year) ~ paste0(str_sub(Year,1,2),str_sub(Year,-2)),
    grepl("\\d{4}[–-]\\d{1}$",Year) ~ paste0(str_sub(Year,1,3),str_sub(Year,-1))))

What this part of code is doing, is to detect the different cases and extract the proper year. Below there are examples for all cases, that are present on the dataset and what this part of code will result to.

• 1868 -> 1868
• 1878-1880 -> 1880
• 1846–52 -> 1852
• 1860-1 -> 1861

Now we have the year, so the next step is to extract the decade. To do so, we need to make sure that Year column is numeric and apply the necessary calculation (check here for it: https://stackoverflow.com/a/48966643/8864619)

  col_table <- col_table %>% 
      mutate(Decade = as.numeric(Year) - as.numeric(Year) %% 10)

To reproduce the plot we need to group by decade and make sure that the Excess Mortality midpoint column is numeric to be able to get the sum of victims per decade.

col_table <- col_table %>% 
  mutate(`Excess Mortality midpoint` = as.numeric(gsub(",", "", `Excess Mortality midpoint`))) %>% 
  group_by(Decade) %>% 
  summarize(val = sum(`Excess Mortality midpoint`)) %>% 
  ungroup()

For the plot itself, ggplot2 is used:

ylab <- c(5, 10, 15, 20, 25)
options(scipen=999)
p <- ggplot(data = col_table, aes(x=factor(Decade),y=val)) + 
   geom_bar(stat = "identity", fill = "navy") + 
   scale_x_discrete(labels = col_table %>% distinct(Decade) %>% mutate(Decade = paste0(Decade,"s")) %>% pull()) +
   geom_text(aes(label=format(val,big.mark=",")), size=2,vjust=-0.3) + 
   scale_y_continuous(labels = paste(ylab, "millions"),breaks = 10^6 * ylab) +
   ggtitle('Famine victims worldwide')+
   theme(panel.background = element_blank(),
        panel.border = element_blank(),
        panel.grid.major.x = element_blank(),
        panel.grid.major.y = element_line(size = 0.05, linetype = 'solid',
                                        colour = "black"),
        axis.title.x = element_blank(),
        axis.title.y = element_blank())
p

So, putting everything together, the following code should get you a column for the year and a column for the relevant decade, which should be then used to create the plot you want to:

library(rvest)
library(dplyr)
library(stringr)
library(ggplot2)

col_link <- "https://ourworldindata.org/famines#famines-by-world-region-since-1860"
col_page <- read_html(col_link)
col_table <- col_page %>% html_nodes("table#tablepress-73") %>% html_table() %>% . [[1]]

col_table <- col_table %>% 
  mutate(Year = case_when(
    grepl("^\\d{4}$",Year) ~Year,
    grepl("\\d{4}[–-]\\d{4}",Year) ~ str_sub(Year, start= -4),
    grepl("\\d{4}[–-]\\d{2}$",Year) ~ paste0(str_sub(Year,1,2),str_sub(Year,-2)),
    grepl("\\d{4}[–-]\\d{1}$",Year) ~ paste0(str_sub(Year,1,3),str_sub(Year,-1)))) %>%
  mutate(Decade = as.numeric(Year) - as.numeric(Year)%%10) %>% 
  mutate(`Excess Mortality midpoint` = as.numeric(gsub(",", "", `Excess Mortality midpoint`))) %>% 
  group_by(Decade) %>% 
  summarize(val = sum(`Excess Mortality midpoint`)) %>% 
  ungroup()

ylab <- c(5, 10, 15, 20, 25)
options(scipen=999)
p <- ggplot(data = col_table, aes(x=factor(Decade),y=val)) + 
   geom_bar(stat = "identity", fill = "navy") + 
   scale_x_discrete(labels = col_table %>% distinct(Decade) %>% mutate(Decade = paste0(Decade,"s")) %>% pull()) +
   geom_text(aes(label=format(val,big.mark=",")), size=2,vjust=-0.3) + 
   scale_y_continuous(labels = paste(ylab, "millions"),breaks = 10^6 * ylab) +
   ggtitle('Famine victims worldwide')+
   theme(panel.background = element_blank(),
        panel.border = element_blank(),
        panel.grid.major.x = element_blank(),
        panel.grid.major.y = element_line(size = 0.05, linetype = 'solid',
                                        colour = "black"),
        axis.title.x = element_blank(),
        axis.title.y = element_blank())
p

Here's the reproduced plot: