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 |
CodePudding user response:
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)
CodePudding user response:
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
CodePudding user response:
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)
CodePudding user response:
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