CODEWITHSUNDEEP
rcsvmergedata.table
Filipe Siqueira
Filipe Siqueira

Reputation: 33

How to combine two columns in R data.table like this:

I have this data.table:

CITY         CITY2
Phoenix      NA
NASHVILLE    Nashville
Los Angeles  Los Angeles
NEWYORK      New York
CHICAGO      NA

This is the result I want:

CITY
Phoenix
Nashville
Los Angeles
New York
CHICAGO

I tried in many ways and nothing worked. Any idea?

Upvotes: 0

Views: 1279

Answers (2)

AaronT86
AaronT86

Reputation: 53

This is a bit of a mess of a dataframe as you have some desired results in both columns but there appears to be a lack of predictability. Are you sure that city2 has the correct formatting for all values that are not NA? Either way, there are a couple of methods to get to your final desired answer with the correct capitalization of city name using dplyr and the "tools" package.

library(dplyr)
library(tools) 
city_df <- data.frame(
city = c("Phoenix", "NASHVILLE", "Los Angeles", "NEWYORK", "CHICAGO"),
city2 = c(NA, "Nashville", "Los Angeles", "New York", NA), 
stringsAsFactors = FALSE)

The first method assumes city_df$city contains all of the cities but is formatted incorrectly.

 city_df %>% 
 mutate(city = 
        replace(x = city, city == "NEWYORK", values = "New York")) %>% 
select(city) %>% 
mutate(city = tools::toTitleCase(tolower(city)))

which returns 

      city
1     Phoenix
2   Nashville
3 Los Angeles
4    New York
5     Chicago

If you need the values of df_city$city replaced with the Non-NA values of df_city$city2 you can do the following:

city_df %>% 
 mutate(city = case_when(
                         !(is.na(city2)) ~ city2,
                         is.na(city2)    ~ city)) %>% 
 select(city) %>% 
 mutate(city = tools::toTitleCase(tolower(city)))

This returns the same column as above.

Upvotes: 0

Filipe Siqueira
Filipe Siqueira

Reputation: 33

Due to my despair I not stopped researching and found a solution:

myDataTable[ is.na( CITY2 ) & !is.na( CITY ), CITY2 := CITY, ]

Upvotes: 1

Related Questions