Extract data.frame column names from rows in data.frame

Question

I have a couple of data.frames which have approximately the same structure. For a reproducible example I created two sample dataframes df1 and df2.

df1 <- structure(list(sample = c(2L, 6L), data1 = c(56L, 78L), data2 = c(59L, 
27L), data6 = c(90L, 28L), data1namet = structure(c(1L, 1L), .Label = "Sam1", class = "factor"), 
    data2namab = structure(c(1L, 1L), .Label = "Test2", class = "factor"), 
    dataame = structure(c(1L, 1L), .Label = "Ex3", class = "factor")), .Names = c("sample", 
"data1", "data2", "data3", "data1namet", "data2namab", "dataame"
), class = "data.frame", row.names = c(NA, -2L))

df1
  sample data1 data2 data3 data1namet data2namab dataame
1      2    56    59    90      Sam1     Test2       Ex3
2      6    78    27    28      Sam1     Test2       Ex3

df2 <- structure(list(sample = c(12L, 13L, 17L), data1 = c(56L, 78L, 
3L), data2 = c(59L, 27L, 2L), datest = structure(c(1L, 1L, 
1L), .Label = "Exa9", class = "factor"), dattestr = structure(c(1L, 
1L, 1L), .Label = "cz1", class = "factor")), .Names = c("sample", 
"data1", "data2", "datest", "dattestr"), class = "data.frame", row.names = c(NA, 
-3L))

df2
  sample data1 data2 datest dattestr
1     12   56   59      Exa9       cz1
2     13   78   27      Exa9       cz1
3     17    3    2      Exa9       cz1

The name of the data is saved in the columns after the data columns and I was wondering if there is a way I could restructure the data.frames (about 40 data.frames) that they contain the name of the data in their column name?

df1
  sample Sam1 Test2 Ex3
1      2   56    59  90
2      6   78    27  28

and

df2
  sample Exa9 cz1
1     12   56  59
2     13   78  27
3     17    3   2

EDIT

As I just realised I also have other columns after the data columns so that my input data looks like this

df1 <- structure(list(sample = c(2L, 6L), data1 = c(56L, 78L), data2 = c(59L, 
27L), data3 = c(90L, 28L), data1namet = structure(c(1L, 1L), .Label = "Sam1", class = "factor"), 
    data2namab = structure(c(1L, 1L), .Label = "Test2", class = "factor"), 
    dataame = structure(c(1L, 1L), .Label = "Ex3", class = "factor"), 
    ma = c("Jay", "Jay")), .Names = c("sample", "data1", "data2", 
"data3", "data1namet", "data2namab", "dataame", "ma"), row.names = c(NA, 
-2L), class = "data.frame")

df1
sample data1 data2 data3 data1namet data2namab dataame  ma
1      2    56    59    90       Sam1      Test2     Ex3 Jay
2      6    78    27    28       Sam1      Test2     Ex3 Jay

df2 <- structure(list(sample = c(12L, 13L, 17L), data1 = c(56L, 78L, 
3L), data2 = c(59L, 27L, 2L), datest = structure(c(1L, 1L, 1L
), .Label = "Exa9", class = "factor"), dattestr = structure(c(1L, 
1L, 1L), .Label = "cz1", class = "factor"), add = c(2, 2, 2)), .Names = c("sample", 
"data1", "data2", "datest", "dattestr", "add"), row.names = c(NA, 
-3L), class = "data.frame")

df2
sample data1 data2 datest dattestr add
1     12    56    59   Exa9      cz1   2
2     13    78    27   Exa9      cz1   2
3     17     3     2   Exa9      cz1   2

In this case the ma and add column are not part of the data and should be added at the end like this:

df1
  sample Sam1 Test2 Ex3 ma
1      2   56    59  90 Jay
2      6   78    27  28 Jay

and

df2
  sample Exa9 cz1 add
1     12   56  59 2
2     13   78  27 2
3     17    3   2 2

RHertel · Accepted Answer

One could start by identifying which columns should be kept:

keep_col <- which(sapply(df2, is.numeric))

After that, some work is required to extract the new column names and to rename the corresponding columns in the data frame:

names <- df2[1,keep_col[-1] + length(keep_col)-1]
colnames(df2)[keep_col[-1]] <- as.character(unlist(names))

Finally, the dataframe can be reassembled by keeping only the desired columns:

df2 <- df2[,keep_col]
#> df2
#  sample Exa9 cz1
#1     12   56  59
#2     13   78  27
#3     17    3   2

In order to use this transformation for several different dataframes, the code can be wrapped into a function:

summarize_table <- function(x){
 keep_col <- which(sapply(x, is.numeric))
 names <- x[1,keep_col[-1] + length(keep_col)-1] 
 colnames(x)[keep_col[-1]] <- as.character(unlist(names))
 x <- x[,keep_col]
}

If the various dataframes are stored in a list, the function summarize_table() can be used with lapply() to obtain the results for each dataframe:

my_dfs <- list(df1,df2)
out <- lapply(my_dfs,summarize_table)
#> out
#[[1]]
#  sample Sam1 Test2 Ex3
#1      2   56    59  90
#2      6   78    27  28
#
#[[2]]
#  sample Exa9 cz1
#1     12   56  59
#2     13   78  27
#3     17    3   2

EDIT / ADDENDUM

The modified version below should be able to handle also the cases mentioned in the revised post:

summarize_tab2 <- function(x){
 keep_col <- which(sapply(x, is.numeric))
 first_block <- c(keep_col[1],keep_col[which(diff(keep_col)==1)])
 add_col <- FALSE
 if (2 * (length(keep_col) - 1) + 1 < ncol(x)) add_col <- TRUE
 keep_col1 <- keep_col[1:length(first_block)]
 names <- x[1,keep_col1[-1] + length(keep_col1) - 1] 
 colnames(x)[keep_col1[-1]] <- as.character(unlist(names))
 df_t <- x[,keep_col]
 if (add_col) df_t <- cbind(df_t, x[(2 * (ncol(df_t) - 1) + 2):ncol(x)])
 return(df_t)
}
my_dfs <- list(df1, df2, df3, df4)
out <- lapply(my_dfs, summarize_tab2)
#> out
#[[1]]
#  sample Sam1 Test2 Ex3  ma
#1      2   56    59  90 Jay
#2      6   78    27  28 Jay
#
#[[2]]
#  sample Exa9 cz1 add
#1     12   56  59   2
#2     13   78  27   2
#3     17    3   2   2
#
#[[3]]
#  sample Sam1 Test2 Ex3
#1      2   56    59  90
#2      6   78    27  28
#
#[[4]]
#  sample Exa9 cz1
#1     12   56  59
#2     13   78  27
#3     17    3   2

Here the dataframes df3 and df4 are, respectively, the data frames df1and df2 of the original post.

Extract data.frame column names from rows in data.frame

Answers (2)

Related Questions