Hi all, I have a dataframe of users which contain US-state codes. Now I want to add a column named REGION based on the state code. I have already done a mapping: NorthEast <- c(07, 20, 22, 30, 31, 33, 39, 41, 47) MidWest <- c(14, 15, 16, 17, 23, 24, 26, 28, 35, 36, 43, 52) South <- c(01, 04, 08, 09, 10, 11, 18, 19, 21, 25, 34, 37, 42, 44, 45, 49, 51) West <- c(02, 03, 05, 06, 12, 13, 27, 29, 32, 38, 46, 50, 53) Other <- c(40, 48, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 94, 98, 99) So for example: Name State_Code Tom 20 Harry 56 Ben 05 Sally 04 Should become like: So for example: Name State_Code REGION Tom 20 NorthEast Harry 56 Other Ben 05 West Sally 04 South Could anyone help me with a clever statement? -- View this message in context: http://r.789695.n4.nabble.com/Add-column-to-dataframe-based-on-code-in-other-column-tp4673335.html Sent from the R help mailing list archive at Nabble.com.
dat1<- read.table(text="
Name??? State_Code
Tom????? 20
Harry??? 56
Ben??????? 05
Sally????? 04
",sep="",header=TRUE,stringsAsFactors=FALSE)
dat2<- do.call(cbind,list(NorthEast,MidWest,South,West,Other))
?colnames(dat2)<-
c("NorthEast","MidWest","South","West","Other")
?dat2<- as.data.frame(dat2)
library(reshape2)
datM<-melt(dat2)
colnames(datM)<- c("REGION","State_Code")
library(plyr)
join(dat1,datM,type="left",match="first",by="State_Code")[,c(2,1,3)]
#?? Name State_Code??? REGION
#1?? Tom???????? 20 NorthEast
#2 Harry???????? 56???? Other
#3?? Ben????????? 5????? West
#4 Sally????????? 4???? South
A.K.
----- Original Message -----
From: Dark <info at software-solutions.nl>
To: r-help at r-project.org
Cc:
Sent: Thursday, August 8, 2013 5:33 AM
Subject: [R] Add column to dataframe based on code in other column
Hi all,
I have a dataframe of users which contain US-state codes.
Now I want to add a column named REGION based on the state code. I have
already done a mapping:
NorthEast <- c(07, 20, 22, 30, 31, 33, 39, 41, 47)
MidWest <- c(14, 15, 16, 17, 23, 24, 26, 28, 35, 36, 43, 52)
South <- c(01, 04, 08, 09, 10, 11, 18, 19, 21, 25, 34, 37, 42, 44, 45, 49,
51)
West <- c(02, 03, 05, 06, 12, 13, 27, 29, 32, 38, 46, 50, 53)
Other <- c(40, 48, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 94,
98, 99)
So for example:
Name? ? State_Code
Tom? ? ? 20
Harry? ? 56
Ben? ? ? ? 05
Sally? ? ? 04
Should become like:
So for example:
Name? ? State_Code REGION
Tom? ? ? 20? ? ? ? ? ? ? ? ? NorthEast
Harry? ? 56? ? ? ? ? ? ? ? ? Other
Ben? ? ? ? 05? ? ? ? ? ? ? ? ? West
Sally? ? ? 04? ? ? ? ? ? ? ? ? South
Could anyone help me with a clever statement?
--
View this message in context:
http://r.789695.n4.nabble.com/Add-column-to-dataframe-based-on-code-in-other-column-tp4673335.html
Sent from the R help mailing list archive at Nabble.com.
______________________________________________
R-help at r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Berend Hasselman
2013-Aug-08 14:14 UTC
[R] Add column to dataframe based on code in other column
On 08-08-2013, at 11:33, Dark <info at software-solutions.nl> wrote:> Hi all, > > I have a dataframe of users which contain US-state codes. > Now I want to add a column named REGION based on the state code. I have > already done a mapping: > > NorthEast <- c(07, 20, 22, 30, 31, 33, 39, 41, 47) > MidWest <- c(14, 15, 16, 17, 23, 24, 26, 28, 35, 36, 43, 52) > South <- c(01, 04, 08, 09, 10, 11, 18, 19, 21, 25, 34, 37, 42, 44, 45, 49, > 51) > West <- c(02, 03, 05, 06, 12, 13, 27, 29, 32, 38, 46, 50, 53) > Other <- c(40, 48, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 94, > 98, 99) > > So for example: > Name State_Code > Tom 20 > Harry 56 > Ben 05 > Sally 04 > > Should become like: > So for example: > Name State_Code REGION > Tom 20 NorthEast > Harry 56 Other > Ben 05 West > Sally 04 South >dd <- read.table(text="Name State_Code Tom 20 Harry 56 Ben 05 Sally 04", header=TRUE, stringsAsFactors=FALSE) # Create table for regions indexed by state_code region.table <- rep("UNKNOWN",99) region.table[NorthEast] <- "NorthEast" region.table[MidWest] <- "MidWest" region.table[South] <- "South" region.table[West] <- "West" region.table[Other] <- "Other" region.table # then this is easy dd[,"REGION"] <- region.table[dd$State_Code] Berend
MacQueen, Don
2013-Aug-08 22:43 UTC
[R] Add column to dataframe based on code in other column
Assuming your data frame of users is named 'users',
and using your mapping vectors:
users$regions <- ''
users$regions[ users$State_Code) %in% NorthEast ] <- 'NorthEast'
and repeat for the other regions
Or, if you put your mappings in a data frame then it is
as simple as
merge(yourdataframe, regions)
(assuming the data frame of mappings is named 'regions')
The regions data frame should have two columns and 50 rows
The two columns contain the state codes and their
respective regions.
How you get that data frame of regions could vary;
here's an example using your vectors, but just two
of the regions:
regions <- data.frame(
region= c( rep('NorthEast',length(NorthEast)),
rep('MidWest,length(MidWest))
),
State_Code=c(NorthEast,Midwest)
)
Note that this is untested. For example,
I could easily have mismatched parentheses.
The whole thing could also be done using match(),
without creating the dataframe of regions.
-Don
--
Don MacQueen
Lawrence Livermore National Laboratory
7000 East Ave., L-627
Livermore, CA 94550
925-423-1062
On 8/8/13 2:33 AM, "Dark" <info at software-solutions.nl> wrote:
>Hi all,
>
>I have a dataframe of users which contain US-state codes.
>Now I want to add a column named REGION based on the state code. I have
>already done a mapping:
>
>NorthEast <- c(07, 20, 22, 30, 31, 33, 39, 41, 47)
>MidWest <- c(14, 15, 16, 17, 23, 24, 26, 28, 35, 36, 43, 52)
>South <- c(01, 04, 08, 09, 10, 11, 18, 19, 21, 25, 34, 37, 42, 44, 45,
49,
>51)
>West <- c(02, 03, 05, 06, 12, 13, 27, 29, 32, 38, 46, 50, 53)
>Other <- c(40, 48, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66,
94,
>98, 99)
>
>So for example:
>Name State_Code
>Tom 20
>Harry 56
>Ben 05
>Sally 04
>
>Should become like:
>So for example:
>Name State_Code REGION
>Tom 20 NorthEast
>Harry 56 Other
>Ben 05 West
>Sally 04 South
>
>Could anyone help me with a clever statement?
>
>
>
>--
>View this message in context:
>http://r.789695.n4.nabble.com/Add-column-to-dataframe-based-on-code-in-oth
>er-column-tp4673335.html
>Sent from the R help mailing list archive at Nabble.com.
>
>______________________________________________
>R-help at r-project.org mailing list
>https://stat.ethz.ch/mailman/listinfo/r-help
>PLEASE do read the posting guide
>http://www.R-project.org/posting-guide.html
>and provide commented, minimal, self-contained, reproducible code.