[R and SQL] [0630 ...................]

[R and SQL]

[0630 ...................]

[1] GT, NT, 새로운 5개 연관된 키워드에 대한 정보 정리 연습

(관심/흥미 주제(? 스포츠, 연예, 시사, 경제) 데이터 활용) 

월이 넘어가는 경우 비율적용해서 월 구분하는 것 연습

프리미어리그 팀들 NT, GT 결합예제

gtclub <- read.csv("GT_clubs.csv")

ntpl_mb <- read.csv("premierleague_NT_mobile.csv")

ntpl_pc <- read.csv("premierleague_NT_pc.csv")

names(ntpl_pc) <- c("no", "dweek", "premier_league_ntpck", "chelsea_ntpck", "manu_ntpck", "arsenal_ntpck", "liverpool_ntpck")

names(ntpl_mb) <- c("no", "dweek", "premier_league_ntmbk", "chelsea_ntmbk", "manu_ntmbk", "arsenal_ntmbk", "liverpool_ntmbk")

ntpl <- merge(ntpl_mb, ntpl_pc, by="dweek")

ntpl1 <- merge(ntpl_mb, ntpl_pc, by="dweek", all.x = TRUE, all.y = TRUE)

#----------- 구글과 네이버 결합 (시점 맞추기 ) -------------

# 네이버가 길기 때문에 네이버에 일련번호 부여

ntpl1$nseq <- c(1:nrow(ntpl1))

# 짧은 구글의 처음을 확인

head(gtclub,1)

# 구글의 처음은 2011년

# 네이버에서 2011년이 nseq 몇번인지 값 확인

tmp1 <- ntpl1[which(substr(ntpl1$dweek,1,4)=='2011'),c(1,14)]

# 28번 부터 시작하므로

# 구글에 27을 더한값을 부여

gtclub$nseq <- c(1:nrow(gtclub))+27

#--------------------

gtclub <- read.csv("GT_clubs.csv")

ntpl_mb <- read.csv("premierleague_NT_mobile.csv")

ntpl_pc <- read.csv("premierleague_NT_pc.csv")

names(ntpl_pc) <- c("no", "dweek", "premier_league_ntpck", "chelsea_ntpck", 

"manu_ntpck", "arsenal_ntpck", "liverpool_ntpck")

names(ntpl_mb) <- c("no", "dweek", "premier_league_ntmbk", "chelsea_ntmbk", 

"manu_ntmbk", "arsenal_ntmbk", "liverpool_ntmbk")

ntpl <- merge(ntpl_mb, ntpl_pc, by="dweek")

ntpl1 <- merge(ntpl_mb, ntpl_pc, by="dweek", all.x = TRUE, all.y = TRUE)

# 네이버가 길기 때문에 네이버에 일련번호 부여

ntpl1$nseq <- c(1:nrow(ntpl1))

ntpl1 <- ntpl1[order(as.character(ntpl1$dweek)),]

ntpl1$nseq <- c(1:nrow(ntpl1))

# 짧은 구글의 처음을 확인

head(gtclub,1)

# 구글의 처음은 2011년

# 네이버에서 2011년이 nseq 몇번인지 값 확인

tmp1 <- ntpl1[which(substr(ntpl1$dweek,1,4)=='2011'),c(1,14)]

plot(as.numeric(substr(ntpl1$dweek,1,8)), ntpl1$nseq)

# 28번 부터 시작하므로

# 구글에 27을 더한값을 부여

gtclub$nseq <- c(1:nrow(gtclub))+27

plgtnt <- merge(ntpl1, gtclub, by="nseq", all.x=TRUE, all.y=TRUE)

# 앞달과 뒷달에 각각 몇일씩 걸쳐있는지

testDate <- "20120528~20120603"

# 시작일자를 date 형식으로

testDateSince <- as.Date(paste(substr(testDate,1,4), substr(testDate,5,6) ,substr

(testDate,7,8), sep="-")) 

# 마지막 일자도 date 형식으로

testDateUntil <- as.Date(paste(substr(testDate,10,13), substr(testDate,14,15) ,substr

(testDate,16,17), sep="-")) 

# 뒷달의 첫날(1일)을 date 형식으로

testDateNM1st <- as.Date(paste(substr(testDate,10,13), substr(testDate,14,15) ,"01", 

sep="-")) 

# 뒷달에 포함된 기간이 얼마의 비율을 차지하는지?

as.numeric(testDateUntil - testDateNM1st +1 )/7

#-----------------

#------- 넘어간 비율 계산 ---- 

head(plgtnt, 2)

plgtnt$is_samem<-1

plgtnt$r_leftover<-0

plgtnt$is_samem <- ifelse(substr(as.character(plgtnt$dweek),5,6) == substr(as.character(plgtnt$dweek),14,15),1,0)

plgtnt$testDateSince <- as.Date(paste(substr(plgtnt$dweek,1,4), substr(plgtnt$dweek,5,6) ,substr(plgtnt$dweek,7,8), sep="-")) 

# 마지막 일자도 date 형식으로

plgtnt$testDateUntil <- as.Date(paste(substr(plgtnt$dweek,10,13), substr(plgtnt$dweek,14,15) ,substr(plgtnt$dweek,16,17), sep="-")) 

# 뒷달의 첫날(1일)을 date 형식으로

plgtnt$testDateNM1st <- as.Date(paste(substr(plgtnt$dweek,10,13), substr(plgtnt$dweek,14,15) ,"01", sep="-")) 

# 뒷달에 포함된 기간이 얼마의 비율을 차지하는지?

plgtnt$r_leftover <- as.numeric(plgtnt$testDateUntil - plgtnt$testDateNM1st +1 )/7

plgtnt$dmonth <- substr(as.character(plgtnt$dweek),1,6)

head(plgtnt[,c(1,2,15:16,26:27)],4)

plgtntlo <- plgtnt[which(plgtnt$is_samem==0),]

plgtnt$wt <- ifelse(plgtnt$is_samem==1, 1, 1-plgtnt$r_leftover)

plgtntlo$wt <- plgtntlo$r_leftover

plgtntlo$dmonth <- substr(as.character(plgtntlo$dweek),10,15)

require(sqldf)

plgtnt4m <- rbind(plgtnt, plgtntlo)

msum_gtnt <- sqldf('select dmonth, sum(chelsea_ntmbk * wt) as wted_sum, sum(wt) as wtsum, sum(chelsea_ntmbk * wt)/ sum(wt) as wted_chelsea_ntmbk from plgtnt4m group by dmonth order by dmonth')

mean(plgtnt$chelsea_ntmbk. na.rm=TRUE)