CODEWITHSUNDEEP
rif-statementdplyrtibble
Raúl Valentín Fonseca
Raúl Valentín Fonseca

Reputation: 63

Ignore NA values of a column within a statement

Until now I've been working with a medium size dataset for an Ocupation Survey(around 200 mb total), here's the data if you want to review it: https://drive.google.com/drive/folders/1Od8zlOE3U3DO0YRGnBadFz804OUDnuQZ?usp=sharing

I have the following code: 

hogares<-read.csv("/home/servicio/Escritorio/TR_VIVIENDA01.CSV")
personas<-read.csv("/home/servicio/Escritorio/TR_PERSONA01.CSV")
datos<-merge(hogares,personas)

library(dplyr)


base<-tibble(ID_VIV=datos$ID_VIV, ID_PERSONA=datos$ID_PERSONA, EDAD=datos$EDAD, CONACT=datos$CONACT) 
base$maxage <- ave(base$EDAD, base$ID_VIV, FUN=max)
base$Condición_I<-case_when(base$CONACT==32 & base$EDAD>=60 ~ 1,
                            base$CONACT>=10 & base$EDAD>=60 & base$CONACT<=16 ~ 2,
                            base$CONACT==20 & base$EDAD>=60 | base$CONACT==31 & base$EDAD>=60 | (base$CONACT>=33 & base$CONACT<=35 & base$EDAD>=60) ~ 3)
base <- subset(base, maxage >= 60) 
base<- base %>%  group_by(ID_VIV) %>% mutate(Condición_V = if(n_distinct(Condición_I) > 1) 4 else Condición_I)
base$ID_VIV<-as.character(base$ID_VIV)           
base$ID_PERSONA<-as.character(base$ID_PERSONA)
base

And ended up with: 

# A tibble: 38,307 x 7
# Groups:   ID_VIV [10,499]
   ID_VIV      ID_PERSONA     EDAD CONACT maxage Condición_I Condición_V
   <chr>       <chr>         <int>  <int>  <int>       <dbl>       <dbl>
 1 10010000007 1001000000701    69     32     69           1           1
 2 10010000008 1001000000803    83     33     83           3           4
 3 10010000008 1001000000802    47     33     83          NA           4
 4 10010000008 1001000000801    47     10     83          NA           4
 5 10010000012 1001000001204     4     NA     60          NA           4
 6 10010000012 1001000001203     2     NA     60          NA           4
 7 10010000012 1001000001201    60     10     60           2           4
 8 10010000012 1001000001202    21     10     60          NA           4
 9 10010000014 1001000001401    67     32     67           1           4
10 10010000014 1001000001402    64     33     67           3           4

The Condición_I column value is a code for the labour conditions of each individual(row), some of this individuals share house (that's why they share ID_VIV), I only care about the individuals that are 60yo or more, all the NA are individuals who live with a 60+yo but I do not care about their situation (but I need to keep them), I need the column Condición_V to display another value following this conditions: 

Condición_I == 1 ~ 1
Condición_I == 2 ~ 2
Condición_I == 3 ~ 3
Any combination of Condición_I ~ 4

This means that if all the 60 and+_yo individuals in a house have Condición_I == 1 then Condición_V will be 1 that's true up to code 3, when there are x.e. one person C_I == 1 and another one C_I == 3 in the same house, then Condición_V will be 4
And I'm hoping to get this kind of result:

A tibble: 38,307 x 7

# Groups:   ID_VIV [10,499]
   ID_VIV      ID_PERSONA     EDAD CONACT maxage Condición_I Condición_V
   <chr>       <chr>         <int>  <int>  <int>       <dbl>       <dbl>
 1 10010000007 1001000000701    69     32     69           1           1
 2 10010000008 1001000000803    83     33     83           3           3
 3 10010000008 1001000000802    47     33     83          NA           3
 4 10010000008 1001000000801    47     10     83          NA           3
 5 10010000012 1001000001204     4     NA     60          NA           2
 6 10010000012 1001000001203     2     NA     60          NA           2
 7 10010000012 1001000001201    60     10     60           2           2
 8 10010000012 1001000001202    21     10     60          NA           2
 9 10010000014 1001000001401    67     32     67           1           4
10 10010000014 1001000001402    64     33     67           3           4

I know my error is in: 

`#base<- base %>%  group_by(ID_VIV) %>% mutate(Condición_V = if(n_distinct(Condición_I) > 1) 4 else` Condición_I)

Is there a way to use that line of code ignoring the NA values or is it my best option to do it otherway, I do not have to do it the way I'm trying and any other way or help will be much appreciated!

Upvotes: 1

Views: 199

Answers (1)

akrun
akrun

Reputation: 886928

We can wrap with na.omit on the Condición_I column, check the number of distinct elements with n_distinct and if it is greater than 1, return 4 or else return the na.omit of the column

library(dplyr)
base %>% 
    group_by(ID_VIV) %>% 
    mutate(Condición_V = if(n_distinct(na.omit(Condición_I)) > 1) 
            4 else na.omit(Condición_I)[1])
# A tibble: 10 x 7
# Groups:   ID_VIV [4]
#   ID_VIV      ID_PERSONA     EDAD CONACT maxage Condición_I Condición_V
#   <chr>       <chr>         <int>  <int>  <int>       <int>       <dbl>
# 1 10010000007 1001000000701    69     32     69           1           1
# 2 10010000008 1001000000803    83     33     83           3           3
# 3 10010000008 1001000000802    47     33     83          NA           3
# 4 10010000008 1001000000801    47     10     83          NA           3
# 5 10010000012 1001000001204     4     NA     60          NA           2
# 6 10010000012 1001000001203     2     NA     60          NA           2
# 7 10010000012 1001000001201    60     10     60           2           2
# 8 10010000012 1001000001202    21     10     60          NA           2
# 9 10010000014 1001000001401    67     32     67           1           4
#10 10010000014 1001000001402    64     33     67           3           4

data

base <- structure(list(ID_VIV = c("10010000007", "10010000008", "10010000008", 
"10010000008", "10010000012", "10010000012", "10010000012", "10010000012", 
"10010000014", "10010000014"), ID_PERSONA = c("1001000000701", 
"1001000000803", "1001000000802", "1001000000801", "1001000001204", 
"1001000001203", "1001000001201", "1001000001202", "1001000001401", 
"1001000001402"), EDAD = c(69L, 83L, 47L, 47L, 4L, 2L, 60L, 21L, 
67L, 64L), CONACT = c(32L, 33L, 33L, 10L, NA, NA, 10L, 10L, 32L, 
33L), maxage = c(69L, 83L, 83L, 83L, 60L, 60L, 60L, 60L, 67L, 
67L), Condición_I = c(1L, 3L, NA, NA, NA, NA, 2L, NA, 1L, 3L
)), row.names = c("1", "2", "3", "4", "5", "6", "7", "8", "9", 
"10"), class = "data.frame")

Upvotes: 1

Related Questions