How can I remove the file extensions from file Names and assign them to column names?

Question

I have a dataframe A that has generic column names: V1 - V24. And I have 19 column names stored in a separate dataframe B that I need to assign to A. I want to:

Remove .tif from the list of column names in dataframe B.
The first 5 column names I am doing it manually. But rest of them should be read from the list B in the order it is given. Basically, V6 = bio_1, V7 = bio_10, etc.

Code:

library(dplyr)
B <- read.table('filenames.txt')
B$V1 <- as.character(B$V1)

B <- B %>%
  mutate(col2 = strsplit(V1, "\\.")[[1]][1])

Also,

A <- read.csv('futuredata.csv',header=F)

A <- A %>%
  rename(ID = V1, gauge = V2, lat = V3, lon = V4, area = V5) %>%

Data:

dput(A)

structure(list(V1 = 1:3, V2 = c(1094000L, 1100600L, 1096000L), 
    V3 = c(-71.506667, -71.215278, -71.658333), V4 = c(42.8575, 
    42.568056, 42.634167), V5 = c(442.888, 94.5346, 170.6802), 
    V6 = c(73.805907, 91.644231, 80.292398), V7 = c(190.198312, 
    205.201923, 196.80117), V8 = c(-50.635021, -26.259615, -43.94152
    ), V9 = c(1136.151899, 1118.826923, 1112.157895), V10 = c(113.563291, 
    114.557692, 112.538012), V11 = c(84.995781, 84.105769, 84.327485
    ), V12 = c(7.845992, 8.846154, 8), V13 = c(311.603376, 308.817308, 
    307.076023), V14 = c(264.763713, 255.980769, 262.22807), 
    V15 = c(281.383966, 255.980769, 269.672515), V16 = c(274.626582, 
    281.442308, 271.836257), V17 = c(121.006329, 115.336538, 
    122.830409), V18 = c(30.763713, 30.826923, 31), V19 = c(9281.014768, 
    8928.057692, 9269.432749), V20 = c(267.883966, 280.5, 274.900585
    ), V21 = c(-119.333333, -90.048077, -113.754386), V22 = c(387.2173, 
    370.548077, 388.654971), V23 = c(28.236287, -0.096154, 34.888889
    ), V24 = c(-37.721519, 205.201923, -13.134503)), .Names = c("V1", 
"V2", "V3", "V4", "V5", "V6", "V7", "V8", "V9", "V10", "V11", 
"V12", "V13", "V14", "V15", "V16", "V17", "V18", "V19", "V20", 
"V21", "V22", "V23", "V24"), row.names = c(NA, 3L), class = "data.frame")

dput(B)

structure(list(V1 = structure(1:19, .Label = c("bio_1.tif", "bio_10.tif", 
"bio_11.tif", "bio_12.tif", "bio_13.tif", "bio_14.tif", "bio_15.tif", 
"bio_16.tif", "bio_17.tif", "bio_18.tif", "bio_19.tif", "bio_2.tif", 
"bio_3.tif", "bio_4.tif", "bio_5.tif", "bio_6.tif", "bio_7.tif", 
"bio_8.tif", "bio_9.tif"), class = "factor")), .Names = "V1", class = "data.frame", row.names = c(NA, 
-19L))

Problems:

I am not getting the correct list after removing .tif.
Not sure how to assign them to dataframe A

zx8754 · Accepted Answer · 2016-10-24T20:58:20.310

1

Maybe try this:

colnames(A) <- c("ID", "gauge", "lat", "lon", "area", tools::file_path_sans_ext(B$V1))

Here is dplyr way:

A <- 
  A %>% rename_(.dots = setNames(names(.), c("ID", "gauge","lat","lon","area",
                                             tools::file_path_sans_ext(B$V1))))

edited Oct 24 '16 at 20:58

answered Oct 24 '16 at 20:45

zx8754

52,746
12
114
209

score 0 · Answer 2 · answered Oct 24 '16 at 20:54

A simple base R solution:

setNames(
     A,
     c(
      c("ID", "gauge", "lat", "lon", "area"),
      gsub("\\.tif", "", unlist(B))
      )
  )

ID   gauge       lat      lon    area    bio_1   bio_10    bio_11   bio_12 1  1 1094000 -71.50667
    42.85750 442.8880 73.80591 190.1983 -50.63502 1136.152 2  2 1100600 -71.21528 42.56806  94.5346 91.64423 205.2019 -26.25962
1118.827 3  3 1096000 -71.65833 42.63417 170.6802 80.29240 196.8012 -43.94152
    1112.158
        bio_13   bio_14   bio_15   bio_16   bio_17   bio_18   bio_19    bio_2 1 113.5633 84.99578 7.845992 311.6034 264.7637 281.3840 274.6266
    121.0063 2 114.5577 84.10577 8.846154 308.8173 255.9808 255.9808 281.4423 115.3365 3 112.5380 84.32748 8.000000 307.0760 262.2281 269.6725 271.8363 122.8304
         bio_3    bio_4    bio_5      bio_6    bio_7     bio_8     bio_9 1 30.76371 9281.015 267.8840 -119.33333 387.2173 28.236287
-37.72152 2 30.82692 8928.058 280.5000  -90.04808 370.5481 -0.096154 205.20192 3 31.00000 9269.433 274.9006 -113.75439 388.6550 34.888889 -13.13450

score 0 · Answer 3 · answered Oct 24 '16 at 20:56

0

At a very basic level (and removing the factor in B)

Bvalue <- as.character(B$V1)
Bshort <- substring(Bvalue, 1, nchar(Bvalue)-4)
names(A)[6:24] <- Bshort

answered Oct 24 '16 at 20:56

Henry

6,704
2
23
39

How can I remove the file extensions from file Names and assign them to column names?

3 Answers3