How to set rownames as column in the list of dataframes?

Question

I have list of dataframes (List of 4) in global environment for match data called match_list. I wanted to transpose the list with match_list <- lapply(match_list,t) so it makes metrics as colnames.

It transposed the dataframe in each list but it also made previous colnames as rownames. Could you please helped me how to get rid off the rownames? And make it as a standard column?

I am then performing for loop

#### create individual dataframes ----
for (i in seq(match_list))
  assign(paste0(file_names[[i]]), match_list[[i]])

that creates separate dataframes in my global environment

I have tried several ways, but still cannot turn my head around to make it work.

My attempt:

match_list <- lapply(match_list,function(x){
  
  colnames(x) <- rownames_to_column(match_list[[1]]) 
  
})

This is my whole script:

match_xlsx <- as_tibble(list.files("C:/Users/User/Desktop/asJohan", pattern = ".xlsx", full.names = TRUE, all.files = FALSE))
file_names <- list.files("C:/Users/User/Desktop/asJohan", pattern = ".xlsx", full.names = TRUE, all.files = FALSE)


#### read specific sheet from excel ---- create two lists and merge them
match_list <- lapply(match_xlsx$value, read_excel, sheet = 1, range = "B1:N40")
match_list <- lapply(match_list,t) 
#match_list <- lapply(match_list, function(x) {x <- x[-1,-1 ]}) #### remove first column and row




###set first row as a header - column names
match_list <- lapply(match_list, function(x){
colnames(x) <- x[1,]
x[-(1:3),]})

####set rowname as column name

match_list <- lapply(match_list, rownames_to_column, var='former_transposed_colnames')


#### create individual dataframes ----
for (i in seq(match_list))
assign(paste0(file_names[[i]]), match_list[[i]])```

MrGumble MrGumble · Accepted Answer · 2021-02-25T14:03:37

The function rownames_to_column (if the one from the tibble-package), returns the input data.frame, in this case, the one in match_list[[1]]. Instead, you are trying to push the entire data.frame into the vector of colnames (the colnames(x) <--portion).

Instead, you can simply use

match_list <- lapply(match_list, rownames_to_column)

and the default behaviour of rownames_to_column will assign the rownames to a column named rownames. You can change this with e.g.

match_list <- lapply(match_list, rownames_to_column, var='former_transposed_colnames')

Edit:

You can convert your list back to data.frames with... lapply:

match_list <- lapply(match_list,t)
match_list <- lapply(match_list,as.data.frame)
match_list <- lapply(match_list, rownames_to_column)

# or if you load dplyr 
library(dplyr)
match_list <- lapply(match_list, t) %>%
  lapply(as.data.frame) %>%
  lapply(rownames_to_column)

But rather than use t to transpose (as it requires all entries having the same data.type, i.e. everything becomes character because a column was a string, try using tidyr's pivot_longer and pivot_wider. This will however require you have an id-column (prior to pivot_longer), but I suspect you might already have one in the first column (in column 'A', which you at some point remove). If you do not, you can create a dummy id-column by simply numbering the rows. When you are done, remove the dummy values.

How to set rownames as column in the list of dataframes?

1 Answers