Transpose multiple columns as column names and fill with values in R

Question

The sample data as following:

x <- read.table(header=T, text="
ID CostType1 Cost1 CostType2 Cost2
                1 a 10 c 1
                2 b 2  c 20
                3 a 1  b 50
                4 a 40 c 1
                5 c 2  b 30
                6 a 60 c 3
                7 c 10 d 1 
                8 a 20 d 2")

I want the second and third columns (CostType1 and CostType 2) to be the the names of new columns and fill the corresponding cost to certain cost type. If there's no match, filled with NA. The ideal format will be following:

          a  b  c  d
        1 10 NA 1  NA
        2 NA 2  20 NA
        3 1  50 NA NA
        4 40 1  NA NA
        5 NA 30 2  NA 
        6 60 NA 3  NA
        7 NA NA 10 1
        8 20 NA NA 2

check out https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format — chinsoon12, Feb 14 '18 at 01:36

score 1 · Answer 1 · answered Feb 14 '18 at 01:49

A solution using tidyverse. We can first get how many groups are there. In this example, there are two groups. We can convert each group, combine them, and then summarize the data frame with the first non-NA value in the column.

library(tidyverse)

# Get the group numbers
g <- (ncol(x) - 1)/2

x2 <- map_dfr(1:g, function(i){
  # Transform the data frame one group at a time
  x <- x %>%
    select(ID, ends_with(as.character(i))) %>%
    spread(paste0("CostType", i), paste0("Cost", i))
  return(x)
  }) %>% 
  group_by(ID) %>%
  # Select the first non-NA value if there are multiple values
  summarise_all(funs(first(.[!is.na(.)])))
x2
# # A tibble: 8 x 5
#      ID     a     b     c     d
#   <int> <int> <int> <int> <int>
# 1     1    10    NA     1    NA
# 2     2    NA     2    20    NA
# 3     3     1    50    NA    NA
# 4     4    40    NA     1    NA
# 5     5    NA    30     2    NA
# 6     6    60    NA     3    NA
# 7     7    NA    NA    10     1
# 8     8    20    NA    NA     2

score 1 · Answer 2 · answered Feb 14 '18 at 01:52

A base solution using reshape

x1 <- setNames(x[,c("ID", "CostType1", "Cost1")], c("ID", "CostType", "Cost"))
x2 <- setNames(x[,c("ID", "CostType2", "Cost2")], c("ID", "CostType", "Cost"))

reshape(data=rbind(x1, x2), idvar="ID", timevar="CostType", v.names="Cost", direction="wide")

Transpose multiple columns as column names and fill with values in R

2 Answers2