R data.table column that counts along unique values in other column

Question

I have some data in long format that I want to make wide. The issue is that the data is in a format doesn't have one piece of information that makes spreading easy. To get around this, I have to create a column in the data frame that counts from 1 to the length of each unique entry in another column.

In the process below, is there a data.table way of making the column "fid"?

library(data.table)
library(tidyverse)

# data:
df <- data.frame(class = c('1', '1', '1', '2', '3', '3'),
                 A = 1:6,
                 B = 11:16)

# create counting column
df <- df %>% group_by(class) %>% mutate(fid=1:n())

# spread using dcast
dcast(setDT(df), class ~ fid, value.var = c("A", "B"))

Cheers

Yes, `rowid`. use `class ~ rowid(class)` in `dcast`. – mt1022 Apr 13 '18 at 03:09 — mt1022, Apr 13 '18 at 03:09

score 5 · Accepted Answer · answered Apr 13 '18 at 03:10

5

Another way is using data.table::rowid.

df[,fid := rowid(class)]

answered Apr 13 '18 at 03:10

hpesoj626

3,529
1
17
25

score 1 · Answer 2 · answered Apr 13 '18 at 03:08

The data.table way of creating the column 'fid', is to convert the data.frame into data.table (setDT(df)), grouped by 'class', get the sequence of rows (seq_len(.N)) and assign (:=) it to 'fid'

setDT(df)[, fid := seq_len(.N), class]

However, creating a column is not at all needed if the intention is to use dcast as there is a rowid function which can directly be used in the formula as @mt1022 suggested

R data.table column that counts along unique values in other column

2 Answers2