CODEWITHSUNDEEP
r
CBechet
CBechet

Reputation: 171

How to change levels of a columns as new rows in a data frame without losing values of another column?

I wish to set levels of a column as new rows in a data frame. The problem is that a value is assigned to these levels in another column and I don't want them to be lost. It appears that some rows have the same name for two levels, so I would like to keep the to levels as columns and get a unique row for previous duplicates. Here comes my example.

> shall.will.table<-read.table(choose.files(), header=T, sep="\t", comment.char="") # loads 1000 observations of 3 variables
> attach(shall.will.table)
> head(shall.will.table)
     CX   VERB FREQ
1 shall     be 4414
2 shall   have 1354
3 shall    see 1131
4 shall     go  521
5 shall     do  482
6 shall   take  356

> ordering.index<-order(VERB, CX)
> shall.will.table.2<-shall.will.table[ordering.index,]
> head(shall.will.table.2)
       CX        VERB FREQ
912  will     abandon   43
384 shall       abide    4
896  will     abolish   47
104 shall      accept   24
565  will      accept  524
171 shall   accompany   14

The output I would like to get should look like the following table, but without going through the following steps:

> VERB<-c("abandon", "abide", "abolish", "accept", "accompany")
> shall<-c(0, 4, 0, 24, 14)
> will<-c(43, 0, 47, 524, 0)
> shall.will.table.3<-data.frame(VERB, shall, will)
> shall.will.table.3
       VERB shall will
1   abandon     0   43
2     abide     4    0
3   abolish     0   47
4    accept    24  524
5 accompany    14    0

Does anyone can help me?

Thanking you in advance.

Upvotes: 1

Views: 53

Answers (2)

jMathew
jMathew

Reputation: 1057

Or you can use the reshape2 package,

require(reshape2)
dcast(shall.will.table.2, VERB~CX, value.var = "FREQ", fill=0)

It is probably better to not assign the fill argument, unless you have an explicit reason.

Upvotes: 1

jeremycg
jeremycg

Reputation: 24945

You can try using the package tidyr, and the function spread. this will convert your long data to wide data:

library(tidyr)
shall.will.table %>% spread(CX, FREQ, fill = 0)

       VERB shall will
1   abandon     0   43
2     abide     4    0
3   abolish     0   47
4    accept    24  524
5 accompany    14    0

Upvotes: 0

Related Questions