如何从R中的前一列中获取值

Question

这就是示例的样子：

# A tibble: 10 x 6
   trip_id bearing_1 bearing_2 bearing_3 bearing_4 bearing_5
     <int>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>
 1       1    -115.      26.0       28.5      29.0     44.2 
 2       2     -30.0   -104.      -151.      -79.7     86.3 
 3       3    -174.    -163.        28.9      28.4     33.4 
 4       4     109.      39.7      108.      -90.4    -96.4 
 5       5     164.     173.       172.     -155.    -151.  
 6       6      29.7     28.8       23.7    -180       -5.72
 7       7    -152.    -152.      -140.      -87.8    -40.4 
 8   21364     -92.5   -102.       -33.1     -22.6     72.1 
 9   21365      36.8     -7.35     125.     -180       -6.25
10   21366     -66.6    -180      -180      -180      -180

test <- structure(list(trip_id = c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 21364L,21365L, 21366L), bearing_1 = c(-114.841862569466, -29.9669766757283,-174.421109701553, 108.540950375186, 164.27892942256, 29.6758222025729,-151.952759961779, -92.5493845489171, 36.7666184516413, -66.5951081074832), bearing_2 = c(26.0208400403598, -103.956520193288, -162.685454728109,39.7241781616749, 172.795046874931, 28.7700915265229, -151.570082970024,-101.727443940579, -7.34765841903149, -180), bearing_3 = c(28.5429778503258,-150.523766272678, 28.9060358290049, 107.799736478861, 171.649870654995,23.6732326703023, -139.805993936888, -33.0756697040324, 124.715023127817, -180), bearing_4 = c(29.0047950732172, -79.7472910710283,28.4442392651495, -90.3662617366973, -155.498976426312, -180,-87.7511498573422, -22.5583129564787, -180, -180),bearing_5 = c(44.1517444467334, 86.2565875990879, 33.44598675342,-96.4423762052258, -150.883228746413, -5.72113572073526,-40.42741337969, 72.1440072645012, -6.24787371298153, -180)), row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame")) 

因此，如果bearing_2到bearing_5之间有一个作为bearing_5的值，则将其替换为以前的值。例如，在trip_id number 6中，bearing_4应替换为23.7

我的结果应该是这样的；

# A tibble: 10 x 6
   trip_id bearing_1 bearing_2 bearing_3 bearing_4 bearing_5
     <int>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>
 1       1    -115.      26.0       28.5      29.0     44.2 
 2       2     -30.0   -104.      -151.      -79.7     86.3 
 3       3    -174.    -163.        28.9      28.4     33.4 
 4       4     109.      39.7      108.      -90.4    -96.4 
 5       5     164.     173.       172.     -155.    -151.  
 6       6      29.7     28.8       23.7       23.7   -5.72
 7       7    -152.    -152.      -140.      -87.8    -40.4 
 8   21364     -92.5   -102.       -33.1     -22.6     72.1 
 9   21365      36.8     -7.35     125.       125.    -6.25
10   21366     -66.6    -66.6      -66.6    -66.6     -66.6

Answer 1

如果您将其转换为长格式，则使用起来可能更容易。然后，只需将-180值替换为NA，并将最后一个值“填充”到NA值中。最后，将其转换回宽格式。

library(dplyr)
library(tidyr)

test %>% 
  pivot_longer(starts_with("bearing")) %>% 
  mutate(value = if_else(value == -180, NA_real_, value)) %>% 
  group_by(trip_id) %>% 
  fill(value) %>% 
  pivot_wider(names_from = "name", values_from = "value") %>% 
  ungroup()

# A tibble: 10 x 6
#    trip_id bearing_1 bearing_2 bearing_3 bearing_4 bearing_5
#      <int>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>
#  1       1    -115.      26.0       28.5      29.0     44.2 
#  2       2     -30.0   -104.      -151.      -79.7     86.3 
#  3       3    -174.    -163.        28.9      28.4     33.4 
#  4       4     109.      39.7      108.      -90.4    -96.4 
#  5       5     164.     173.       172.     -155.    -151.  
#  6       6      29.7     28.8       23.7      23.7     -5.72
#  7       7    -152.    -152.      -140.      -87.8    -40.4 
#  8   21364     -92.5   -102.       -33.1     -22.6     72.1 
#  9   21365      36.8     -7.35     125.      125.      -6.25
# 10   21366     -66.6    -66.6      -66.6     -66.6    -66.6 

Answer 2

下面是一种data.table方法

library(data.table)
# Make it a data.table
setDT(test)
# Set values that are -180 to NA. This only works if 
# there are no NA in your production data!!
test[test == -180] <- NA_real_
# Melt to long format
test.long <- setorder(melt(test, id.vars = "trip_id"), trip_id, variable)
# Fill down NA
test.long[, value_new := nafill(value, type = "locf"), by = .(trip_id)][]
# Cast to wide format again
dcast(test.long, trip_id ~ variable, value.var = "value_new")
#    trip_id  bearing_1   bearing_2  bearing_3  bearing_4   bearing_5
# 1:       1 -114.84186   26.020840   28.54298   29.00480   44.151744
# 2:       2  -29.96698 -103.956520 -150.52377  -79.74729   86.256588
# 3:       3 -174.42111 -162.685455   28.90604   28.44424   33.445987
# 4:       4  108.54095   39.724178  107.79974  -90.36626  -96.442376
# 5:       5  164.27893  172.795047  171.64987 -155.49898 -150.883229
# 6:       6   29.67582   28.770092   23.67323   23.67323   -5.721136
# 7:       7 -151.95276 -151.570083 -139.80599  -87.75115  -40.427413
# 8:   21364  -92.54938 -101.727444  -33.07567  -22.55831   72.144007
# 9:   21365   36.76662   -7.347658  124.71502  124.71502   -6.247874
#10:   21366  -66.59511  -66.595108  -66.59511  -66.59511  -66.595108

Answer 3

没有pivot，您只能在dplyr中完成这一任务。

test %>% 
  mutate(dummy = max.col(test[-1] == -180, ties.method = 'first')-1) %>%
  rowwise() %>%
  mutate(across(starts_with('bearing'), 
                       ~ifelse(. == -180, 
                               get(paste0('bearing_', dummy)), 
                               .))) %>%
  select(-dummy)

# A tibble: 10 x 6
# Rowwise: 
   trip_id bearing_1 bearing_2 bearing_3 bearing_4 bearing_5
     <int>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>
 1       1    -115.      26.0       28.5      29.0     44.2 
 2       2     -30.0   -104.      -151.      -79.7     86.3 
 3       3    -174.    -163.        28.9      28.4     33.4 
 4       4     109.      39.7      108.      -90.4    -96.4 
 5       5     164.     173.       172.     -155.    -151.  
 6       6      29.7     28.8       23.7      23.7     -5.72
 7       7    -152.    -152.      -140.      -87.8    -40.4 
 8   21364     -92.5   -102.       -33.1     -22.6     72.1 
 9   21365      36.8     -7.35     125.      125.      -6.25
10   21366     -66.6    -66.6      -66.6     -66.6    -66.6