This table is a simplified version of the one I have. Now that I have multiple observations for each user, I want to include specific ones. The rule I want to apply is that if the observation for a specific user is just one observation, then include this one observation, else, include the 2nd observation only (as ordered by time for each session played).

So, I want to reach this output

I tried running multiple codes that use dplyr, and use if_else. I used the function count() to find the number of observations for each name, and then tried to filter accordingly

Here's my try

new_table<- 
old_table %>%
group_by(name) %>%
arrange(name) %>% 
if_else(count(name) %>% filter(n==1), filter (row_number()==1), filter (row_number()==2))

However it's not working. Kindly guide me to adjust it for the right code.

CodePudding user response：

A tidyverse option:

library(tidyverse)

tribble(
  ~name, ~user_id, ~session_id,
  "e_1", 111, 101,
  "e_1", 111, 102,
  "e_1", 111, 103,
  "e_2", 112, 104,
  "e_2", 112, 105,
  "e_3", 113, 106,
  "e_3", 113, 107,
  "e_4", 114, 108,
  "e_5", 115, 109
) |> 
  group_by(user_id) |> 
  filter(row_number() <= 2) |> 
  slice_tail(n = 1)
#> # A tibble: 5 × 3
#> # Groups:   user_id [5]
#>   name  user_id session_id
#>   <chr>   <dbl>      <dbl>
#> 1 e_1       111        102
#> 2 e_2       112        105
#> 3 e_3       113        107
#> 4 e_4       114        108
#> 5 e_5       115        109

^{Created on 2022-06-06 by the reprex package (v2.0.1)}

CodePudding user response：

This is not the most efficient, but should give you a solution.

Input Data:

> df
  name user_id session_id
1  e_1     111        101
2  e_1     111        102
3  e_1     111        103
4  e_2     112        104
5  e_2     112        105
6  e_3     113        106
7  e_3     113        107
8  e_4     114        108
9  e_5     115        109

And then:

rnam.df <- c()
for (n in unique(df$name)){
  new_df <- subset(df,df$name==n) # subset df by names
  ifelse(nrow(new_df)>1,Value <- 2, Value <- 1) # you only want first or second row whith same name
  rnam.df <- append(rnam.df,row.names(new_df[Value, ])) # append row names which contains the values found
}

Out:

> rnam.df
[1] "2" "5" "7" "8" "9"

> df[rnam.df, ]
  name user_id session_id
2  e_1     111        102
5  e_2     112        105
7  e_3     113        107
8  e_4     114        108
9  e_5     115        109

EDIT

3rd column should not be a problem since I did not work with. I got values from previous post before you made the edit!