Calculation for last month -- with conditions

Question

In my dataset, I have observations on different people, for each month, in different places. I want to create a new column which is: The average of value for last month for all places the person visited other than the place of the current row. So for example, row 6 would be the average of rows 2-5 (last month in places that are not A).

In my actual dataset, the data is not balanced -- and users are observed for different months -- so any solution should not necessarily rely on that.

library(tidyr)

# Generate people, places, and dates
people <- letters[1:3]
places <- LETTERS[1:5]
dates <- seq(as.Date("2020/01/04"), by = "month", length.out = 3)

# Now cross data so there is observation for each person, for each place, for each month
crossed <- crossing(people, dates, places)

# Add random values
crossed$value <- rnorm(nrow(crossed), 2)

Here is what the data looks like

   people dates      places value
   <chr>  <date>     <chr>  <dbl>
 1 a      2020-01-04 A      2.94 
 2 a      2020-01-04 B      1.74 
 3 a      2020-01-04 C      2.68 
 4 a      2020-01-04 D      3.96 
 5 a      2020-01-04 E      0.821
 6 a      2020-02-04 A      1.86 
 7 a      2020-02-04 B      1.04 
 8 a      2020-02-04 C      1.51 
 9 a      2020-02-04 D      3.62 
10 a      2020-02-04 E      1.81

score 0 · Accepted Answer · answered Mar 30 '22 at 01:59

This works if the dates for each month are always exactly one month apart. If not, you’ll have to tinker with dates == .y - months(1), but the general approach will be the same.

library(tidyverse)
set.seed(1)

crossed %>%
  group_by(people) %>%
  mutate(
    new_val = map2_dbl(
    places, 
    dates,
    ~ mean(value[places != .x & dates == .y - months(1)]))
  ) %>%
  ungroup()

Output:

# A tibble: 45 x 5
   people dates      places value new_val
   <chr>  <date>     <chr>  <dbl>   <dbl>
 1 a      2020-01-04 A       1.37  NaN   
 2 a      2020-01-04 B       2.18  NaN   
 3 a      2020-01-04 C       1.16  NaN   
 4 a      2020-01-04 D       3.60  NaN   
 5 a      2020-01-04 E       2.33  NaN   
 6 a      2020-02-04 A       1.18    2.32
 7 a      2020-02-04 B       2.49    2.12
 8 a      2020-02-04 C       2.74    2.37
 9 a      2020-02-04 D       2.58    1.76
10 a      2020-02-04 E       1.69    2.08
# ... with 35 more rows

Calculation for last month -- with conditions

1 Answers1