r语言 - 将 NULL 替换为不同 ID 的同一列中的值

Question

在我的数据框的一列中，我有一些空单元格。数据看起来像这样：

LoanID  PaymentMonth  Country  
112345  201301        {null}
112345  201402        {null}
112345  201403        UK
234567  201301        US
234567  201302        {null}
234567  201303        {null}

我需要替换为独特的贷款ID的零。所需的结果就像这样

LoanID  PaymentMonth  Country  
112345  201301        UK
112345  201402        UK
112345  201403        UK
234567  201301        US
234567  201302        US
234567  201303        US

如何解决此问题？

Answer 1

with tidyverse：

library(tidyr)
library(dplyr)
df %>% 
    mutate(Country = case_when(Country == '{null}' ~ NA_character_,
                               TRUE ~ Country)) %>% 
    group_by(LoanID) %>% 
    fill(Country, .direction = 'up') %>% 
    fill(Country, .direction = 'down')
#> Source: local data frame [6 x 3]
#> Groups: LoanID [2]
#> 
#>   LoanID PaymentMonth Country
#>    <int>        <int>  <fctr>
#> 1 112345       201301      UK
#> 2 112345       201402      UK
#> 3 112345       201403      UK
#> 4 234567       201301      US
#> 5 234567       201302      US
#> 6 234567       201303      US

数据：

df <- read.table(text = 'LoanID  PaymentMonth  Country  
112345  201301        {null}
                 112345  201402        {null}
                 112345  201403        UK
                 234567  201301        US
                 234567  201302        {null}
                 234567  201303        {null}', header = T, stringsAsFactors = F)

或者，如果可能从一开始就对您的输入数据进行消毒，请放弃mutate步骤：

df <- read.table(text = 'LoanID  PaymentMonth  Country  
112345  201301        {null}
                 112345  201402        {null}
                 112345  201403        UK
                 234567  201301        US
                 234567  201302        {null}
                 234567  201303        {null}', header = T, na.string = '{null}')
df %>% 
    group_by(LoanID) %>% 
    fill(Country, .direction = 'up') %>% 
    fill(Country, .direction = 'down')

Answer 2

假设'country'是 character class，而 {null}是字符串，我们可以用 NA替换它，然后从 zoo中使用 na.locf将缺失值替换为相邻的非NA元素

library(zoo)
df1$Country[df1$Country=="{null}"] <- NA
df1$Country <-  with(df1, ave(Country, LoanID, FUN = function(x)
                 na.locf(na.locf(x, na.rm = FALSE), fromLast=TRUE)))
df1
#   LoanID PaymentMonth Country
#1 112345       201301      UK
#2 112345       201402      UK
#3 112345       201403      UK
#4 234567       201301      US
#5 234567       201302      US
#6 234567       201303      US

---

根据评论，也可以通过" LoanId"进行分组，然后使用第一个非`{null}'元素更新"国家"列

library(dplyr)
df1 %>%
    group_by(LoanID) %>%
    mutate(Country = Country[Country!= "{null}"][1L])
#  LoanID PaymentMonth Country
#   <int>        <int>   <chr>
#1 112345       201301      UK
#2 112345       201402      UK
#3 112345       201403      UK
#4 234567       201301      US
#5 234567       201302      US
#6 234567       201303      US

数据

df1 <- structure(list(LoanID = c(112345L, 112345L, 112345L, 234567L, 
 234567L, 234567L), PaymentMonth = c(201301L, 201402L, 201403L, 
 201301L, 201302L, 201303L), Country = c("{null}", "{null}", "UK", 
 "US", "{null}", "{null}")), .Names = c("LoanID", "PaymentMonth", 
 "Country"), class = "data.frame", row.names = c(NA, -6L))