CODEWITHSUNDEEP
rdatabasedplyr
Boris
Boris

Reputation: 131

Create a conditional count variable in R

I want to create a count variable with the number of peoples with Z==0 in each of the given years. As Illustrated below: 

PersonID    Year    Z   Count*
       1    1990    0   1
       2    1990    1   1
       3    1990    1   1
       4    1990    2   1
       5    1990    1   1
       1    1991    1   3
       2    1991    0   3
       3    1991    1   3
       4    1991    0   3
       5    1991    0   3
       1    1992    NA  1
       2    1992    2   1
       3    1992    2   1
       4    1992    0   1
       5    1993    1   0
       1    1993    1   0
       2    1993    2   0
       3    1993    NA  0
       4    1993    1   0
       5    1994    0   5
       1    1994    0   5
       2    1994    0   5
       3    1994    0   5
       4    1994    0   5

I looked at my previous R-scripts and found this 

library(dplyr)
sum_data <- data %>% group_by(PersonID) %>% summarise(Count = sum(Z, na.rm=T))

Can someone help me get this right? The count variable should basically count a total number of persons with Z==0, in the same format as I illustrated above. Thanks!!

dput(data)
structure(list(PersonID = c(1L, 2L, 3L, 4L, 5L, 1L, 2L, 3L, 4L, 
5L, 1L, 2L, 3L, 4L, 5L, 1L, 2L, 3L, 4L, 5L, 1L, 2L, 3L, 4L), 
    Year = c(1990L, 1990L, 1990L, 1990L, 1990L, 1991L, 1991L, 
    1991L, 1991L, 1991L, 1992L, 1992L, 1992L, 1992L, 1993L, 1993L, 
    1993L, 1993L, 1993L, 1994L, 1994L, 1994L, 1994L, 1994L), 
    Z = c(0L, 1L, 1L, 2L, 1L, 1L, 0L, 1L, 0L, 0L, NA, 2L, 2L, 
    0L, 1L, 1L, 2L, NA, 1L, 0L, 0L, 0L, 0L, 0L)), .Names = c("PersonID", 
"Year", "Z"), class = "data.frame", row.names = c(NA, -24L))

Upvotes: 2

Views: 1458

Answers (2)

Sandipan Dey
Sandipan Dey

Reputation: 23129

Try this:

library(dplyr)
df <- left_join(data, data %>% filter(Z==0) %>% group_by(Year) %>% summarise(Count = n())) 
df[is.na(df$Count),]$Count <- 0


 PersonID Year  Z Count
1         1 1990  0     1
2         2 1990  1     1
3         3 1990  1     1
4         4 1990  2     1
5         5 1990  1     1
6         1 1991  1     3
7         2 1991  0     3
8         3 1991  1     3
9         4 1991  0     3
10        5 1991  0     3
11        1 1992 NA     1
12        2 1992  2     1
13        3 1992  2     1
14        4 1992  0     1
15        5 1993  1     0
16        1 1993  1     0
17        2 1993  2     0
18        3 1993 NA     0
19        4 1993  1     0
20        5 1994  0     5
21        1 1994  0     5
22        2 1994  0     5
23        3 1994  0     5
24        4 1994  0     5

Upvotes: 1

Menelith
Menelith

Reputation: 551

Here's a simple solution : 

library(dplyr)

sum_data <- df %>%
  mutate(Z=replace(Z, is.na(Z), 1)) %>%
  mutate(temp = ifelse(Z == 0, 1, 0)) %>%
  group_by(Year) %>%
  summarize(count = sum(temp))

basically this is what the code is doing :

  • mutate(Z=replace(Z, is.na(Z), 1)) replace the NA with 1 (optional)
  • mutate(temp = ifelse(Z == 0, 1, 0)) create a conditional temp variable :
  • ifelse(Z == 0, 1, 0) say if Z == 0 then the value is 1 else 0
  • group_by(Year) pretty explicite :) it group the data frame by Year
  • summarize(count = sum(temp)) create a count variable with the sum of earlier generated temp

results : 

   Year count
  <int> <int>
1  1990     5
2  1991     5
3  1992     4
4  1993     5
5  1994     5

and if you want to join this data to the original data frame just use join :

left_join(df, sum_data)
Joining, by = "Year"
   PersonID Year  Z count
1         1 1990  0     1
2         2 1990  1     1
3         3 1990  1     1
4         4 1990  2     1
5         5 1990  1     1
6         1 1991  1     3
7         2 1991  0     3
8         3 1991  1     3
9         4 1991  0     3
10        5 1991  0     3
11        1 1992 NA     1
12        2 1992  2     1
13        3 1992  2     1
14        4 1992  0     1
15        5 1993  1     0
16        1 1993  1     0
17        2 1993  2     0
18        3 1993 NA     0
19        4 1993  1     0
20        5 1994  0     5
21        1 1994  0     5
22        2 1994  0     5
23        3 1994  0     5
24        4 1994  0     5

Upvotes: 1

Related Questions