Replace NA in one row of data frame with values from other

Question

i want to replace NA in one row with values from another row, example data are:

group <-c('A','A_old')
year1<- c(NA,'20')
year2<- c(NA,'40')
year3<- c('20','230')
datac=data_frame(group,year1,year2,year3)

group <-c('A','A_old')
year1<- c('20','20')
year2<- c('40','40')
year3<- c('20','230')
finaldatac=data_frame(group,year1,year2,year3)

Original table is much larger so referring to each element one by one and assigning value is not possible..

Thanks!

For sake of argument below, i need to refer to the row values by their name as original table is big and i can not play around with only two rows. For example in table below, i would like to replace row 1 (group==A) with row 5 (group==E). Data are here:

group <-c('A','B','C','D','E','F','G')
year1<- c(NA,'100',NA,'200','300',NA,NA)
year2<- c(NA,'100',NA,'200','300','50','40')
year3<- c('20','100',10,'200','300','150','230')
data=data.frame(group,year1,year2,year3)

SO i want to get:

group <-c('A','B','C','D','E','F','G')
year1<- c('300','100',NA,'200','300',NA,NA)
year2<- c('300','100',NA,'200','300','50','40')
year3<- c('20','100',10,'200','300','150','230')
data=data.frame(group,year1,year2,year3)

Perhaps you need datac %>% fill(!!! rlang::syms(names(.)), .direction = 'up') — akrun
– akrun, Commented Jan 27, 2019 at 20:02
Or you can try datac %>% fill(everything(), .direction = "up"). — tmfmnk
– tmfmnk, Commented Jan 27, 2019 at 20:07
thanks for suggestions, unfortunately my original data are much larger so it will have more than just two groups (rows) and in many cases they are not next to each other.. can i refer somehow to the rows i want to replace directly by group value? — nov
– nov, Commented Jan 27, 2019 at 20:18

arg0naut91 · Accepted Answer · 2019-01-27 22:52:44Z

1

Other than using fill or na.locf, you could do:

datac %>%
  group_by(grp = gsub("_.*", "", group)) %>%
  mutate_at(vars(contains("year")),
                 funs(.[!is.na(.)])) %>%
  ungroup() %>% select(-grp)

Output:

# A tibble: 2 x 4
  group year1 year2 year3
  <chr> <chr> <chr> <chr>
1 A     20    40    20   
2 A_old 20    40    230

For your second example, you could do:

data %>%
  mutate_at(
    vars(contains("year")), 
    funs(
      case_when(
        group == "A" & is.na(.) ~ .[group == "E"],
        TRUE ~ .)
      )
    )

Output:

  group year1 year2 year3
1     A   300   300    20
2     B   100   100   100
3     C  <NA>  <NA>    10
4     D   200   200   200
5     E   300   300   300
6     F  <NA>    50   150
7     G  <NA>    40   230

You can also add other conditions to case_when.

For instance, if you'd additionally like to replace C years with what is there for group D, you would add:

data %>%
  mutate_at(
    vars(contains("year")), 
    funs(
      case_when(
        group == "A" & is.na(.) ~ .[group == "E"],
        group == "C" & is.na(.) ~ .[group == "D"],
        TRUE ~ .)
    )
  )

edited Jan 27, 2019 at 22:52

answered Jan 27, 2019 at 20:35

arg0naut91

14.8k2 gold badges22 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

nov Over a year ago

Thanks a lot, but in case i have big data and only few rows i want to modify how i would i refer to exact rows with which i want to values?

arg0naut91 Over a year ago

Hm, hard to judge - I think in this case you would need to provide us with a larger example of your dataframe, both input and final output.

nov Over a year ago

Thanks! Can this be modified so that A take value of E only when A==NA?, so it would be 300,300,20...

arg0naut91 Over a year ago

Of course - see the modified code. Basically you can manipulate this with various different conditions just like in normal ifelse statements.

nov · Accepted Answer · 2019-01-27 21:53:25Z

After a very long evening and headache from r i managed to get this:

rm(list = ls())
group <-c('A','A old')
year1<- c(NA,'20')
year2<- c(NA,'40')
year3<- c('20','230')
datac=data_frame(group,year1,year2,year3)
group <-c('A','A old')
year1<- c('20','20')
year2<- c('40','40')
year3<- c('20','230')
finaldatac=data_frame(group,year1,year2,year3)


datac$group <- gsub(' ', '--', datac$group)
datact = t(datac)
colnames(datact) = datact[1, ] 
datact = datact[-1, ] 

datact[,"A"]  <- ifelse(!is.na(datact[,"A"]), datact[,"A"] , datact[,"A--old"])
datactt=t(datact)
group = rownames(datactt)
datactt<-cbind(datactt, group)
rownames(datactt) <- c()
datactt <- as.data.frame(datactt)

sapply(datactt, class)
datactt <- data.frame(lapply(datactt, as.character), stringsAsFactors=FALSE)


datactt$group <- gsub('--', ' ', datactt$group)

Where datactt (hopefully) is the same as finaldatac that i wanted... I am sure this cant be the best solution, obviously not the prettiest. If anybody has something similar, but shorter or more efficient please post it i would appreciate the answer.

Alan Gómez · Accepted Answer · 2023-11-19 00:48:15Z

0

A simple replacement in Base R can be done with:

data[1,is.na(data[1,])] <- data[5,is.na(data[1,])]

Taking in account that only need to know row indices of desired replacements.

answered Nov 19, 2023 at 0:48

Alan Gómez

3881 gold badge4 silver badges11 bronze badges

Collectives™ on Stack Overflow

Replace NA in one row of data frame with values from other

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related