How to get the value from another row depends on value in another column in R

grislepak 0
Viewed 21

I have the data set of the sales performance like this

|Customer_ID |Year |Month| Sales|
|------------|---- |-----|------|
|Mercedes    |2019 |  01 |   10 |
|Mercedes    |2019 |  02 |    8 |
|Mercedes    |2019 |  05 |    3 |
|Mercedes    |2020 |  01 |    7 |
|Mercedes    |2020 |  03 |   12 |
|Mercedes    |2020 |  05 |   11 |

.....

I need to add another column with Sales volume from the previous year
|Customer_ID |Year |Month| Sales|LastYear|
|------------|---- |-----|------|--------|
|Mercedes    |2019 |  01 |   10 |        |
|Mercedes    |2019 |  02 |    8 |        |
|Mercedes    |2019 |  05 |    3 |        |
|Mercedes    |2020 |  01 |    7 |   10   |
|Mercedes    |2020 |  03 |   12 |    8   |
|Mercedes    |2020 |  05 |   11 |    3   |

.....

Tried to use left_join function and remove the rows where year are equal

ds5 <- subset(ds4,SBYEAR.new == SBYEAR.old+1)

but it is not working Please advise

join dataframe r
1 Answers

dplyr

library(dplyr)
ds4 %>%
  transmute(Customer_ID, Year = Year + 1, Month, LastYear = Sales) %>%
  left_join(ds4, ., by = c("Customer_ID", "Year", "Month"))
#   Customer_ID Year Month Sales LastYear
# 1    Mercedes 2019     1    10       NA
# 2    Mercedes 2019     2     8       NA
# 3    Mercedes 2019     5     3       NA
# 4    Mercedes 2020     1     7       10
# 5    Mercedes 2020     3    12       NA
# 6    Mercedes 2020     5    11        3

My row 5 is NA here because the month changed. If you don't care about the month, then perhaps

ds4 <- group_by(ds4, Customer_ID, Year) %>%
  mutate(row = row_number()) %>%
  ungroup()
ds4 %>%
  transmute(Customer_ID, Year = Year + 1, row, LastYear = Sales) %>%
  left_join(ds4, ., by = c("Customer_ID", "Year", "row"))
# # A tibble: 6 x 6
#   Customer_ID  Year Month Sales   row LastYear
#   <chr>       <dbl> <int> <int> <int>    <int>
# 1 Mercedes     2019     1    10     1       NA
# 2 Mercedes     2019     2     8     2       NA
# 3 Mercedes     2019     5     3     3       NA
# 4 Mercedes     2020     1     7     1       10
# 5 Mercedes     2020     3    12     2        8
# 6 Mercedes     2020     5    11     3        3

base R

ds4$row <- ave(dat$Sales, dat[,c("Customer_ID", "Year")], FUN = seq_along)
ds4 |>
  transform(Year = Year + 1, LastYear = Sales) |>
  subset(select = c(Customer_ID, Year, row, LastYear)) |>
  merge(ds4, by = c("Customer_ID", "Year", "row"), all.y = TRUE)
#   Customer_ID Year row LastYear Month Sales
# 1    Mercedes 2019   1       NA     1    10
# 2    Mercedes 2019   2       NA     2     8
# 3    Mercedes 2019   3       NA     5     3
# 4    Mercedes 2020   1       10     1     7
# 5    Mercedes 2020   2        8     3    12
# 6    Mercedes 2020   3        3     5    11
r2evans0
Related