Reshape data frame by row

Question

I have a data frame similar to the following example:

> df <- data.frame(imp = c("Johny", "Johny", "Lisa", "Max"), item = c(5025, 1101, 2057, 1619))
> df
     imp     item  
[1,] "Johny" "5025"
[2,] "Johny" "1101"
[3,] "Lisa"  "2057"
[4,] "Max"   "1619"

I would like to have an unique row for each user. The final result should be something like this:

> df
     imp     item1  item2 
[1,] "Johny" "5025" "1101"
[2,] "Lisa"  "2057" NA    
[3,] "Max"   "1619" NA

Note that 'df' in your example needs data.frame() to be a data.frame. Also, how many items can each person have? Up to 2, or many? — Kevin Wright, Apr 08 '14 at 18:01
You are right Kevin! thanks for the edit. Items can be repeated, so you can see more than one time the same item. I would like the number of columns not to be fixed, so if Johny has 4 items we will have item1, item2, item3, item4. — Gianluca, Apr 08 '14 at 18:05

score 3 · Accepted Answer · answered Apr 08 '14 at 18:32

## Add an ID column to distinguish multiple measurements per imp
## There's probably a better way to do this?
df <- do.call(rbind, lapply(
    split(df, df$imp),
    function(x) {
        x$item_id <- seq(nrow(x))
        return(x)
    }
))

## Then simply use the dcast function from the reshape2 package
df <- dcast(df, imp ~ item_id, value.var='item')

## Tidy up the column names
names(df) <- sub('^(\\d+)$', 'item_\\1', names(df))

score 3 · Answer 2 · edited Oct 17 '18 at 12:53

Using data.table v 1.9.6+ we can pass expressions directly to formula. Please see ?dcast for more and also the examples section.

require(data.table) # v1.9.6+
dcast(setDT(df), imp ~ paste0("item", 
       df[, seq_len(.N), by=imp]$V1), value.var="item")

#      imp item1 item2
# 1: Johny  5025  1101
# 2:  Lisa  2057    NA
# 3:   Max  1619    NA

Edit:

Using data.table v1.9.8+ you could simply do

require(data.table) # v1.9.8+
dcast(setDT(df), imp ~ rowid(imp, prefix = "item"), value.var = "item")

Christian Borck · Answer 3 · 2014-04-08T20:39:39.140

1

what about this approach using data.table:

require(data.table)
dt <- data.table(imp = c("Johny", "Johny", "Lisa", "Max"),
                 item = c(5025, 1101, 2057, 1619))

dt[, list(items = list(unique(item))), by=imp]

# to keep all items, not only uniques
dt[, list(items = list(item)), by=imp]

this provides you with a list of "items" for every "imp"...

edited Apr 08 '14 at 20:39

answered Apr 08 '14 at 18:27

Christian Borck

1,812
1
13
19

score 1 · Answer 4 · answered Apr 11 '14 at 06:06

Here's a way to do it in base R with ave (to create your "time" variable) and reshape (to go from "long" to "wide"):

df$times <- ave(rep(1, nrow(df)), df$imp, FUN = seq_along)
df
#     imp item times
# 1 Johny 5025     1
# 2 Johny 1101     2
# 3  Lisa 2057     1
# 4   Max 1619     1
reshape(df, direction = "wide", idvar="imp", timevar="times")
#     imp item.1 item.2
# 1 Johny   5025   1101
# 3  Lisa   2057     NA
# 4   Max   1619     NA

Reshape data frame by row

4 Answers4

Linked

Related