CODEWITHSUNDEEP
rdplyr
bmrn
bmrn

Reputation: 500

apply rename_if predicate to column names

I am working with a set of excel spreadsheets which has column names which are dates.

After reading in the data with readxl::read_xlsx(), these column names become excel index dates (i.e. an integer representing days elapsed from 1899-12-30)

Is it possible to used dplyr::rename_if() or similar to rename all column names that are currently integers? I have written a function rename_func that I would like to apply to all such columns.

df %>% rename_if(is.numeric, rename_func) is not suitable as is.numeric is applied to the data in the column not the column name itself. I have also tried:

is.name.numeric <- function(x) is.numeric(names(x))
df %>% rename_if(is.name.numeric, rename_func)

which does not work and does not change any names (i.e. is.name.numeric returns FALSE for all cols)

edit: here is a dummy version of my data

df_badnames <- structure(list(Level = c(1, 2, 3, 3, 3), Title = c("AUSTRALIAN TOTAL", 
"MANAGERS", "Chief Executives, Managing Directors & Legislators", 
"Farmers and Farm Managers", "Hospitality, Retail and Service Managers"
), `38718` = c(213777.89, 20997.52, 501.81, 121.26, 4402.7), 
    `38749` = c(216274.12, 21316.05, 498.1, 119.3, 4468.67), 
    `38777` = c(218563.95, 21671.84, 494.08, 118.03, 4541.02), 
    `38808` = c(220065.05, 22011.76, 488.56, 116.24, 4609.28)), row.names = c(NA, 
-5L), class = c("tbl_df", "tbl", "data.frame"))

and I would like:

df_goodnames <- structure(list(Level = c(1, 2, 3, 3, 3), Title = c("AUSTRALIAN TOTAL", 
"MANAGERS", "Chief Executives, Managing Directors & Legislators", 
"Farmers and Farm Managers", "Hospitality, Retail and Service Managers"
), Jan2006 = c(213777.89, 20997.52, 501.81, 121.26, 4402.7), 
    Feb2006 = c(216274.12, 21316.05, 498.1, 119.3, 4468.67), 
    Mar2006 = c(218563.95, 21671.84, 494.08, 118.03, 4541.02), 
    Apr2006 = c(220065.05, 22011.76, 488.56, 116.24, 4609.28)), row.names = c(NA, 
-5L), class = c("tbl_df", "tbl", "data.frame"))

I understand that it is best practice to create a date column and change the shape of this df, but I need to join a few spreadsheets first and having integer column names causes a lot of problems. I currently have a work around but the crux of my question (apply a rename_if predicate to a name, rather than a column) is still interesting.

Upvotes: 0

Views: 329

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388982

Although, the names look numeric but they are not 

class(names(df_badnames))
#[1] "character"

so they would not be caught by is.numeric or similar other functions.

One way to do this is find out which names can be coerced to numeric and then convert them into the date format of our choice

cols <- as.numeric(names(df_badnames))
names(df_badnames)[!is.na(cols)] <- format(as.Date(cols[!is.na(cols)], 
                                          origin = "1899-12-30"), "%b%Y")

df_badnames

#  Level Title                                           Jan2006 Feb2006 Mar2006 Apr2006
#   <dbl> <chr>                                             <dbl>   <dbl>   <dbl>   <dbl>
#1     1 AUSTRALIAN TOTAL                                213778. 216274. 218564. 220065.
#2     2 MANAGERS                                         20998.  21316.  21672.  22012.
#3     3 Chief Executives, Managing Directors & Legisla…    502.    498.    494.    489.
#4     3 Farmers and Farm Managers                          121.    119.    118.    116.
#5     3 Hospitality, Retail and Service Managers          4403.   4469.   4541.   4609.

Upvotes: 1

Related Questions