Reshape dataframe in R using values in 1 column as column names in new dataframe

user_5573294 0
Viewed 45

I think this is a pretty straight-forward reshaping, although we are struggling. We have this dataframe:

structure(list(X1 = c("Title:", "Phone:", "Email:", "Previous College:"
), X2 = c("Head Coach, Year2", "123-456-7890", "name@email.com", 
          "The Best")), class = "data.frame", row.names = c(NA, 
                                                                     -4L))

                 X1                X2
1            Title: Head Coach, Year2
2            Phone:      123-456-7890
3            Email:    name@email.com
4 Previous College:          The Best

and we're trying to convert it into a 1-row dataframe like such:

data.frame(Title = 'Head Coach, Year2', Phone = '123-456-7890', Email = 'name@email.com', `Previous College` = 'The Best', stringsAsFactors = FALSE)

              Title        Phone          Email Previous.College
1 Head Coach, Year2 123-456-7890 name@email.com         The Best
data-manipulation r
3 Answers

We can deframe and convert to a tibble

library(dplyr)
library(stringr)
library(tibble)
df1 %>%
    mutate(X1 = str_remove(X1, ":")) %>%
    deframe %>%
    as_tibble_row
# A tibble: 1 x 4
  Title             Phone        Email          `Previous College`
  <chr>             <chr>        <chr>          <chr>             
1 Head Coach, Year2 123-456-7890 name@email.com The Best

or with base R by setting the names with the 'X1' column and use as.data.frame.list to convert the named vector to data.frame

as.data.frame.list(with(df1, setNames(X2, trimws(X1, 
        whitespace = ":"))), check.names = FALSE)
              Title        Phone          Email Previous College
1 Head Coach, Year2 123-456-7890 name@email.com         The Best

Or with data.table::transpose

 data.table::transpose(df1, make.names = 'X1')
             Title:       Phone:         Email: Previous College:
1 Head Coach, Year2 123-456-7890 name@email.com          The Best
user_37322710

You could also do:

read.dcf(textConnection(do.call(paste, df)), all = TRUE)
              Title        Phone          Email Previous College
1 Head Coach, Year2 123-456-7890 name@email.com         The Best
user_83802720

Another approach using data.table -

library(data.table)

dcast(setDT(df), rowid(X1)~sub(':', '', X1, fixed = TRUE), value.var = 'X2')

#   X1          Email        Phone Previous College             Title
#1:  1 name@email.com 123-456-7890         The Best Head Coach, Year2
user_39629140
Related