1

I want to do this using data tables in R.

So I start with this 

     dtMain
           Name      state
    1: CompanyC         CA
    2: CompanyM         MN
    3: CompanyC1 California
    4: CompanyT         TX

     statesFile
       stateExpan state
    1:      Texas    TX
    2:  Minnesota    MN
    3: California    CA

Where dtMain$State == statesFile$state, I want to replace dtMain$State with statesFile$stateExpan and get this

      dtMain
           Name      state
    1: CompanyA California
    2: CompanyB  Minnesota
    3: CompanyC California
    4: CompanyD      Texas

Here's code to create the 2 files

library(data.table)
dtMain <- data.table(Name  = c("CompanyA"  ,"CompanyB","CompanyC","CompanyD"),
                 state = c("CA","MN","California","TX"))
statesFile <- data.table( stateExpan = c("Texas","Minnesota","California"),
                          state = c("TX","MN","CA"))

My problem is the next level of this one R finding rows of a data frame where certain columns match those of another and I am looking for a data table solution.

Community
  • 1
  • 1
DashingQuark
  • 105
  • 8
  • 1
    You should use words to explain the rules for the transformation, not simply show input and output – Frank Mar 03 '17 at 19:47
  • @ytk Not a dupe of that, I think, since this is about modifying dtMain, not merging to create a new table. – Frank Mar 03 '17 at 19:48
  • 1
    Anyway, the answer is `dtMain[statesFile, on=.(state), state := i.stateExpan ]` I'll see if I can find a more suitable dupe. You could also have a look at the user-written examples for this: http://stackoverflow.com/documentation/data.table/4976/joins-and-merges/17560 – Frank Mar 03 '17 at 19:49
  • Thanks this answers my question. I was using dtMain[statesFile$state, state := statesFile$stateExpan] which works but destroys the original dt order. – DashingQuark Mar 03 '17 at 19:56
  • 1
    Yeah, generally best to avoid $ with data.tables, where it's not needed and can create confusion. – Frank Mar 03 '17 at 20:01

1 Answers1

3

Use an update join:

dtMain[statesFile, on=.(state), state := i.stateExpan ]

The i.* prefix indicates that it's from the i table in x[i, on=, j]. It is optional here.

See ?data.table for details.

Graham
  • 7,431
  • 18
  • 59
  • 84
Frank
  • 66,179
  • 8
  • 96
  • 180