Fill values in column according to row type

user_13174189 0
Viewed 44

I have a datafarme:

ID      Type     Direction
1        A         Rigth
1        B         Rigth
2        AC        Left
2        BC        Right 
3        C         Right 
1        A         NA
1        B         NA
2        AC        NA
2        BC        NA 
3        C         NA 
1        A         NA
1        B         NA
2        AC        NA
2        BC        NA 
3        C         NA

I want to fill those NA in column Direction accordingly to ID and Type of row (cases with not empty column Direction). So desired result must look like this:

ID      Type     Direction
1        A         Rigth
1        B         Rigth
2        AC        Left
2        BC        Right 
3        C         Right 
1        A         Rigth
1        B         Rigth
2        AC        Left
2        BC        Right 
3        C         Right 
1        A         Rigth
1        B         Rigth
2        AC        Left
2        BC        Right 
3        C         Right

How could i do this?

dataframe r
3 Answers

We can do a group by 'ID', Type' and fill

library(dplyr)
library(tidyr)
df1 %>%
    group_by(ID, Type) %>%
    fill(Direction)

-output

# A tibble: 15 x 3
# Groups:   ID, Type [5]
#      ID Type  Direction
#   <int> <chr> <chr>    
# 1     1 A     Rigth    
# 2     1 B     Rigth    
# 3     2 AC    Left     
# 4     2 BC    Right    
# 5     3 C     Right    
# 6     1 A     Rigth    
# 7     1 B     Rigth    
# 8     2 AC    Left     
# 9     2 BC    Right    
#10     3 C     Right    
#11     1 A     Rigth    
#12     1 B     Rigth    
#13     2 AC    Left     
#14     2 BC    Right    
#15     3 C     Right

data

df1 <- structure(list(ID = c(1L, 1L, 2L, 2L, 3L, 1L, 1L, 2L, 2L, 3L, 
1L, 1L, 2L, 2L, 3L), Type = c("A", "B", "AC", "BC", "C", "A", 
"B", "AC", "BC", "C", "A", "B", "AC", "BC", "C"), Direction = c("Rigth", 
"Rigth", "Left", "Right", "Right", NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA)), class = "data.frame", row.names = c(NA, -15L))
user_37322710

using data.table and pre-sorting.

library(magrittr)
library(data.table)
df <- as.data.table(df)[order(df$ID, df$Type)] %>% 
  .[, Direction := first(Direction), by = .(ID, Type)]
df

Output:

> df
    ID Type Direction
 1:  1    A     Rigth
 2:  1    A     Rigth
 3:  1    A     Rigth
 4:  1    B     Rigth
 5:  1    B     Rigth
 6:  1    B     Rigth
 7:  2   AC      Left
 8:  2   AC      Left
 9:  2   AC      Left
10:  2   BC     Right
11:  2   BC     Right
12:  2   BC     Right
13:  3    C     Right
14:  3    C     Right
15:  3    C     Right

Data:

df <- structure(list(ID = c(1L, 1L, 2L, 2L, 3L, 1L, 1L, 2L, 2L, 3L, 
1L, 1L, 2L, 2L, 3L), Type = c("A", "B", "AC", "BC", "C", "A", 
"B", "AC", "BC", "C", "A", "B", "AC", "BC", "C"), Direction = c("Rigth", 
"Rigth", "Left", "Right", "Right", NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA)), row.names = c(NA, -15L), class = c("data.table", 
"data.frame"))
user_54425270

Here is another option using left_join + subset

left_join(
  df[c("ID", "Type")],
  subset(df, complete.cases(df)),
  all = TRUE
)

which gives

   ID Type Direction
1   1    A     Rigth
2   1    B     Rigth
3   2   AC      Left
4   2   BC     Right
5   3    C     Right
6   1    A     Rigth
7   1    B     Rigth
8   2   AC      Left
9   2   BC     Right
10  3    C     Right
11  1    A     Rigth
12  1    B     Rigth
13  2   AC      Left
14  2   BC     Right
15  3    C     Right
user_121587570
Related