CODEWITHSUNDEEP
r
user3022875
user3022875

Reputation: 9018

Pivot columns in Data Frame

I have the data frame below:

data<-data.frame(names= c("Bob","Bob", "Fred","Fred","Tom"), id =c(1,1,2,2,3),amount = c(100,200,400,500,700), status = c("Active","Not Active","Active","Retired","Active"))
data

 names id amount     status
1   Bob  1    100     Active
2   Bob  1    200 Not Active
3  Fred  2    400     Active
4  Fred  2    500    Retired
5   Tom  3    700     Active

I would like to Pivot the "Status" column so the "amount" data appears under the new status columns so that the result looks like this:

names     id    Active    Not Active  Retired
Bob       1      100         200
Fred      2      400                   500
Tom       3      700

Is this possible? What is the best way?

Upvotes: 2

Views: 95

Answers (4)

akrun
akrun

Reputation: 886938

Here is another base R option

 reshape(data, idvar=c('names', 'id'), timevar='status', direction='wide')
 #  names id amount.Active amount.Not Active amount.Retired
 #1   Bob  1           100               200             NA
 #3  Fred  2           400                NA            500
 #5   Tom  3           700                NA             NA

Upvotes: 0

IRTFM
IRTFM

Reputation: 263301

This would be the base method:

> xtabs(amount~names+status, data=data)
      status
names  Active Not Active Retired
  Bob     100        200       0
  Fred    400          0     500
  Tom     700          0       0

Upvotes: 1

hrbrmstr
hrbrmstr

Reputation: 78792

I am now compelled to turn a comment into an answer. Here's the Hadleyverse version:

library(tidyr)
spread(data, status, amount)

##   names id Active Not Active Retired
## 1   Bob  1    100        200      NA
## 2  Fred  2    400         NA     500
## 3   Tom  3    700         NA      NA

Upvotes: 2

bdemarest
bdemarest

Reputation: 14667

Here is a solution using dcast from the package reshape2:

library(reshape2)

dcast(data, names + id ~ status, value.var="amount")

#   names id Active Not Active Retired
# 1   Bob  1    100        200      NA
# 2  Fred  2    400         NA     500
# 3   Tom  3    700         NA      NA

Upvotes: 1

Related Questions