CODEWITHSUNDEEP
r
ChinookJargon
ChinookJargon

Reputation: 99

converting an abbreviation into a full word

I am trying to avoid writing a long nested ifelse statement in excel.

I am working on two datasets, one where I have abbreviations and county names.

Abbre
COUNTY_NAME
1   AD  Adams
2   AS  Asotin
3   BE  Benton
4   CH  Chelan
5   CM  Clallam
6   CR  Clark

And another data set that contains the county abbreviation and votes.

  CountyCode Votes
 1 WM            97
 2 AS            14
 3 WM           163
 4 WM           144
 5 SJ            21

For the second table, how do I convert the countycode (abbreviation) into the full spelled-out text and add that as a new column?

I have been trying to solve this unsuccessfully using grep, match, and %in%. Clearly I am missing something and any insight would be greatly appreciated.

Upvotes: 0

Views: 185

Answers (2)

ThomasIsCoding
ThomasIsCoding

Reputation: 102349

Another base R option using match

df2$COUNTY_NAME <- with(
  df1,
  COUNTY_NAME[match(df2$CountyCode, Abbre)]
)

gives

> df2
  CountyCode Votes COUNTY_NAME
1         WM    97        <NA>
2         AS    14      Asotin
3         WM   163        <NA>
4         WM   144        <NA>
5         SJ    21        <NA>

A data.table option

> setDT(df1)[setDT(df2), on = .(Abbre = CountyCode)]
   Abbre COUNTY_NAME Votes
1:    WM        <NA>    97
2:    AS      Asotin    14
3:    WM        <NA>   163
4:    WM        <NA>   144
5:    SJ        <NA>    21

Upvotes: 1

akrun
akrun

Reputation: 887571

We can use a join

library(dplyr)
library(tidyr)
df2 <- df2 %>%
     left_join(Abbre %>% 
        separate(COUNTY_NAME, into = c("CountyCode", "FullName")),
         by = "CountyCode")

Or use base R

tmp <- read.table(text = Abbre$COUNTY_NAME, header = FALSE, 
       col.names = c("CountyCode", "FullName"))
df2 <- merge(df2, tmp, by = 'CountyCode', all.x = TRUE)

Upvotes: 1

Related Questions