Account based on several conditions from other data.frame

Question

I am transferring analysis from Excel to R and would like information on how best to execute something similar to Excel COUNTIFS in R.

I have two data.frames, saidf and memberdf.

statedf=data.frame(state=c('MD','MD','MD','NY','NY','NY'), week = 5:7) 
memberdf=data.frame(memID = 1:15, state = c('MD','MD','NY','NY','MD'),
              finalweek = c(3,3,5,3,3,5,3,5,3,5,6,5,2,3,5),
              orders = c(1,2,3))

This data is for subscription-based businesses. I would like to know the number of members who recently failed for each week / state combo in a statement where the new failure is determined by the declared f $ week-1 = memberdf $ finalweek. Next, I would like to have a separate account for each order value (1,2,3).

The desired result will look like

out <- data.frame(state=c('MD','MD','MD','NY','NY','NY'), week = 5:7,
               oneorder = c(0,1,0,0,0,0),
               twoorder = c(0,0,1,0,1,0),
               threeorder = c(0,3,0,0,1,0))

( ) - data.frame member.df, , assertf, saidf / , memberdf, . Excel, COUNTIFS, R.

.

Answer 1

dplyr tidyr:

library(tidyr) ; library(dplyr)

counts <- memberdf %>%
  mutate(lapsedweek = finalweek + 1) %>%
  group_by(state, lapsedweek, orders) %>%
  tally()
counts <- counts %>% spread(orders, n, fill = 0)    
out <- left_join(statedf, counts, by = c("state", "week" = "lapsedweek"))
out[is.na(out)] <- 0 # convert rows with all NAs to 0s
names(out)[3:5] <- paste0("order", names(out)[3:5]) # rename columns

Answer 2

('week1') "fixedf", merge "memberdf" "fixedf", "long" "wide" dcast. "", ​​ "out".

statedf$week1 <-  statedf$week-1
df1 <- merge(memberdf[-1], statedf, by.x=c('state', 'finalweek'), 
                 by.y=c('state', 'week1'), all.y=TRUE)
lvls <- paste0(c('one', 'two', 'three'), 'order')
df1$orders <- factor(lvls[df1$orders],levels=lvls) 
library(reshape2)
out1 <- dcast(df1, state+week~orders, value.var='orders', length)[-6]
out1
#     state week oneorder twoorder threeorder
#1    MD    5        0        0          0
#2    MD    6        1        0          3
#3    MD    7        0        1          0
#4    NY    5        0        0          0
#5    NY    6        0        1          1
#6    NY    7        0        0          0

all.equal(out, out1)
#[1] TRUE