data.table substring on column

Question

Given this data.table

DT = data.table(item=c("item1 - description one", "item2 - description two", "item3 - description three"), sales=1:3)

DT
                        item sales
1:   item1 - description one     1
2:   item2 - description two     2
3: item3 - description three     3

How can I easily get output that looks like

      code sales
1:   item1     1
2:   item2     2
3:   item3     3

Probably very easy but thanks in advance.

Split on space, then keep the 1st value. Something like: `dt1$item_clean <- sapply(strsplit(dt1$item, " "), function(i)i[1])` — zx8754, Jun 23 '17 at 14:27
`data.table(code = substr(DT[[1]], 1, regexpr(' ', DT[[1]])-1), sales = DT[[2]])` — Majo, Jun 23 '17 at 14:37
Thanks to all of you for prompt responses... sorry for duplicate. — Martyn, Jun 23 '17 at 15:43

score 1 · Accepted Answer · answered Jun 23 '17 at 14:32

1

You can do this:

DT[, item:=gsub(item, pattern=" - [a-zA-Z ]+", replacement="")]
setnames(DT, "item", "code")

#    code sales
#1: item1     1
#2: item2     2
#3: item3     3

answered Jun 23 '17 at 14:32

Rafael Toledo

974
13
19

Thanks Rafael for your prompt response. Worked like a charm. – Martyn Jun 23 '17 at 15:44

data.table substring on column

1 Answers1