How to join a dataframe with a new variable?

Question

I have a table that is a combination of 4 things.

place1 <- c("Florida", "California", "Georgia")
race1 <- c("NHW", "NHB", "Hisp")
race2 <- c("NHW", "NHB", "Hisp")
cancer <- c("Lung", "Liver", "Thyroid")

combos <- expand.grid(place1, race1, race2, cancer, stringsAsFactors = FALSE)
names(combos) <- c("place1", "race1", "race2", "cancer")

I would like to add another column that is called place2. It needs to hold the value from place1, "USA", and then "France". That is, I need to take each record and multiply it by 3.

Currently, the first two records are:

picture of first records is here.

The dataframe I want to produce will look like this:

picture of what I want data to look like

Possible duplicate of [How to join (merge) data frames (inner, outer, left, right)?](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right) — divibisan, Jul 17 '18 at 20:58
`merge(combos, data.frame(place2=c("Florida","USA","France"),stringsAsFactors=FALSE))` — r2evans, Jul 17 '18 at 21:01
@r2evans I think OP wants first addition as `place2` same as `place1`. — MKR, Jul 17 '18 at 21:02
More generally, if you look at [`?merge`](https://stat.ethz.ch/R-manual/R-patched/library/base/html/merge.html): *If by or both by.x and by.y are of length 0 (a length zero vector or NULL), the result, r, is the Cartesian product of x and y, i.e., dim(r) = c(nrow(x)*nrow(y), ncol(x) + ncol(y)).* My code could be more explicit by adding `by=NULL`, but since we are confident here that the intersection is the empty set, it is perhaps redundant. — r2evans, Jul 17 '18 at 21:03
@MKR, good point ... then `newcombos$place2 <- with(newcombos, ifelse(place2 == "Florida", place1, place2))`? — r2evans, Jul 17 '18 at 21:05

score 2 · Accepted Answer · answered Jul 17 '18 at 21:05

using tidyverse:

library(tidyverse)
combos %>%
  mutate(place2 = map(place1,list,"USA","France")) %>%
  unnest
#         place1 race1 race2  cancer     place2
# 1      Florida   NHW   NHW    Lung    Florida
# 2      Florida   NHW   NHW    Lung        USA
# 3      Florida   NHW   NHW    Lung     France
# 4   California   NHW   NHW    Lung California
# 5   California   NHW   NHW    Lung        USA
# 6   California   NHW   NHW    Lung     France
# 7      Georgia   NHW   NHW    Lung    Georgia
# 8      Georgia   NHW   NHW    Lung        USA
# 9      Georgia   NHW   NHW    Lung     France
# 10     Florida   NHB   NHW    Lung    Florida
# 11     Florida   NHB   NHW    Lung        USA
# ...

score 2 · Answer 2 · answered Jul 17 '18 at 21:06

Just with base R:

# add columns
new_vals = c("USA", "France")
result = rbind(
  merge(combos, data.frame(place2 = new_vals), all = TRUE),
  transform(combos, place2 = place1)
)

# order result rows and columns
with(result, result[order(place1, race1, race2, cancer, place2), c("place1", "place2", "race1", "race2", "cancer")])
#         place1     place2 race1 race2  cancer
# 134 California     France  Hisp  Hisp   Liver
# 53  California        USA  Hisp  Hisp   Liver
# 215 California California  Hisp  Hisp   Liver
# 107 California     France  Hisp  Hisp    Lung
# 26  California        USA  Hisp  Hisp    Lung
# 188 California California  Hisp  Hisp    Lung
# ...

score 0 · Answer 3 · answered Jul 17 '18 at 20:59

A simple way could be by using combination of cbind and rbind as:

rbind(cbind(combos, place2 = combos$place1),
      cbind(combos, place2 = "USA"),
      cbind(combos, place2 = "France"))

#        place1 race1 race2  cancer     place2
# 1     Florida   NHW   NHW    Lung    Florida
# 2  California   NHW   NHW    Lung California
# 3     Georgia   NHW   NHW    Lung    Georgia
# 4     Florida   NHB   NHW    Lung    Florida
#
# so on 239 more rows

How to join a dataframe with a new variable?

3 Answers3