How to calculate a frequency (count) variable in R?

Question

I've begun to gradually shift to R from Excel but I'm still having some difficulties with (relatively simple) calculations.

I want to create a frequency version of my variable x, let's call it "xfrequency".

Please, see the sample of my data below.

The desired variable xfrequency should basically count the number of x's, during a certain period (country-year). In the sample data the observation period is from 1990 to 1995. So, in 1994 Canada recieved 4 x's in total.

Perhaps there is a relevant function for this out there? Thanks!

country year    x   xfrequency
CAN     1990    1   1
CAN     1991    0   0
CAN     1992    1   2
CAN     1993    0   0
CAN     1994    2   4
CAN     1995    1   5
USA     1990    0   0
USA     1991    2   2
USA     1992    1   3
USA     1993    0   0
USA     1994    1   4
USA     1995    0   0
GER     1990    NA  NA
GER     1991    1   1
GER     1992    0   0
GER     1993    1   2
GER     1994    2   4
GER     1995    1   5

@TimBiegeleisen the Q is about creating the xfrequency column if I understood properly., i.e: sum from 1990 to current year the values of x by country. — Tensibai, Feb 17 '16 at 08:40
No...he wants a cumulative sum by increasing year, for each country group. His wording is not very good IMO. — Tim Biegeleisen, Feb 17 '16 at 08:41
@TimBiegeleisen it's 4, unless you have better data on x's ;). (1+1+2=4) — FKG, Feb 17 '16 at 08:41
@FKG did not you xfrequency column be `[1] 1 1 2 2 4 5 0 2 3 3 4 4 0 1 1 2 4 5` — Tensibai, Feb 17 '16 at 08:47
@Tensibai interesting, yes– one can make it like that as well. The ideal thing would be to learn how to calculate both ways (your suggestion and the one in the sample) — FKG, Feb 17 '16 at 08:51
What about something like `aggregate(x ~ country, df, cumsum)` ? — Sotos, Feb 17 '16 at 08:57

Tensibai · Accepted Answer · 2016-02-17T09:12:18.357

Example with data.table assuming your dataset in in a variable called data:

library(data.table)
setDT(data)
data[is.na(x),x := 0] # Remove the NA as a sum of anything with NA is NA
data[, xfreq := cumsum(x), by=country]

Which gives:

    country year x xfrequency xfreq
 1:     CAN 1990 1          1     1
 2:     CAN 1991 0          0     1
 3:     CAN 1992 1          2     2
 4:     CAN 1993 0          0     2
 5:     CAN 1994 2          4     4
 6:     CAN 1995 1          5     5
 7:     USA 1990 0          0     0
 8:     USA 1991 2          2     2
 9:     USA 1992 1          3     3
10:     USA 1993 0          0     3
11:     USA 1994 1          4     4
12:     USA 1995 0          0     4
13:     GER 1990 0         NA     0
14:     GER 1991 1          1     1
15:     GER 1992 0          0     1
16:     GER 1993 1          2     2
17:     GER 1994 2          4     4
18:     GER 1995 1          5     5

this is not exactly your expected output, but according to the description you give, the xfreq column seems to be what you're looking for.

To get your exact output, we can reset the xfreq to 0 when x is 0:

> data[x==0,xfreq := 0]
> data
    country year x xfrequency xfreq
 1:     CAN 1990 1          1     1
 2:     CAN 1991 0          0     0
 3:     CAN 1992 1          2     2
 4:     CAN 1993 0          0     0
 5:     CAN 1994 2          4     4

Or in one pass with a test:

data[, xfreq := ifelse(x==0,0L,cumsum(x)), by=country]

score 2 · Answer 2 · answered Feb 17 '16 at 09:15

A base R alternative:

mydf <- transform(mydf, xfreq = ave(x, country, FUN = function(x) cumsum(!is.na(x))))
mydf[mydf$x==0 | is.na(mydf$x), "xfreq"] <- 0

gives:

> mydf
   country year  x xfrequency xfreq
1      CAN 1990  1          1     1
2      CAN 1991  0          0     0
3      CAN 1992  1          2     3
4      CAN 1993  0          0     0
5      CAN 1994  2          4     5
6      CAN 1995  1          5     6
7      USA 1990  0          0     0
8      USA 1991  2          2     2
9      USA 1992  1          3     3
10     USA 1993  0          0     0
11     USA 1994  1          4     5
12     USA 1995  0          0     0
13     GER 1990 NA         NA     0
14     GER 1991  1          1     1
15     GER 1992  0          0     0
16     GER 1993  1          2     3
17     GER 1994  2          4     4
18     GER 1995  1          5     5

DenisK · Answer 3 · 2016-02-17T08:33:11.783

1

You can use library(dplyr).

library(dplyr)
sum_data <- data %>% group_by(country) %>% summarise(xfrequency = sum(x, na.rm=T)).

I just grouped your data by country and added sum of x for all the periods given for this country.

edited Feb 17 '16 at 08:33

answered Feb 17 '16 at 08:26

DenisK

140
1
8

1

This does no give the xfrequency column OP's is looking for. – Tensibai Feb 17 '16 at 08:41
Thanks DenisK. But this generates same results as in plyr: `newdata<- ddply(mydata, ~country, summarise, xfrequency=sum(x, na.rm=TRUE))` and it gives the total sum of x's for all years combined. So not what i wanted, but perhaps useful to know in future – FKG Feb 17 '16 at 08:55

How to calculate a frequency (count) variable in R?

3 Answers3