CODEWITHSUNDEEP
rpaste
Hyuser98
Hyuser98

Reputation: 33

concatenate specific character strings in columns

I have a data frame like this:

df <- data.frame("region" = c("Spain", "Barcelona", "Madrid",
                          "France", "Paris", "Lyon", 
                          "Belgium", "Bruges", "Brussels"), 
             "2010" = 1:9, "2011" = c(NA, 1, 2, NA, 3, 4, NA, 5, 6))

I would like to concatenate country name and city name. All the country name’s rows have NA and each city names come after country name.

The data frame I want to have is like this:

desired_df <- data.frame("region" = c("Spain_Spain", "Spain_Barcelona", "Spain_Madrid",
                          "France_France", "France_Paris", "France_Lyon",
                          "Belgium_Belgium", "Belgium_Bruges", "Belgium_Brussels"), 
             "2010" = 1:9, "2011" = c(NA, 1, 2, NA, 3, 4, NA, 5, 6))

It’s ok if country_country rows are missing. Any help would be greatly appreciated.

Upvotes: 3

Views: 78

Answers (3)

Yuriy Saraykin
Yuriy Saraykin

Reputation: 8880

library(dplyr)
library(tidyr)
df %>% 
  mutate(country = if_else(is.na(X2011), region, NULL)) %>% 
  fill(country) %>% 
  unite("region", c(country,region))

Upvotes: 1

akrun
akrun

Reputation: 886938

We can create a grouping variable based on the occurrence of country name, and paste the first element of 'region' with the other elements of 'region' to update the 'region' column

library(dplyr)
library(stringr)
df %>%
   group_by(grp = cumsum(region %in% c("Spain", "France", "Belgium"))) %>%
   mutate(region = str_c(first(region), region, sep="_")) %>%
   ungroup %>% 
   select(-grp)
# A tibble: 9 x 3
#  region           X2010 X2011
#  <chr>            <int> <dbl>
#1 Spain_Spain          1    NA
#2 Spain_Barcelona      2     1
#3 Spain_Madrid         3     2
#4 France_France        4    NA
#5 France_Paris         5     3
#6 France_Lyon          6     4
#7 Belgium_Belgium      7    NA
#8 Belgium_Bruges       8     5
#9 Belgium_Brussels     9     6

Or as @akash87 mentioned, if the pattern should be based on 'X2011'

df %>%
   group_by(grp = cumsum(is.na(X2011))) %>%
   mutate(region = str_c(first(region), region, sep="_")) %>%
   ungroup %>% 
   select(-grp)

Upvotes: 3

akash87
akash87

Reputation: 3994

A generalized solution using tidyverse would require filtering out the country from the other data and joining the data back in:

df %>% 
mutate(gr = cumsum(is.na(X2011))) %>% 
filter(!is.na(X2011)) %>% 
left_join(countries %>% 
          select(region, gr) %>% 
          rename("country" = "region"), by = "gr") %>% 
mutate(new_region = paste(country,region, sep = "_")) %>% 
select(-gr)

Upvotes: 1

Related Questions