The column trip_id does not exist. There are columns called trip_id...1 and a trip_id...9. I split the data base into two, storing columns 1 - 8 in one and columns 9 - 16 in another. I changed the column names to drop the numeric suffixes, I then combined the two and sorted by trip_id.
DF <- structure(list(trip_id...1 = c(9900082882, 12617682, 9075366,
9900319298, 14468597, 9900153369),
subscriber_type...2 = c("Walk Up", "Local365", "Local365",
"24-Hour Kiosk (Austin B-cycle)", "Walk Up", "Local30"),
start_time...3 = c("3/19/2015 19:12", "10/30/2016 2:06", "3/11/2016 16:28", "11/23/2014 15:12",
"4/16/2017 15:39", "5/29/2015 15:12"),
ride_length...4 = c(41, 6, 13, 80, 25, 29),
station_name...5 = c("Waller & 6th St.", "2nd & Congress", "Bullock Museum @ Congress & MLK",
"Capitol Station / Congress & 11th", "Rainey St @ Cummings", "17th & Guadalupe"),
latitude...6 = c(30.26461, 30.26408, 30.28039, 30.2726, 30.25579, 30.27974),
longitude...7 = c(-97.73049, -97.74355, -97.73809, -97.74127, -97.73982, -97.74254),
origin_destination...8 = c("Origin", "Origin", "Origin", "Origin", "Origin", "Origin"),
trip_id...9 = c(9900082882, 12617682, 9075366, 9900319298, 14468597, 9900153369),
subscriber_type...10 = c("Walk Up", "Local365", "Local365", "24-Hour Kiosk (Austin B-cycle)", "Walk Up","Local30"),
start_time...11 = c("3/19/2015 19:12", "10/30/2016 2:06", "3/11/2016 16:28", "11/23/2014 15:12", "4/16/2017 15:39", "5/29/2015 15:12"),
ride_length...12 = c(41, 6, 13, 80, 25, 29),
station_name...13 = c("Trinity & 6th Street", "South Congress & Academy", "Convention Center / 4th St. @ MetroRail",
"Toomey Rd @ South Lamar", "MoPac Pedestrian Bridge @ Veterans Drive", "West & 6th St."),
latitude...14 = c(30.26735, 30.25226, 30.26483, 30.26304, 30.27466, 30.27041),
longitude...15 = c(-97.73933, -97.74854, -97.739, -97.75824, -97.77028, -97.75046),
origin_destination...16 = c("Destination", "Destination", "Destination", "Destination", "Destination", "Destination")),
spec = structure(list(cols = list(trip_id...1 = structure(list(), class = c("collector_double", "collector")),
subscriber_type...2 = structure(list(), class = c("collector_character", "collector")),
start_time...3 = structure(list(), class = c("collector_character", "collector")),
ride_length...4 = structure(list(), class = c("collector_double", "collector")),
station_name...5 = structure(list(), class = c("collector_character", "collector")),
latitude...6 = structure(list(), class = c("collector_double", "collector")),
longitude...7 = structure(list(), class = c("collector_double", "collector")),
origin_destination...8 = structure(list(), class = c("collector_character", "collector")),
trip_id...9 = structure(list(), class = c("collector_double", "collector")),
subscriber_type...10 = structure(list(), class = c("collector_character", "collector")),
start_time...11 = structure(list(), class = c("collector_character", "collector")),
ride_length...12 = structure(list(), class = c("collector_double", "collector")),
station_name...13 = structure(list(), class = c("collector_character", "collector")),
latitude...14 = structure(list(), class = c("collector_double", "collector")),
longitude...15 = structure(list(), class = c("collector_double", "collector")),
origin_destination...16 = structure(list(), class = c("collector_character", "collector"))),
default = structure(list(), class = c("collector_guess", "collector")),
delim = ","), class = "col_spec"), row.names = c(NA, 6L),
class = c("spec_tbl_df", "tbl_df", "tbl", "data.frame"))
colnames(DF)
#> [1] "trip_id...1" "subscriber_type...2"
#> [3] "start_time...3" "ride_length...4"
#> [5] "station_name...5" "latitude...6"
#> [7] "longitude...7" "origin_destination...8"
#> [9] "trip_id...9" "subscriber_type...10"
#> [11] "start_time...11" "ride_length...12"
#> [13] "station_name...13" "latitude...14"
#> [15] "longitude...15" "origin_destination...16"
DF1 <- DF[, 1:8]
DF2 <- DF[, 9:16]
colnames(DF1) <- sub(pattern = "\\.{3}\\d{1,2}",replacement = "",colnames(DF1))
colnames(DF2) <- sub(pattern = "\\.{3}\\d{1,2}",replacement = "",colnames(DF2))
DFnew <- rbind(DF1, DF2)
DFnew <- DFnew[order(DFnew$trip_id), ]
DFnew
#> trip_id subscriber_type start_time ride_length
#> 3 9075366 Local365 3/11/2016 16:28 13
#> 9 9075366 Local365 3/11/2016 16:28 13
#> 2 12617682 Local365 10/30/2016 2:06 6
#> 8 12617682 Local365 10/30/2016 2:06 6
#> 5 14468597 Walk Up 4/16/2017 15:39 25
#> 11 14468597 Walk Up 4/16/2017 15:39 25
#> 1 9900082882 Walk Up 3/19/2015 19:12 41
#> 7 9900082882 Walk Up 3/19/2015 19:12 41
#> 6 9900153369 Local30 5/29/2015 15:12 29
#> 12 9900153369 Local30 5/29/2015 15:12 29
#> 4 9900319298 24-Hour Kiosk (Austin B-cycle) 11/23/2014 15:12 80
#> 10 9900319298 24-Hour Kiosk (Austin B-cycle) 11/23/2014 15:12 80
#> station_name latitude longitude
#> 3 Bullock Museum @ Congress & MLK 30.28039 -97.73809
#> 9 Convention Center / 4th St. @ MetroRail 30.26483 -97.73900
#> 2 2nd & Congress 30.26408 -97.74355
#> 8 South Congress & Academy 30.25226 -97.74854
#> 5 Rainey St @ Cummings 30.25579 -97.73982
#> 11 MoPac Pedestrian Bridge @ Veterans Drive 30.27466 -97.77028
#> 1 Waller & 6th St. 30.26461 -97.73049
#> 7 Trinity & 6th Street 30.26735 -97.73933
#> 6 17th & Guadalupe 30.27974 -97.74254
#> 12 West & 6th St. 30.27041 -97.75046
#> 4 Capitol Station / Congress & 11th 30.27260 -97.74127
#> 10 Toomey Rd @ South Lamar 30.26304 -97.75824
#> origin_destination
#> 3 Origin
#> 9 Destination
#> 2 Origin
#> 8 Destination
#> 5 Origin
#> 11 Destination
#> 1 Origin
#> 7 Destination
#> 6 Origin
#> 12 Destination
#> 4 Origin
#> 10 Destination
Created on 2024-02-29 with reprex v2.0.2