CODEWITHSUNDEEP
r
Tawk Tomahawk
Tawk Tomahawk

Reputation: 3

Re-arrange data so a single cell is header

I am looking to re-arrange my data. Currently it looks like data 1 and I would like for it to look like data2. Essentially, I would like to move 'total' so that it is its own column, and I'd like to move its n along with it. I am using R. Thank you. 

data1 <- data.frame (
  question = c("recommend", "recommend", "overall", "overall"),
  response = c("top box score", "total", "top box score", "total"),
  n = c(673, 784, 654, 784))


data2 <- data.frame (
  question = c("recommend", "overall"),
  response = c("top box score", "top box score"),
  n = c(673, 654),
  total = c(784, 784))

Upvotes: 0

Views: 41

Answers (2)

B. Christian Kamgang
B. Christian Kamgang

Reputation: 6489

You can use data.table as follows:

library(data.table)
data2 <- setDT(data1)[response != "total"][data1, total := i.n, on = "question"]

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 388982

One way would be to filter data for "total" rows, get them in wide format and join to the original data without "total" rows. 

library(dplyr)
library(tidyr)

data1 %>% 
  filter(response != 'total') %>%
  left_join(data1 %>%
          filter(response == 'total') %>%
          pivot_wider(names_from = response, values_from = n), by = 'question')

#   question      response   n total
#1 recommend top box score 673   784
#2   overall top box score 654   784

Upvotes: 0

Related Questions