How to order one dataframe by order of another?

Asked

Viewed 32 times

2

I have 3 dataframes with 2 columns and 5 rows each, as follows:

Dataframe 1:

 estado    casosNovos
  <chr>       <dbl>
1 SP           3189
2 CE           1921
3 AM           1365
4 PA           1135
5 MA            938

I would like to order the DF2 and DF3 in the same order as the column "status" of DF1. I tried to use the functions sort and order, but states end up in different positions. For example, when using the function order:

DF2 <- DF2[order(DF1$estado),]

Upshot:

 estado    casosNovos
  <chr>       <dbl>
1 MA            689
2 CE            744
3 SP           3378
4 PA           1285
5 AM           1648

Code I am using:

library(tidyverse)

dados <- read_csv("https://brasil.io/dataset/covid19/caso_full/?place_type=state&is_repeated=False&format=csv") %>%
  select(-c(epidemiological_week, order_for_place, city, city_ibge_code, place_type,
            last_available_confirmed_per_100k_inhabitants, last_available_death_rate,
            estimated_population_2019, is_last, is_repeated)) %>%
  arrange(state, date)

names(dados) <- c("data", "estado", "casosAcumulados", "casosNovos", 
                  "obitosAcumulados", "obitosNovos")

DF1 <- dados %>%
  filter(data == max(data)) %>%
  select(estado, casosNovos) %>%
  arrange(desc(casosNovos)) %>%
  head(5L)

DF2 <- dados %>%
  filter(estado %in% DF1$estado, data == max(data) - 1) %>%
  select(estado, casosNovos)

DF3 <- dados %>%
  filter(estado %in% DF1$estado, data == max(data) - 7) %>%
  select(estado, casosNovos)

DF2 <- DF2[order(DF1$estado),]
DF3 <- DF3[order(DF1$estado),]

1 answer

2


The function order is the reverse itself. So, if you order the order of DF2 by the reverse of the order of DF1, we have DF2 by the required order. Complicated? the code is even very simple.

i1 <- order(DF1$estado)
i2 <- order(DF2$estado)
DF2[i2[order(i1)],]
#  estado casosNovos
#3     SP       3378
#2     CE        744
#5     AM       1648
#4     PA       1285
#1     MA        689

If there are several bases to sort, it is best to write a function.

ordemEspecial <- function(X, Y, coluna){
  i1 <- order(X[[coluna]])
  i2 <- order(Y[[coluna]])
  Y[i2[order(i1)], ]
}

ordemEspecial(DF1, DF2, 'estado')

Dice

DF1 <- read.table(text = "
estado    casosNovos
1 SP           3189
2 CE           1921
3 AM           1365
4 PA           1135
5 MA            938
", header = TRUE)


DF2 <- read.table(text = "
 estado    casosNovos
1 MA            689
2 CE            744
3 SP           3378
4 PA           1285
5 AM           1648
", header = TRUE)

Browser other questions tagged

You are not signed in. Login or sign up in order to post.