CODEWITHSUNDEEP
r
Prajwal Mani Pradhan
Prajwal Mani Pradhan

Reputation: 397

Merge multiple categorical variables into one using value from another value in r

I have data columns like:

Weather1    Weather.2   Weather3        
Sunny       Sunny       NA              
Cloudy      NA          some cloud      
Hazy        Hazy        Hazy            
Warm        NA          Warm            
NA          NA          NA

Here is a snippet of code to recreate the data:

example = data.frame(Weather1 = c('Sunny','Cloudy','Hazy','Warm','NA'),
                     Weather.2 = c('Sunny','NA','Hazy','NA','NA'),
                     Weather3 = c('NA','some cloud','Hazy','NA', 'NA'))

I want to combine the three variables like the following:

Weather1    Weather.2   Weather3        combine_weather
Sunny       Sunny       NA              Sunny
Cloudy      NA          some cloud      Cloudy
Hazy        Hazy        Hazy            Hazy
Warm        NA          NA              Warm
NA          NA          NA              NA

I want to use value from first variable if there is any mismatch.

Upvotes: 1

Views: 686

Answers (3)

jlhoward
jlhoward

Reputation: 59335

Using data.table:

library(data.table)
setDT(example)[
  , combined.weather:=fcoalesce(.SD)
  , .SDcols=c('Weather1', 'Weather.2', 'Weather3')]

example
##    Weather1 Weather.2   Weather3 combined.weather
## 1:    Sunny     Sunny       <NA>            Sunny
## 2:   Cloudy      <NA> some cloud           Cloudy
## 3:     Hazy      Hazy       Hazy             Hazy
## 4:     Warm      <NA>       <NA>             Warm
## 5:     <NA>      <NA>       <NA>             <NA>

Upvotes: 0

TarJae
TarJae

Reputation: 78917

Another way could be first define how your combine_weather could be in pattern: then extract after unite:

library(tidyverse)

pattern <- c("Sunny|Cloudy|Hazy|Warm")

example %>% 
  na_if("NA") %>% 
  unite(combine_weather, starts_with("Weather"), sep = " ", na.rm = TRUE,  remove = FALSE) %>%
  mutate(combine_weather = str_extract(combine_weather, pattern))

  combine_weather Weather1 Weather.2   Weather3
1           Sunny    Sunny     Sunny       <NA>
2          Cloudy   Cloudy      <NA> some cloud
3            Hazy     Hazy      Hazy       Hazy
4            Warm     Warm      <NA>       <NA>
5            <NA>     <NA>      <NA>       <NA>

Upvotes: 0

benson23
benson23

Reputation: 19097

You can use coalesce to fill in NA horizontally with the first non-missing value.

Note I have changed your string "NA" into real NA first.

There are a few ways to specify columns in "tidy-select" style:

  1. If your columns that need to be acted on all start with the string "Weather", you can do select(., starts_with("Weather")).
  2. If they are ordered next to each other, you can do select(., Weather1:Weather3).
  3. Or select them manually select(., Weather1, Weather.2, Weather3)
library(dplyr)

example %>% mutate(across(everything(), ~ifelse(.x == "NA", NA, .x)),
                   combine_weather = coalesce(!!!select(., everything())))

  Weather1 Weather.2   Weather3 combine_weather
1    Sunny     Sunny       <NA>           Sunny
2   Cloudy      <NA> some cloud          Cloudy
3     Hazy      Hazy       Hazy            Hazy
4     Warm      <NA>       <NA>            Warm
5     <NA>      <NA>       <NA>              NA

Upvotes: 1

Related Questions