R: Replacing Nas in a Data.Frame with Values in the Same Position in Another Dataframe

Replace NAs in dataframe with values from second dataframe based on multiple criteria

You can create a unique key to update df2.

unique_key1 <- paste(df1$A, df1$B)
unique_key2 <- paste(df2$A, df2$B)
inds <- is.na(df2$C)
df2$C[inds] <- df1$C[match(unique_key2[inds], unique_key1)]
df2

#         A     B  C    E
#1  20210901 15:00 74 A 74
#2  20210903 17:00 27 C 27
#3  20210904 18:00 60 D 60
#4  20210906 20:00  7  F 7
#5  20210907 21:00 96 G 96
#6  20210908 22:00 98 H 98
#7  20210909 23:00 38 I 38
#8  20210910 00:00 89 J 89
#9  20210912 02:00 69 L 69
#10 20210913 03:00 72 M 72
#11 20210914 04:00 76 N 76
#12 20210915 05:00 63 O 63
#13 20210916 06:00 13 P 13
#14 20210918 08:00 25 R 25
#15 20210919 09:00 92 S 92
#16 20210920 10:00 21 T 21
#17 20210921 11:00 79 U 79
#18 20210922 12:00 41 V 41
#19 20210924 14:00 97 X 97
#20 20210925 15:00 16 Y 16

data

cbind creates a matrix, use data.frame to create dataframes.

df1 <- data.frame(A, B, C, D)
df2 <- data.frame(A, B, C, E)

R: replacing NAs in a data.frame with values in the same position in another dataframe

You can do:

dfa <- data.frame(a=c(1,NA,3,4,5,NA),b=c(1,5,NA,NA,8,9),c=c(7,NA,NA,NA,2,NA))
dfrepair <- data.frame(a=c(2:7),b=c(6:1),c=c(8:3))
dfa[is.na(dfa)] <- dfrepair[is.na(dfa)]
dfa

  a b c
1 1 1 7
2 3 5 7
3 3 4 6
4 4 3 5
5 5 8 2
6 7 9 3

Replacing NAs in a dataframe based on a partial string match (in another dataframe) in R

You can create create a regex pattern from the State_Name and use str_extract to extract it from Article. Use match to get the corresponding Abbreviation name from df1.

library(stringr)

df2$State <- df1$Abbreviation[match(str_extract(df2$Article, 
               str_c(df1$State_Name, collapse = '|')), df1$State_Name)]
df2$State
#[1] "TX" "CA" "NY" "MD" NA   "VA"

You can also use inbuilt state.name and state.abb instead of df1 to get state name and abbreviations.

---

Here's a way to do this in for loop -

for(i in seq(nrow(df1))) {
  inds <- grep(df1$State_Name[i], df2$Article)
  if(length(inds)) df2$State[inds] <- df1$Abbreviation[i]
}
df2

#  Index State                                                                      Article
#1     1    TX                              Texas governor, Abbott, signs new abortion bill
#2     2    CA                     Effort to recall California governor Newsome loses steam
#3     3    NY New York governor, Cuomo, accused of manipulating Covid-19 nursing home data
#4     4    MD     Hogan (Maryland, R) announces plans to lift statewide Covid restrictions
#5     5  <NA>                                     DC statehood unlikely as Manchin opposes
#6     6    VA               Amazon HQ2 causing housing prices to soar in northern Virginia

How to replace NAs of a variable with values from another dataframe

Here's a quick solution using data.tables binary join this will join only gender with sex and leave all the rest of the columns untouched

library(data.table)
setkey(setDT(df1), ID)
df1[df2, gender := i.sex][]
#     ID gender
#  1:  1      2
#  2:  2      2
#  3:  3      1
#  4:  4      2
#  5:  5      2
#  6:  6      2
#  7:  7      2
#  8:  8      2
#  9:  9      2
# 10: 10      2
# 11: 11      2
# 12: 12      2
# 13: 13      1
# 14: 14      1
# 15: 15      2
# 16: 16      2
# 17: 17      2
# 18: 18      2
# 19: 19      2
# 20: 20      2
# 21: 21      1
# 22: 22      2
# 23: 23      2
# 24: 24      2
# 25: 25      2
# 26: 26      2
# 27: 27      2
# 28: 28      2
# 29: 29      2
# 30: 30      2

Creating a function to replace NAs from one data frame with values from another

Functions behave a little differently. It is not a good practice to change dataframes within the function, return the changed dataframe from the function and pass the column name as string.

impute <- function(x) {
  df_raw[[x]] <- ifelse(is.na(df_raw[[x]]), miceoutput[[x]][inds],df_raw[[x]])
  df_raw
}

df_raw <- impute("PIDS_14")
df_raw

How to replace all values in data frame with another data frame except NA?

Here's a way -

df1_B[is.na(df1_B)] <- df_A[is.na(df1_B)]

  n  s     b
1 2 aa  TRUE
2 4 bb FALSE
3 5 bb  TRUE

Replace NA values with value from another column in same dataframe

I would try with standard subsetting:

#subset the NAs of new price with the ones from price
df$NewPrice[is.na(df$NewPrice)] <- df$Price[is.na(df$NewPrice)]

Out:

df
#  Item   From Price Discount NewPrice
#1    A  Delhi   100      0.1      110
#2    A Mumbai   200      0.1      120
#3    A   Pune   150       NA      150
#4    A Nagpur   200      0.1      200

---

Related Topics