data.table or dplyr - data manipulation

data-manipulation, data.table, dplyr, plyr, r

Solution

library(dplyr)
df %.% 
  arrange(Date) %.% 
  filter(!duplicated(Col1)) %.% 
  group_by(Date) %.% 
  summarise(Count=n()) %.% # n() <=> length(Date)
  mutate(Count = cumsum(Count))
# Source: local data frame [3 x 2]
# 
#         Date Count
# 1 2014-01-01     3
# 2 2014-01-02     5
# 3 2014-01-03     6

library(data.table)
dt <- data.table(df, key="Date")
dt <- unique(dt, by="Col1")
(dt <- dt[, list(Count=.N), by=Date][, Count:=cumsum(Count)])
#          Date Count
# 1: 2014-01-01     3
# 2: 2014-01-02     5
# 3: 2014-01-03     6

Or

dt <- data.table(df, key="Date")
dt <- unique(dt, by="Col1")
dt[, .N, by=Date][, Count:=cumsum(N)]

`.N` is named `N` (no dot) automatically for convenience in chained operations like this, so you can use both `.N` and `N` together in the next operation if need be.

Problem

I have the following data ``` Date Col1 Col2 2014-01-01 123 12 2014-01-01 123 21 2014-01-01 124 32 2014-01-01 125 32 2014-01-02 123 34 2014-01-02 126 24 2014-01-02 127 23 2014-01-03 521 21 2014-01-03 123 13 2014-01-03 126 15 ``` Now, I want to count unique values in `Col1` for the each date (that did not repeat in previous date), and add to the previous count. For example, ``` Date Count 2014-01-01 3 i.e. 123,124,125 2014-01-02 5 (2 + above 3) i.e. 126, 127 2014-01-03 6 (1 + above 5) i.e. 521 only ```

Original source