How to take values from a previous column in R

Question

This is what the sample looks like:

# 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")) 

So, if there is a value as -180 between bearing_2 to bearing_5, then replace it as a previous value instead. For example, in trip_id number 6, bearing_4 should be replaced with 23.7

And my result should be like this;

# 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

I know that for loops give the chills to any R user, but in same cases are useful.

Mind that this solution is viable only if you don't want to replace bearing_1 with bearing_5 from the previous row when bearing_1 == -180.

for(i in 2:5){
  test[[i+1]] <- ifelse(test[[i+1]] == -180, test[[i]], test[[i+1]])
}
test

#> # 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

Without pivot you can do this in dplyr only

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

Answer 3

here is a data.table approach

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 4

If you pivot it to a long format it may be easier to work with. Then just replace the -180 values with NA and "fill down" the last value into the NA values. Finally, pivot it back to the wide format.

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