R - how to count users based on open/close dates of accounts, but with users having multiple accounts

Here's how I'd do it:

library(tidyverse)

set.seed(14159)

## generating some data that looks roughly
##  like your data

data <- tibble(
  user = sample(LETTERS[1:5], size = 20, replace = TRUE),
  open = sample(seq(as.Date('1999/01/01'), as.Date('2000/01/01'), by="day"), 20),
  close = sample(seq(as.Date('1999/01/01'), as.Date('2000/01/01'), by="day"), 20),
  area = sample(c("N", "E", "S", "W"), 20, replace = T)
) %>%
  filter(
    close > open
  )

data
#> # A tibble: 9 × 4
#>   user  open       close      area 
#>   <chr> <date>     <date>     <chr>
#> 1 A     1999-04-03 1999-07-28 N    
#> 2 B     1999-01-27 1999-05-12 W    
#> 3 B     1999-06-05 1999-12-29 W    
#> 4 C     1999-09-26 1999-12-30 W    
#> 5 C     1999-04-21 1999-12-04 E    
#> 6 C     1999-08-11 1999-12-12 N    
#> 7 A     1999-02-13 1999-09-16 W    
#> 8 E     1999-02-17 1999-05-21 E    
#> 9 B     1999-07-26 1999-08-16 S

## figuring out what months are in between open and close
get_months_in_range <- function(open, close) {
  seq.Date(
    open,
    close,
    by = "month"
  ) %>%
    list()
}

data %>%
  rowwise() %>%
  mutate(
    Month = get_months_in_range(open, close)
  ) %>%
  ungroup() %>%
  unnest_longer(
    col = Month
  ) %>%
  count(Month, area) %>%
  pivot_wider(
    names_from = area,
    values_from = n,
    values_fill = 0
  ) %>%
  rowwise() %>%
  mutate(
    Total = sum(
      c_across(
        -Month
      )
    )
  ) %>%
  ungroup()
#> # A tibble: 45 × 6
#>    Month          W     E     N     S Total
#>    <date>     <int> <int> <int> <int> <int>
#>  1 1999-01-27     1     0     0     0     1
#>  2 1999-02-13     1     0     0     0     1
#>  3 1999-02-17     0     1     0     0     1
#>  4 1999-02-27     1     0     0     0     1
#>  5 1999-03-13     1     0     0     0     1
#>  6 1999-03-17     0     1     0     0     1
#>  7 1999-03-27     1     0     0     0     1
#>  8 1999-04-03     0     0     1     0     1
#>  9 1999-04-13     1     0     0     0     1
#> 10 1999-04-17     0     1     0     0     1
#> # … with 35 more rows

^{Created on 2021-08-18 by the reprex package (v2.0.1)}

It's not the world's sexiest solution, but I think it'll get you where you're trying to go. Basically, I just make a helper function that gives me all the dates between open and close and then you can group by those to figure out how many users you have in any given month. Let me know if you want more explanation about what the long chain of dplyr stuff is doing.

This is a general solution working for dates spanning over more than one year.

library(dplyr)
library(tidyr)
library(lubridate)

data %>%
  group_by(rn = row_number()) %>%
  mutate(seq = list(seq(month(Open), month(Close) + 12 * (year(Close) - year(Open))))) %>% 
  unnest(seq) %>%
  mutate(
    seq_2 = (seq - 1) %% 12 + 1,
    month = month(seq_2, label = TRUE),
    year  = year(Open + months(seq - first(seq)))
    ) %>% 
  ungroup() %>% 
  distinct(User, month, year, Area) %>% 
  count(month, year, Area) %>% 
  pivot_wider(
    names_from = "Area", 
    values_from = "n", 
    values_fill = 0
    ) %>% 
  mutate(Total = rowSums(across(c(North, South, West, East))))

returns

  month  year North South  West  East Total
  <ord> <dbl> <int> <int> <int> <int> <dbl>
1 Feb    2018     1     1     1     0     3
2 Mar    2018     2     1     1     1     5
3 Apr    2018     2     2     2     1     7
4 May    2018     1     2     2     1     6
5 Jun    2018     1     2     2     1     6
6 Jul    2018     0     1     1     1     3

Data

df <- structure(list(id = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10), User = c("A", 
"B", "B", "C", "C", "D", "E", "E", "F", "G"), Open = structure(c(17575, 
17588, 17589, 17589, 17605, 17610, 17622, 17635, 17610, 17647
), class = "Date"), Close = structure(c(17743, 17686, 17605, 
17677, 17685, 17714, 17701, 17655, 17640, 17716), class = "Date"), 
    Area = c("West", "North", "North", "South", "South", "East", 
    "West", "West", "North", "South")), problems = structure(list(
    row = 10L, col = "Area", expected = "", actual = "embedded null", 
    file = "literal data"), row.names = c(NA, -1L), class = c("tbl_df", 
"tbl", "data.frame")), class = c("spec_tbl_df", "tbl_df", "tbl", 
"data.frame"), row.names = c(NA, -10L), spec = structure(list(
    cols = list(id = structure(list(), class = c("collector_double", 
    "collector")), User = structure(list(), class = c("collector_character", 
    "collector")), Open = structure(list(format = ""), class = c("collector_date", 
    "collector")), Close = structure(list(format = ""), class = c("collector_date", 
    "collector")), Area = structure(list(), class = c("collector_character", 
    "collector"))), default = structure(list(), class = c("collector_guess", 
    "collector")), skip = 1L), class = "col_spec"))

welcome to SO. I can't test this code as you haven't provided a snippet of your data in the right format (see below for a suggestion on this point), but I think the basic idea of what you want to do is extract a month-year value from Open and then use group_by. For example:

library(lubridate)
library(dplyr)

df %>% mutate(
  Date = dmy(Open),
  Month_Yr = format_ISO8601(Date, precision = "ym")) %>% 
  group_by(Month_Yr) %>% 
  distinct(PERSON.ID, .keep_all = TRUE) %>%
  count(Area) 

Generally when sharing data on SO it's best to use a dput. See ?dput for info on how to use it if you're unsure.