CODEWITHSUNDEEP
rmissing-data
hk2
hk2

Reputation: 487

Populating the blank values of a column based on same values in another column using R

I have a sample dataset:


df= data.frame(Id=c(NA, "601","865",NA,"743"), Date= as.Date(c("12/23/2019","12/12/2019","12/23/2019",
"11/21/2019","12/19/2019"), format = "%m/%d/%Y" ),
 Amount=c(1100,-1100,-5055,675,-675), stringsAsFactors = F)

which would look like:

| Id  |    Date    | Amount |
|-----|:----------:|-------:|
| NA  | 12/23/2019 |   1100 |
| 601 | 12/12/2019 |  -1100 |
| 865 | 12/23/2019 |  -5055 |
| NA  | 11/21/2019 |    675 |
| 743 | 12/19/2019 |   -675 |

Now, what I want to do is to populate the NA rows of column 'Id' such that the NA in Id will be populated with the same Id values having same amount. In other words, when the amount is same (irrespective of it being a positive or a negative value), the NA Id value will be populated with the same value Id value having the same amount. In the above example, we see that the first row has NA and the second row has Id value of 601. Since both belong to same amount 1100 (ignore the positive and negative values), the NA should be populated with same Id value, i.e., 601. The third row does not have multiple occurrence with NA in the Id column, so we will move ahead with the next row which has a NA with same amount value as the fifth row and populate it. The final output should look like the one below:

| Id  |    Date    | Amount |
|-----|:----------:|-------:|
| 601 | 12/23/2019 |   1100 |
| 601 | 12/12/2019 |  -1100 |
| 865 | 12/23/2019 | -5055  |
| 743 | 11/21/2019 |  675   |
| 743 | 12/19/2019 | -675   |

Upvotes: 0

Views: 66

Answers (1)

Allan Cameron
Allan Cameron

Reputation: 173793

You can try this:

df$Id[is.na(df$Id)] <- df$Id[!is.na(df$Id)][match(abs(df$Amount[is.na(df$Id)]), abs(df$Amount[!is.na(df$Id)]))]
df
#>    Id       Date Amount
#> 1 601 2019-12-23   1100
#> 2 601 2019-12-12  -1100
#> 3 865 2019-12-23  -5055
#> 4 743 2019-11-21    675
#> 5 743 2019-12-19   -675

Upvotes: 3

Related Questions