# excel file
myData <- read_excel("../00_data/myData.xlsx")
myData## # A tibble: 27 × 35
## country country_code year `Cardiovascular diseases (%)` `Cancers (%)`
## <chr> <chr> <dbl> <dbl> <dbl>
## 1 World OWID_WRL 1990 26.5 12.2
## 2 World OWID_WRL 1991 26.6 12.4
## 3 World OWID_WRL 1992 27.0 12.5
## 4 World OWID_WRL 1993 27.3 12.7
## 5 World OWID_WRL 1994 27.3 12.7
## 6 World OWID_WRL 1995 27.6 12.9
## 7 World OWID_WRL 1996 27.6 13.0
## 8 World OWID_WRL 1997 27.7 13.1
## 9 World OWID_WRL 1998 27.8 13.3
## 10 World OWID_WRL 1999 28.0 13.4
## # ℹ 17 more rows
## # ℹ 30 more variables: `Respiratory diseases (%)` <dbl>, `Diabetes (%)` <dbl>,
## # `Dementia (%)` <dbl>, `Lower respiratory infections (%)` <dbl>,
## # `Neonatal deaths (%)` <dbl>, `Diarrheal diseases (%)` <dbl>,
## # `Road accidents (%)` <dbl>, `Liver disease (%)` <dbl>,
## # `Tuberculosis (%)` <dbl>, `Kidney disease (%)` <dbl>,
## # `Digestive diseases (%)` <dbl>, `HIV/AIDS (%)` <dbl>, …myData## # A tibble: 27 × 35
## country country_code year `Cardiovascular diseases (%)` `Cancers (%)`
## <chr> <chr> <dbl> <dbl> <dbl>
## 1 World OWID_WRL 1990 26.5 12.2
## 2 World OWID_WRL 1991 26.6 12.4
## 3 World OWID_WRL 1992 27.0 12.5
## 4 World OWID_WRL 1993 27.3 12.7
## 5 World OWID_WRL 1994 27.3 12.7
## 6 World OWID_WRL 1995 27.6 12.9
## 7 World OWID_WRL 1996 27.6 13.0
## 8 World OWID_WRL 1997 27.7 13.1
## 9 World OWID_WRL 1998 27.8 13.3
## 10 World OWID_WRL 1999 28.0 13.4
## # ℹ 17 more rows
## # ℹ 30 more variables: `Respiratory diseases (%)` <dbl>, `Diabetes (%)` <dbl>,
## # `Dementia (%)` <dbl>, `Lower respiratory infections (%)` <dbl>,
## # `Neonatal deaths (%)` <dbl>, `Diarrheal diseases (%)` <dbl>,
## # `Road accidents (%)` <dbl>, `Liver disease (%)` <dbl>,
## # `Tuberculosis (%)` <dbl>, `Kidney disease (%)` <dbl>,
## # `Digestive diseases (%)` <dbl>, `HIV/AIDS (%)` <dbl>, …myData_long <- myData %>%
pivot_longer(cols = c(`Cardiovascular diseases (%)`, `Cancers (%)`),
names_to = "`Cancers (%)`",
values_to = "`Diabetes (%)`")
myData_long## # A tibble: 54 × 35
## country country_code year `Respiratory diseases (%)` `Diabetes (%)`
## <chr> <chr> <dbl> <dbl> <dbl>
## 1 World OWID_WRL 1990 7.07 3.44
## 2 World OWID_WRL 1990 7.07 3.44
## 3 World OWID_WRL 1991 7.09 3.48
## 4 World OWID_WRL 1991 7.09 3.48
## 5 World OWID_WRL 1992 7.21 3.56
## 6 World OWID_WRL 1992 7.21 3.56
## 7 World OWID_WRL 1993 7.25 3.61
## 8 World OWID_WRL 1993 7.25 3.61
## 9 World OWID_WRL 1994 7.22 3.64
## 10 World OWID_WRL 1994 7.22 3.64
## # ℹ 44 more rows
## # ℹ 30 more variables: `Dementia (%)` <dbl>,
## # `Lower respiratory infections (%)` <dbl>, `Neonatal deaths (%)` <dbl>,
## # `Diarrheal diseases (%)` <dbl>, `Road accidents (%)` <dbl>,
## # `Liver disease (%)` <dbl>, `Tuberculosis (%)` <dbl>,
## # `Kidney disease (%)` <dbl>, `Digestive diseases (%)` <dbl>,
## # `HIV/AIDS (%)` <dbl>, `Suicide (%)` <dbl>, `Malaria (%)` <dbl>, …myData_long %>%
pivot_wider(names_from = year,
values_from = country)## # A tibble: 54 × 60
## country_code `Respiratory diseases (%)` `Diabetes (%)` `Dementia (%)`
## <chr> <dbl> <dbl> <dbl>
## 1 OWID_WRL 7.07 3.44 2.06
## 2 OWID_WRL 7.07 3.44 2.06
## 3 OWID_WRL 7.09 3.48 2.11
## 4 OWID_WRL 7.09 3.48 2.11
## 5 OWID_WRL 7.21 3.56 2.16
## 6 OWID_WRL 7.21 3.56 2.16
## 7 OWID_WRL 7.25 3.61 2.20
## 8 OWID_WRL 7.25 3.61 2.20
## 9 OWID_WRL 7.22 3.64 2.22
## 10 OWID_WRL 7.22 3.64 2.22
## # ℹ 44 more rows
## # ℹ 56 more variables: `Lower respiratory infections (%)` <dbl>,
## # `Neonatal deaths (%)` <dbl>, `Diarrheal diseases (%)` <dbl>,
## # `Road accidents (%)` <dbl>, `Liver disease (%)` <dbl>,
## # `Tuberculosis (%)` <dbl>, `Kidney disease (%)` <dbl>,
## # `Digestive diseases (%)` <dbl>, `HIV/AIDS (%)` <dbl>, `Suicide (%)` <dbl>,
## # `Malaria (%)` <dbl>, `Homicide (%)` <dbl>, …myData_sep <- myData %>%
separate(col = year, into = c("`Cancers (%)`"))
myData_sep## # A tibble: 27 × 35
## country country_code `\`Cancers (%)\`` Cardiovascular disease…¹ `Cancers (%)`
## <chr> <chr> <chr> <dbl> <dbl>
## 1 World OWID_WRL 1990 26.5 12.2
## 2 World OWID_WRL 1991 26.6 12.4
## 3 World OWID_WRL 1992 27.0 12.5
## 4 World OWID_WRL 1993 27.3 12.7
## 5 World OWID_WRL 1994 27.3 12.7
## 6 World OWID_WRL 1995 27.6 12.9
## 7 World OWID_WRL 1996 27.6 13.0
## 8 World OWID_WRL 1997 27.7 13.1
## 9 World OWID_WRL 1998 27.8 13.3
## 10 World OWID_WRL 1999 28.0 13.4
## # ℹ 17 more rows
## # ℹ abbreviated name: ¹`Cardiovascular diseases (%)`
## # ℹ 30 more variables: `Respiratory diseases (%)` <dbl>, `Diabetes (%)` <dbl>,
## # `Dementia (%)` <dbl>, `Lower respiratory infections (%)` <dbl>,
## # `Neonatal deaths (%)` <dbl>, `Diarrheal diseases (%)` <dbl>,
## # `Road accidents (%)` <dbl>, `Liver disease (%)` <dbl>,
## # `Tuberculosis (%)` <dbl>, `Kidney disease (%)` <dbl>, …myData_unite <- myData_sep %>%
unite(col = "`Dementia (%)`", c(`Diabetes (%)`), sep = "-")
myData_unite## # A tibble: 27 × 35
## country country_code `\`Cancers (%)\`` Cardiovascular disease…¹ `Cancers (%)`
## <chr> <chr> <chr> <dbl> <dbl>
## 1 World OWID_WRL 1990 26.5 12.2
## 2 World OWID_WRL 1991 26.6 12.4
## 3 World OWID_WRL 1992 27.0 12.5
## 4 World OWID_WRL 1993 27.3 12.7
## 5 World OWID_WRL 1994 27.3 12.7
## 6 World OWID_WRL 1995 27.6 12.9
## 7 World OWID_WRL 1996 27.6 13.0
## 8 World OWID_WRL 1997 27.7 13.1
## 9 World OWID_WRL 1998 27.8 13.3
## 10 World OWID_WRL 1999 28.0 13.4
## # ℹ 17 more rows
## # ℹ abbreviated name: ¹`Cardiovascular diseases (%)`
## # ℹ 30 more variables: `Respiratory diseases (%)` <dbl>,
## # `\`Dementia (%)\`` <chr>, `Dementia (%)` <dbl>,
## # `Lower respiratory infections (%)` <dbl>, `Neonatal deaths (%)` <dbl>,
## # `Diarrheal diseases (%)` <dbl>, `Road accidents (%)` <dbl>,
## # `Liver disease (%)` <dbl>, `Tuberculosis (%)` <dbl>, …myData %>%
complete(`Diabetes (%)`, `Cancers (%)`) %>%
select(`Diabetes (%)`, `Cancers (%)`, `Dementia (%)`)## # A tibble: 729 × 3
## `Diabetes (%)` `Cancers (%)` `Dementia (%)`
## <dbl> <dbl> <dbl>
## 1 3.44 12.2 2.06
## 2 3.44 12.4 NA
## 3 3.44 12.5 NA
## 4 3.44 12.7 NA
## 5 3.44 12.7 NA
## 6 3.44 12.9 NA
## 7 3.44 13.0 NA
## 8 3.44 13.1 NA
## 9 3.44 13.3 NA
## 10 3.44 13.4 NA
## # ℹ 719 more rows