R importing excel sheet, cell a1 becomes row value in new column

user_13300333 0
Viewed 51

I am importing multiple excel sheets into r. Each sheet has a different title in cell a1 above row headers. In r I would like to cut this, promote headers, and then use cut title as row value in a new id column. E.g. I want to turn table a below into table b.

table a

| football |                |
| rank     | team           |
| -------- | -------------- |
| 1        | A              |
| 2        | B              |

table b

| rank     | team           |id         |
| -------- | -------------- |-----------
| 1        | A              |football   |
| 2        | B              |football   |

Can you suggest code for this operation that I can apply in function for every sheet?

tidyverse r excel
2 Answers

We could use row_to_names from janitor

library(openxlsx) %>%
library(dplyr)
library(janitor)
df1 <- read.xlsx("table_a.xlsx") 
df1 %>%
   row_to_names(1) %>%
   mutate(id = colnames(df1)[1])
user_37322710

You could use

library(openxlsx)
library(dplyr)

df <- read.xlsx("table_a.xlsx")

df %>%
  `colnames<-`(., .[1,]) %>% 
  slice(-1) %>% 
  mutate(id = colnames(df)[1])

which returns

  rank team       id
1    1    A football
2    2    B football
user_125052510
Related