Convert single column dataframe to dataframe with multiple rows and named columns

Question

dfOrig <- data.frame(rbind("1",
                       "C",
                       "531404",
                       "3",
                       "B",
                       "477644"))
setnames(dfOrig, "Value")

I have a single column vector, which actually comprises two observations of three variables. How do I convert it to a data.frame with the following structure:

ID      Code     Tag
"1"     "C"      "531404"
"3"     "B"      "477644"

Obviously, this is just a toy example to illustrate a real-world problem with many more observations and variables.

So you will have to create more columns than just ID, Code, and Tag? — Sotos
– Sotos, Commented Sep 13, 2016 at 8:12

talat · Accepted Answer · 2016-09-13 09:18:49Z

4

Here's another approach - it does rely on the dfOrig column being ordered 1,2,3,1,2,3 etc.

x <- c("ID", "Code", "Tag")    # new column names
n <- length(x)                 # number of columns
res <- data.frame(lapply(split(as.character(dfOrig$Value), rep(x, nrow(dfOrig)/n)), 
         type.convert))

The resulting data is:

> str(res)
#'data.frame':  2 obs. of  3 variables:
# $ Code: Factor w/ 2 levels "B","C": 2 1
# $ ID  : int  1 3
# $ Tag : int  531404 477644

As you can see, the column classes have been converted. In case you want the Code column to be character instead of factor you can specify stringsAsFactors = FALSE in the data.frame call.

And it looks like this:

> res
#  Code ID    Tag
#1    C  1 531404
#2    B  3 477644

Note: You have to get the column name order in x in line with the order of the entries in dfOrig$Value.

If you want to get the column order of res as specified in x, you can use the following:

res <- res[, match(x, names(res))]

edited Sep 13, 2016 at 9:18

answered Sep 13, 2016 at 8:11

talat

70.5k22 gold badges130 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

matekus Over a year ago

Excellent, Is there a way to add the column headers (ID, Code, Tag) at the same time?

cccmir Over a year ago

use colnames(df) <- c("ID", "Code","Tag")

matekus Over a year ago

Columns sorted into alphabetical order (Code, ID, Tag) - need original (ID, Code, Tag)?

talat Over a year ago

@matekus, you can use res[, match(x, names(res))] or whatever logic / order you like

zx8754 · Accepted Answer · 2016-09-13 09:44:59Z

1

Maybe convert to matrix with nrow:

# set number of columns
myNcol <- 3

# convert to matrix, then dataframe
res <- data.frame(matrix(dfOrig$Value, ncol = myNcol, byrow = TRUE),
                  stringsAsFactors = FALSE)

# convert the type and add column names
res <- as.data.frame(lapply(res, type.convert),
                     col.names = c("resID", "Code", "Tag"))
res
#   resID Code    Tag
# 1     1    C 531404
# 2     3    B 477644

edited Sep 13, 2016 at 9:44

answered Sep 13, 2016 at 8:26

zx8754

56.7k12 gold badges131 silver badges229 bronze badges

Comments

Ronak Shah · Accepted Answer · 2016-09-13 09:00:43Z

0

You can create a sequence of numbers

x <- seq(1:nrow(dfOrig)) %% 3  #you can change this 3 to number of columns you need

data.frame(ID = dfOrig$Value[x == 1], 
           Code = dfOrig$Value[x == 2], 
           Tag = dfOrig$Value[x == 0])


#ID Code    Tag
#1  1    C 531404
#2  3    B 477644

Another approach would be splitting the dataframe according to the sequence generated above and then binding the columns using do.call

x <- seq(1:nrow(dfOrig))%%3
res <- do.call("cbind", split(dfOrig,x))

You can definitely change the column names

colnames(res) <- c("Tag", "Id", "Code")

#  Tag       Id  Code
#3 531404     1     C
#6 477644     3     B

edited Sep 13, 2016 at 9:00

answered Sep 13, 2016 at 8:44

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Collectives™ on Stack Overflow

Convert single column dataframe to dataframe with multiple rows and named columns

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related