CODEWITHSUNDEEP
rstata
Juan Pedro
Juan Pedro

Reputation: 13

R - Sum within group and only if another variable has consecutive values

I have a dataframe where each row is a firm on a specific month. I have two columns: amount of money and number of transactions. I need to identify those firms who have at least 150.0 in the amount of money column and at least 11 on the number of transactions column, by trimester. I have approximately 50 months of observations.

In Stata, what I did was to sort the data by id and month, then ask whether the sum of the trimester is higher that the conditions. This I did by using the [_n] functionality of Stata: having the data sorted and being in observation [_n], I know that observation [_n-1] is the same firm in the earlier month:

by id: replace auxactivado = 1 if auxactivado != 1 & !missing(amount) & ///
(amount[_n] + amount[_n-1]) > 150.00 & !missing(transac) & ///
(transac[_n] + transac[_n-1]) >= 10 & (mes[_n] == (mes[_n-1] + 1) | mes[_n] == 1 & mes[_n-1] == 12 & ao[_n] != ao[_n-1])

In the Stata code above I check whether the condition is met in just two months, for example (I also check for year changes; in the data below I created an auxiliar month which adjusts for this, so no need to make this adjustment anymore).

I would like to do this in R, but have no clue how. I have extensively looked online but could not come up with a solution. Any ideas would be much appreciated

month   year   monthaux           id    amount  transac
    2   2019         26      1201857     301.0     7
    3   2019         27      1201857     423.9     9
    4   2019         28      1201857     684.7    10
    5   2019         29      1201857     494.1     6
    4   2018         16      1202268     51       13
    5   2018         17      1202268     80       15
    2   2019         26      1202268     20       53
    6   2017          6      1202545     102.97    6
    7   2017          7      1202545     2429.6    1
    8   2017          8      1202545     1735.0    1

This is a piece of my data in case I was not clear. Note that the months are not always consecutive: I need to check the condition only on consecutive months.

I would like id 1201857 to show as 1 (meets conditions), 1202268 as 0 (meets transactions but not amount due to no-consecutive month) and 1202545 as 0 (meets amount, does not meet transac condition)

Edit: eastclintw00d has been helping me and there is some trouble with data of this sort, where the conditions are met within two months.

id  month   year    amount  transac
2068814 9   2016    151.18  5
2068814 10  2016    206.36  7

2037434 8   2018    85.43   1
2037434 10  2018    744.91  4
2037434 11  2018    630.8   6
2037434 1   2019    596.33  3



structure(list(id = c(2068814L, 2068814L, 2037434L, 2037434L, 
2037434L, 2037434L, 2037434L, 2037434L, 2037434L, 2037434L, 2037434L, 
2037434L, 2037434L, 2037434L, 2037434L, 2037434L, 2037434L, 2037434L, 
2037434L, 2037434L, 2037434L, 2037434L), ao = c(2016L, 2016L, 
2017L, 2017L, 2017L, 2017L, 2017L, 2017L, 2018L, 2018L, 2018L, 
2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 2018L, 2019L, 2019L, 
2019L, 2019L), mes = c(9L, 10L, 7L, 8L, 9L, 10L, 11L, 12L, 1L, 
2L, 3L, 4L, 5L, 6L, 7L, 8L, 10L, 11L, 1L, 3L, 4L, 5L), importe_dol = c(151.18, 
206.36, 268.85, 299.97, 63.99, 797.27, 525, 643.15, 108.58, 128.21, 
452.24, 403.25, 92, 1003.45, 158.96, 85.43, 744.91, 630.8, 596.33, 
574.02, 80.50351324, 444.9815415), cant_transac = c(5, 7, 2, 
1, 1, 2, 1, 2, 1, 1, 3, 1, 1, 3, 1, 1, 4, 6, 3, 4, 1, 3)), row.names = c(45L, 
811L, 10507L, 12459L, 15487L, 16601L, 19590L, 22927L, 27284L, 
30505L, 33036L, 36794L, 41810L, 43778L, 49722L, 54720L, 61910L, 
67047L, 77803L, 89001L, 97082L, 100933L), class = "data.frame")

Upvotes: 1

Views: 955

Answers (1)

eastclintw00d
eastclintw00d

Reputation: 2364

Assuming that your table is called df try:

library(dplyr)
df  %>% 
  group_by(id, year, trimester = ceiling(month / 4)) %>% 
  summarise_at(vars(transac, amount), sum) %>% 
  mutate(criterion = if_else(transac >= 11 & amount >= 150, 1, 0))

Given your clarification regarding trimesters the following code should do the trick. I first create a cartesian product of the three key variables and then join your dataframe on it. I create 1st and 2nd lag of the relevant variables and check whether they meet the criteria. Finally, I filter for those entries that you are looking for.

library(dplyr)
library(tidyr)
crossing(
  data.frame(ao = min(df$ao):max(df$ao)),
  data.frame(mes = 1:12),
  data.frame(id = unique(df$id))
) %>% 
  left_join(df %>% mutate(original = 1), by = c("ao", "mes", "id")) %>% 
  arrange(id, ao, mes) %>% 
  mutate(
    cant_transac2 = if_else(id == lag(id), lag(cant_transac), NA_real_), 
    cant_transac3 = if_else(id == lag(id, 2), lag(cant_transac, 2), NA_real_), 
    importe_dol2 = if_else(id == lag(id), lag(importe_dol), NA_real_), 
    importe_dol3 = if_else(id == lag(id, 2), lag(importe_dol, 2), NA_real_), 
  ) %>% 
  replace_na(list(cant_transac2 = 0, cant_transac3 = 0, importe_dol2 = 0, importe_dol3 = 0)) %>% 
  mutate(criterion = if_else(cant_transac + cant_transac2 + cant_transac3 >= 11 & importe_dol + importe_dol2 + importe_dol3 >= 150, 1, NA_real_)) %>% 
  filter(original == 1) %>% 
  select(-original, -cant_transac2, -cant_transac3, -importe_dol2, -importe_dol3)

Upvotes: 2

Related Questions