Merging tables when row order different

[rasuranasinghe]

I have disease numbers data from diufferent years ordered in descending order
2017

Disaese	Number
Hepatitis	237
Fever Unknown	217
CKD	115

2018

Disaese	Number
Fever Unknown	445
Hepatitis	100
CKD	90

2019

Disaese	Number
CKD	774
Hepatitis	100
Fever Unknown	62

how do I merge these so I can get a table like this

Disaese	2017	2018	2019
Hepatitis	237	100	100
Fever Unknown	217	445	62
CKD	115	90	774

[vlad_aluas]

Hi,

Can you please provide an representative example so we can help you. You have a guide below:
Tidyverse

Thank you

[iamericfletcher]

Here is an approach using the dplyr, tidyr, and purrr packages.

Note:

• Please take some time to learn how to include a properly formatted reproducible example. For example, to produce a minimal data set, you can use head(), subset(), or the indices. Then use dput() to give us something that can be put in R immediately. This not only helps you as a question asker, but it makes things easier for us to help you!

library(dplyr)
library(tidyr)
library(purrr)

df_2017 <- data.frame(
  stringsAsFactors = FALSE,
           Disaese = c("Hepatitis", "Fever Unknown", "CKD"),
            Number = c(237L, 217L, 115L)
            )

df_2018 <- data.frame(
  stringsAsFactors = FALSE,
           Disaese = c("Fever Unknown", "Hepatitis", "CKD"),
            Number = c(445L, 100L, 90L)
           )

df_2019 <- data.frame(
  stringsAsFactors = FALSE,
           Disaese = c("CKD", "Hepatitis", "Fever Unknown"),
            Number = c(774L, 100L, 62L)
           )

df_list <- list(df_2017, df_2018, df_2019)

df_list2 <- Map(cbind, df_list, year = rep_len(2017:2019, length.out = length(df_list)))

df_list2 %>% reduce(full_join, by = 'Disaese') %>% 
  pivot_wider(names_from = starts_with('year'), values_from = starts_with('Number')) %>% 
  rename('2017' = Number.x_2017_2018_2019,
         '2018' = Number.y_2017_2018_2019,
         '2019' = Number_2017_2018_2019)

#> # A tibble: 3 x 4
#>   Disaese        2017  2018   2019
#>   <chr>          <int>  <int>  <int>
#> 1 Hepatitis        237    100    100
#> 2 Fever Unknown    217    445     62
#> 3 CKD              115     90    774

^{Created on 2021-03-19 by the reprex package (v0.3.0)}

[nirgrahamuk]

assuming you do have 3 dataframes as per iamericfletcher's reprex

library(tidyverse)

df_list <- list(df_2017, df_2018, df_2019) %>% 
  set_names(2017:2019)

bind_rows(df_list,.id="id") %>% 
pivot_wider(names_from="id",
            values_from="Number")

[iamericfletcher]

Holy cannoli. My approach is so verbose. Thank you for this!