filling NA values in data frame with values common based on unique id

Question

I have a large data frame and part of it look like this:

   id carbon nitrogen sulfer
1   1     NA       NA     NA
2   1     NA       NA     NA
3   1      5        6     78
4   2     NA       NA     NA
5   2     NA       NA     NA
6   2      8        8     67
7   3     NA       NA     NA
8   3     NA       NA     NA
9   3     NA       NA     NA
10  3      7        9     55

Is there a way I can filled the NA values in columns (example carbon), with one value given common to id. For example if we take id == 1 then for column carbon has value of 5 and need to replace NA with 5. I have nearly 200 columns to filled like this.

Any help to automate this is much appreciated.

Also, is it a coincidence that on each row you either have `NA` everywhere or nowhere? — flodel, Sep 24 '13 at 04:55
OK. What I'm trying to fill the data frame for missing values. So each is a location in landscape (unique id). So values for 3 columns are same same for that unique id — user2807119, Sep 24 '13 at 05:31
The question is clear. For each id there is a unique non-NA value per column. Or could be more, but all values the same. And the position should be uncaring. — iago, Aug 29 '19 at 09:59

Henrik · Accepted Answer · 2013-09-24T11:30:30.423

2

You haven't really clarified the issues pointed out by @flodel in his comments. Anyway, given the pattern in your example data, i.e. all values of carbon nitrogen sulfer are missing, except the last within each id, then you can try this:

library(zoo)
na.locf(df, na.rm = FALSE, fromLast = TRUE)

#    id carbon nitrogen sulfer
# 1   1      5        6     78
# 2   1      5        6     78
# 3   1      5        6     78
# 4   2      8        8     67
# 5   2      8        8     67
# 6   2      8        8     67
# 7   3      7        9     55
# 8   3      7        9     55
# 9   3      7        9     55
# 10  3      7        9     55

edited Sep 24 '13 at 11:30

answered Sep 24 '13 at 10:18

Henrik

65,555
14
143
159

It works. Thanks @Henrik. What about if the level of id sub levels different from each other. Example id==1 consists of 3 sub levels while id==2 consists of 2 sub levels – user2807119 Sep 25 '13 at 01:26
And if the position is not always the last, but another one? – iago Aug 29 '19 at 10:00
@iago Then you need to fill _by group_, forwards and backwards. See e.g. [Filling in missing (blanks) in a data table, per category - backwards and forwards](https://stackoverflow.com/questions/12607465/filling-in-missing-blanks-in-a-data-table-per-category-backwards-and-forwar), [Replace NA with previous or next value, by group, using dplyr](https://stackoverflow.com/questions/40040834/replace-na-with-previous-or-next-value-by-group-using-dplyr) – Henrik Aug 29 '19 at 10:16

score 0 · Answer 2 · answered Sep 24 '13 at 09:31

Try this, I hope it what you need. If not update and I update the result. By the way this was asked a few times already.

myFunc <- function(value) {
  if (value[1] == 1) {
    value[2] = 5;
    value[3] = 5;
    value[4] = 5 
  }
  # here you put you're if's
}

apply(data, 1, myFunc)

filling NA values in data frame with values common based on unique id

2 Answers2