CODEWITHSUNDEEP
rdataframe
Archer Jiang
Archer Jiang

Reputation: 61

How to replace NAs with values that has the same reference combination in R?

I have a data frame like the one below. There are some missing values in val1 and val2 columns.

  con1 con2 con3 val1 val2
1    C   U2    m   12   20
2    C   U3    m   NA   NA
3    C   U4    f    1   37
4    D   Y1    m   32   28
5    D   Y5    c   93   19
6    D   Y6    c   NA   NA
7    E   Z4    m   NA   26
8    E   Z7    m   NA   15
9    E   Z9    f   47   23

What I intend to achieve is try to replace the NAs in val1 and val2 with the values where the combinations of con1 and con3 match. The intended result is as follows:

 con1 con2 con3 val1 val2
1    C   U2    m   12   20
2    C   U3    m   12   12
3    C   U4    f    1   37
4    D   Y1    m   32   28
5    D   Y5    c   93   19
6    D   Y6    c   93   19
7    E   Z4    m   NA   26
8    E   Z7    m   NA   15
9    E   Z9    f   47   23

Note that the matching rows are not always adjacent. Also, there are cases where there's no value to replace with (such as val1 for row7 and row8), and we just skip those instances.

Upvotes: 0

Views: 167

Answers (1)

Maël
Maël

Reputation: 51894

You can group_by con1 and con3 and use tidyr::fill to replace the NAs with the values from the group. For the second row, I'm just guessing there's a typo in your expected output.

library(dplyr)
library(tidyr)
df %>% 
  group_by(con1, con3) %>% 
  fill(val1, val2)

which gives

# A tibble: 9 x 5
# Groups:   con1, con3 [6]
  con1  con2  con3   val1  val2
  <chr> <chr> <chr> <int> <int>
1 C     U2    m        12    20
2 C     U3    m        12    20
3 C     U4    f         1    37
4 D     Y1    m        32    28
5 D     Y5    c        93    19
6 D     Y6    c        93    19
7 E     Z4    m        NA    26
8 E     Z7    m        NA    15
9 E     Z9    f        47    23

data

df <- read.table(header = T, text = "  con1 con2 con3 val1 val2
1    C   U2    m   12   20
2    C   U3    m   NA   NA
3    C   U4    f    1   37
4    D   Y1    m   32   28
5    D   Y5    c   93   19
6    D   Y6    c   NA   NA
7    E   Z4    m   NA   26
8    E   Z7    m   NA   15
9    E   Z9    f   47   23")

Upvotes: 1

Related Questions