CODEWITHSUNDEEP
rlistdataframe
Mayo Racek MBA
Mayo Racek MBA

Reputation: 75

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

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.

This is the output [![enter image description here][1]][1]

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]])```

Upvotes: 0

Views: 871

Answers (1)

MrGumble
MrGumble

Reputation: 5776

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.

Upvotes: 2

Related Questions