CODEWITHSUNDEEP
rtidyverse
user330
user330

Reputation: 1270

Reshaping data and generating IDs with column in R

I have the following data:

DF <- read.table(header = TRUE, text =
"126    D1  181 N1  225 P1

A   2   B   9   A   11
D   3   B   12  A   11
"
)

I want to get the following table, but I struggle to it

DF <- read.table(header = TRUE, text =
"ID Class   D1  Time    N1
126 A   2   D   3
181 B   9   B   12
225 A   11  A   11"

)

!26,181 and 225 get the ID column and based on that the table is completed. Can we do it in R

Upvotes: 0

Views: 47

Answers (2)

ThomasIsCoding
ThomasIsCoding

Reputation: 101109

Hope the base R option as below works for your purpose

type.convert(
  do.call(
    rbind,
    lapply(
      split.default(DF, cumsum(grepl("X\\d+", names(DF)))),
      function(x) data.frame(t(c(head(names(x), 1), t(x))))
    )
  ),
  as.is = TRUE
)

which gives

    X1 X2 X3 X4 X5
1 X126  A  2  D  3
2 X181  B  9  B 12
3 X225  A 11  A 11

Upvotes: 1

r2evans
r2evans

Reputation: 160407

I'm starting with headers turned off, since it's easier to deal with static/known headers and keeping data as data.

DF <- read.table(header = FALSE, text =
"126    D1  181 N1  225 P1
A   2   B   9   A   11
D   3   B   12  A   11")

Then the work:

library(dplyr)
library(purrr) # map, transpose

split.default(DF, (seq_len(ncol(DF)) - 1) %/% 2) %>%
  map(., setNames, c("V1", "V2")) %>%
  lapply(., function(x) c(unlist(t(x)))) %>%
  bind_rows() %>%
  transpose()
#    V1 V2 V3 V4 V5 V6
# 1 126 D1  A  2  D  3
# 2 181 N1  B  9  B 12
# 3 225 P1  A 11  A 11

And you can name them (and omit columns) as needed.

Upvotes: 0

Related Questions