Create new dataframe based on sequential row values

user_13510834 0
Viewed 123

I have a dataframe of names and years, with a dummy variable for whether the name occurred in a year or not.

I'm trying to create a dataframe which tell me

    1. the total number names which appeared in that year, and
    1. the number of those which appeared in that year but not in the year before.

In the below example, in 2017 there is only one person occurring (Terry) and nothing for the previous year, so both total and new would be 1. In 2018 three people occur but only two are new as Terry occurred in the previous year. If somebody appeared in 2017 and 2019 but not in 2018, they should be classed as new in 2019.

EXAMPLE

   Name x2017 x2018 x2019
1 Terry     1     1     0
2   Sam     0     0     1
3   Nic     0     1     1
4 Sarah     0     1     1

CODE

data.frame(
  Name = c("Terry", "Sam", "Nic", "Sarah"), 
  x2017 = c(1, 0, 0, 0), 
  x2018 = c(1, 0, 1, 1), 
  x2019 = c(0, 1, 1, 1)
  )

OUTPUT I'M TRYING TO CREATE

  Year Total New
1 2017     1   1
2 2018     3   2
3 2019     3   1

I've tried filtering and using row sums, but I feel like there's a function which I don't know of that can do this.

Thanks!

accumulate rolling-computation dplyr tidyverse r
4 Answers

The mutate(new = as.numeric(values == 1 & lag(values) == 0), new = ifelse(is.na(new), values, new)) %>%part is from stefan (credits to him, thank you stefan). The difference is parse_number

library(tidyverse)
df %>% 
  pivot_longer(
    cols = -Name,
    names_to = "Year", 
    values_to = "values"
  ) %>% 
  mutate(Year = parse_number(Year)) %>% 
  mutate(new = as.numeric(values == 1 & lag(values) == 0),
         new = ifelse(is.na(new), values, new)) %>% 
  group_by(Year) %>% 
  summarise(Total = sum(values), New = sum(new))

output:

   Year Total   New
* <dbl> <dbl> <dbl>
1  2017     1     1
2  2018     3     2
3  2019     3     1
user_133216470

Updated-02 I am so sorry as I had to revise my solution, cause I realized that only when a name has occurred in the previous year it is not considered new so you can use also use this for your sample data and the one presented below the page:

library(dplyr)
library(purrr)


df %>% 
  summarise(across(2:4, ~ sum(.x))) %>%
  bind_cols() %>% 
  pivot_longer(everything(), names_to = "Year", values_to = "Total", 
               names_prefix = "x") %>%
  left_join(df %>% select(2:4) %>% pmap_dfr(~ {x <- c(...); x - lag(x, default = 0)}) %>% 
              summarise(across(everything(), ~ sum(.x == 1))) %>% 
              pivot_longer(everything(), names_to = "Year", values_to = "New", 
                           names_prefix = "x"), 
            by = "Year")
 

# A tibble: 3 x 3
  Year  Total   New
  <chr> <dbl> <dbl>
1 2017      1     1
2 2018      3     2
3 2019      3     1
user_143145200

Maybe this is what you are looking for:

  1. Reshape to long format using e.g. tidy::pivot_longer
  2. Group by Name and make use of dplyr::lag to add an indicator whether a person is new
  3. Summarise by year
d <- data.frame(
  Name = c("Terry", "Sam", "Nic", "Sarah"), 
  x2017 = c(1, 0, 0, 0), 
  x2018 = c(1, 0, 1, 1), 
  x2019 = c(0, 1, 1, 1)
) 

library(dplyr)
library(tidyr)

d %>% 
  tidyr::pivot_longer(-Name, names_to = "year") %>% 
  mutate(year = gsub("^x", "", year)) %>%
  group_by(Name) %>% 
  mutate(new = as.numeric(value == 1 & lag(value) == 0),
         new = ifelse(is.na(new), value, new)) %>% 
  ungroup() %>% 
  group_by(year) %>% 
  summarise(total = sum(value), new = sum(new))
#> # A tibble: 3 x 3
#>   year  total   new
#>   <chr> <dbl> <dbl>
#> 1 2017      1     1
#> 2 2018      3     2
#> 3 2019      3     1
user_129938610

case-I When records have to be checked in one previous row only.

df %>%
  pivot_longer(!Name, names_to = 'Year', names_prefix = 'x') %>%
  group_by(Year) %>%
  summarise(total = sum(value),
            new = list(Name[value == 1]), .groups = 'drop') %>%
  mutate(new = map2_int(new, lag(new), ~ sum(!(.x %in% .y))))

# A tibble: 3 x 3
  Year  total   new
  <chr> <dbl> <int>
1 2017      1     1
2 2018      3     2
3 2019      3     1

Case-II when records have to be looked into all previous rows. Using both map_* and accumulate. The strategy adopted-

  • pivot_longer first. drop x from years directly here using names_prefix argument
  • group_by on year then
  • calculate total values n() and names for that year in a list
  • mutate new using map2_int with first argument as that list only, and second argument as accumulated and lagged list .
  • map2_int thus calculates the total number of TRUE in that row.

library(tidyverse)
df %>%
  pivot_longer(!Name, names_to = 'Year', names_prefix = 'x') %>%
  group_by(Year) %>%
  summarise(total = sum(value),
         new = list(Name[value == 1]), .groups = 'drop') %>%
  mutate(new = map2_int(new, lag(accumulate(new, union, .init = first(new))[-1]), ~ sum(!(.x %in% .y))))

#> # A tibble: 3 x 3
#>   Year  total   new
#>   <chr> <int> <int>
#> 1 2017      1     1
#> 2 2018      3     2
#> 3 2019      3     1
user_28848590
Related