R: create or delete rows given a range of values

Question

I have the next database with country, year, and GDP:

What I have

Country	Year	GDP
Afghanistan	1950	$123
Afghanistan	1951	$123
Afghanistan	2019	$123
Australia	1945	$123
Australia	2021	$123

And what I need is to create or delete rows so each country has rows from 1948 to 2021. So, for example, for Afghanistan I need to create rows 1948 to 1949 and 2021 with a null GDP, and for Australia delete the 1945 row and create everything in between.

This isn't my exact database, I have 200+ countries each with different years. Is there a way to create this easily?

What I need

Country	Year	GDP
Afghanistan	1948	NA
...	...	...
Afghanistan	2021	NA
Australia	1948	$123
...	...	...
Australia	2021	$123

akrun · Answer 1 · 2021-09-26T18:38:58.837

2

We can use complete to create the missing combinations and specify the GDP as 0

library(tidyr)
complete(df1, Country, Year = 1948:2021, list(GDP = 0)) %>%
    arrange(Country)

edited Sep 26 '21 at 18:38

answered Sep 26 '21 at 18:20

akrun

874,273
37
540
662

score 1 · Answer 2 · answered Sep 26 '21 at 18:22

library(tidyr)
library(dplyr)

df <-
  tibble::tribble(
         ~Country, ~Year,   ~GDP,
    "Afghanistan", 1950L, "$123",
    "Afghanistan", 1951L, "$123",
    "Afghanistan", 2019L, "$123",
      "Australia", 1945L, "$123",
      "Australia", 2021L, "$123"
    )

df %>% 
  filter(Year >= 1948 & Year <= 2021) %>% 
  complete(Year = 1948:2021,Country) %>% 
  arrange(Country)

# A tibble: 148 x 3
    Year Country     GDP  
   <int> <chr>       <chr>
 1  1948 Afghanistan NA   
 2  1949 Afghanistan NA   
 3  1950 Afghanistan $123 
 4  1951 Afghanistan $123 
 5  1952 Afghanistan NA   
 6  1953 Afghanistan NA   
 7  1954 Afghanistan NA   
 8  1955 Afghanistan NA   
 9  1956 Afghanistan NA   
10  1957 Afghanistan NA   
# ... with 138 more rows

norie · Accepted Answer · 2021-09-26T18:39:15.460

We can use complete, then filter and finally replace_na.

library(dplyr)


df <-read.table(header=TRUE, text="Country  Year    GDP
Afghanistan 1950    $123
Afghanistan 1951    $123
Afghanistan 2019    $123
Australia   1945    $123
Australia   2021    $123")


df <- df %>% 
  complete(Year = 1948:2021, Country) %>%
  filter(between(Year, 1948, 2021)) %>%
  replace_na(list(GDP = 0)) %>%
  arrange(Country)

head(df)
tail(df)
 
> print(head(df))
# A tibble: 6 x 3
   Year Country     GDP  
  <int> <chr>       <chr>
1  1948 Afghanistan 0    
2  1949 Afghanistan 0    
3  1950 Afghanistan $123 
4  1951 Afghanistan $123 
5  1952 Afghanistan 0    
6  1953 Afghanistan 0    
> print(tail(df))
# A tibble: 6 x 3
   Year Country   GDP  
  <int> <chr>     <chr>
1  2016 Australia 0    
2  2017 Australia 0    
3  2018 Australia 0    
4  2019 Australia 0    
5  2020 Australia 0    
6  2021 Australia $123

^{Created on 2021-09-26 by the reprex package (v2.0.1)}

score 1 · Answer 4 · answered Sep 26 '21 at 19:06

Here is a solution with complete and coalesce

library(dplyr)
library(tidyr)
df %>% 
  complete(Year = 1948:2021, Country) %>% 
  arrange(Country, Year) %>% 
  mutate(GDP = coalesce(GDP, "0"))

# A tibble: 149 x 3
    Year Country     GDP  
   <int> <chr>       <chr>
 1  1948 Afghanistan 0    
 2  1949 Afghanistan 0    
 3  1950 Afghanistan $123 
 4  1951 Afghanistan $123 
 5  1952 Afghanistan 0    
 6  1953 Afghanistan 0    
 7  1954 Afghanistan 0    
 8  1955 Afghanistan 0    
 9  1956 Afghanistan 0    
10  1957 Afghanistan 0    
# … with 139 more rows

R: create or delete rows given a range of values

4 Answers4