data aggregation

number=50
data <- matrix(nrow = number, ncol = 3 )
colnames(data) <- c("ID",  "Shop_year", "Region")
set.seed(1)
data[,1] <- c(sample(1:25, size=number, replace = T) )#ID

data[,2] <- c(sample(c("year2013","year2014","year2015"), size=number, replace= T, prob = c(0.1, 0.3, 0.6) ) )
data[,3] <- c(sample(c("北區一","北區二","北區三"), size = number,
                     replace = T, prob = c(0.5,0.3,0.2) ))
data <- data.frame(data)

library(reshape2)
library(reshape)
result <- cast(data, ID~Shop_year,value=c("Region"))

##分類成三組
data_new <- with(result, subset(result, year2015 !=0 & year2014 == 0))
data_new$level <- "新增"
data_lost <- with(result, subset(result, year2015 == 0))
data_lost$level <- "流失"
data_now <- with(result, subset(result, year2015 != 0 & year2014 !=0))
data_now$level <- "回購"

##合併檔案
data_combine <- do.call(rbind, list(data_now, data_new, data_lost))
## 新增分組資料到原始檔案
data_final <- merge(data, data_combine)
#    ID Shop_year Region year2013 year2014 year2015 level
# 1   1  year2014 北區二        0        1        1  回購
# 2   1  year2015 北區一        0        1        1  回購
# 3  10  year2014 北區一        0        2        3  回購
# 4  10  year2015 北區一        0        2        3  回購
# 5  10  year2014 北區一        0        2        3  回購
# 6  10  year2015 北區一        0        2        3  回購
# 7  10  year2015 北區一        0        2        3  回購
# 8  11  year2015 北區二        0        0        1  新增
# 9  12  year2015 北區二        0        0        1  新增
# 10 13  year2015 北區二        0        0        3  新增

## method 1
library(dplyr)
library(magrittr)
library(reshape2)
library(data.table)
DT = data %>% tbl_dt() %>% mutate(ID = ID %>% as.character %>% as.integer)
DT %>% dcast.data.table(ID ~ Shop_year) %>%
  mutate(level = ifelse(year2014 !=0 && year2015 ==0, "流失", ifelse(
    year2014 !=0 && year2015 !=0, "回購", "新增"
  ))) %>% left_join(DT) %>% arrange(ID) %>% distinct %>%
  select(-Shop_year)

#    ID year2013 year2014 year2015 level Region
# 1   1        0        1        1  回購 北區二
# 2   2        0        0        1  回購 北區二
# 3   3        0        0        1  回購 北區二
# 4   4        0        0        1  回購 北區一
# 5   5        0        0        2  回購 北區二
# 6   6        1        1        1  回購 北區二
# 7   7        0        0        2  回購 北區二
# 8   9        1        0        0  回購 北區二
# 9  10        0        2        3  回購 北區一
# 10 11        0        0        1  回購 北區二

## method 2
library(dplyr)
library(tidyr)
library(magrittr)
library(data.table)
DT = data %>% tbl_dt() %>% mutate(ID = ID %>% as.character %>% as.integer,
  Shop_year = factor(Shop_year, levels = paste0("year", 2013:2015)))
DT %>% gather(variable, years, -ID, -Region) %>% select(-variable) %>%
  group_by(ID, years) %>%
  mutate(count = length(Region)) %>% group_by(ID) %>%
  mutate(level = ifelse(all(c("year2014", "year2015") %in% years), "回購",
    ifelse("year2015" %in% years, "新增", "流失"))) %>% arrange(ID, years)

#    ID Region    years count level
# 1   1 北區二 year2014     1  回購
# 2   1 北區一 year2015     1  回購
# 3   2 北區二 year2015     1  新增
# 4   3 北區二 year2015     1  新增
# 5   4 北區一 year2015     1  新增
# 6   5 北區二 year2015     2  新增
# 7   5 北區二 year2015     2  新增
# 8   6 北區三 year2013     1  回購
# 9   6 北區一 year2014     1  回購
# 10  6 北區二 year2015     1  回購
# .. ..    ...      ...   ...   ...