Add third column to data frame based on values in two existing columns

Question

I have a dataframe similar to this, where there are several observations for each state over 4 quarters.

df <- data.frame(states=rep(c("AL","AR","FL","GA","LA","MS","NC","OK","SC","TN","TX"), times = 4),
qtr=rep(c(1,2,3,4), times = 11))

I now want to add a third column, where each state is assigned one value for qtr 1 and 2 and a different one for quarter 3 and 4. I want the result to look like this:

state qtr unemp
AL    1   4.4
AL    2   4.4
AL    3   4.1
AL    4   4.1 
AR    1   3.7 
AR    2   3.7 
AR    3   3.9
AR    4   3.9

I hope the pattern is clear. I tried this

df$unemp <- ifelse(df$qtr <3 & df$states %in% "AL",4.4,4.1)

but I don't know how to add more arguments to it. This only created the unemp column but not matching with the arguments.

You really should [use a join](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right?rq=1) to merge the data from your source rather than hand-coding them in. — Mikko Marttila, Aug 28 '18 at 12:13
I got the county data from there. It's a huge xlxs file which I filtered for a different part of the analysis I'm doing. https://www.bls.gov/lau/#tables — carical, Aug 28 '18 at 12:30

Vincent Bonhomme · Accepted Answer · 2018-08-28T12:47:51.343

As pointed in the comments, it's better to provide reproducible examples mimicking your data. What you want to do is a join after some data manipulation (merge the first two qtr into a class, same for the two last).

library(dplyr)
df <- data.frame(states=rep(c("AL","AR","FL","GA","LA","MS","NC","OK","SC","TN","TX"), times = 4),
                 qtr=rep(c(1,2,3,4), times = 11))

df <- df %>% arrange(states, qtr) # pure cosmetics
df <- df %>% mutate(sem=ifelse(qtr <= 2, 1, 2),    # merge the first two and the last two
                    key=paste0(states, "_", sem))  # create a joining key


head(df)
states qtr sem  key
1     AL   1   1 AL_1
2     AL   2   1 AL_1
3     AL   3   2 AL_2
4     AL   4   2 AL_2
5     AR   1   1 AR_1
6     AR   2   1 AR_1

# recreate an external source
ext <- df %>% select(states, sem) %>% distinct()

set.seed(123) # for the sake of reproductibility
ext$unemp <- runif(nrow(ext)/2) # simulate some unemp rates
# you probably have something that looks like this:
head(ext)
states sem     unemp
1     AL   1 0.2875775
2     AL   2 0.7883051
3     AR   1 0.4089769
4     AR   2 0.8830174
5     FL   1 0.9404673
6     FL   2 0.0455565

# recreate a key column
ext <- mutate(ext, key=paste0(states, "_", sem))

# have a look at it
head(ext)
states sem     unemp  key
1     AL   1 0.2875775 AL_1
2     AL   2 0.7883051 AL_2
3     AR   1 0.4089769 AR_1
4     AR   2 0.8830174 AR_2
5     FL   1 0.9404673 FL_1
6     FL   2 0.0455565 FL_2

# left join and drop redundant columns
df2 <- left_join(df, ext, "key") %>% 
  transmute(states=states.x, qtr, unemp)

head(df2)
states qtr     unemp
1     AL   1 0.2875775
2     AL   2 0.2875775
3     AL   3 0.7883051
4     AL   4 0.7883051
5     AR   1 0.4089769
6     AR   2 0.4089769

Is that what you were looking for?

Yeah this is probably the way to go. Thank you! – carical Aug 28 '18 at 12:49 — carical, Aug 28 '18 at 12:49

Add third column to data frame based on values in two existing columns

1 Answers1