R Notebook

tryCatch({
    drv <- dbDriver("PostgreSQL")
    print("Connecting to database")
    conn <- con
    print("Connected!")
    },
    error=function(cond) {
            print("Unable to connect to database.")
    })

## Run querys to look thru db 
query_1 <- dbGetQuery(conn, 'SELECT * FROM information_schema.tables ')
query_2 <- dbGetQuery(conn, 'SELECT * FROM pg_stat_all_indexes ')
query_5 <- dbGetQuery(conn, 'SELECT * FROM pg_stat_all_tables ')
query_3 <- dbGetQuery(conn, 'SELECT * FROM player_notes')
query_4 <- dbGetQuery(conn, 'SELECT * FROM handhistories limit 10')


# query 6 returns player stats but with only sn
# query 7 has id and sn 
query_6 <- dbGetQuery(conn, 'SELECT * FROM players')
query_7 <- dbGetQuery(conn, 'SELECT * FROM compiledplayerresults limit 1000000')

wsop_players <- query_6%>% 
    filter(.,pokersite_id==12) %>% 
    select(.,c(playername,player_id,cashhands))
## extract player ids
player_ids <- wsop_players$player_id
## remove my player id
remove <- c(32587)
player_ids <- player_ids[!player_ids == remove] 

## filter other df by player ids from last filtered query
wsop_player_stats <- query_7 %>% 
    filter(.,player_id %in% player_ids )

## merge dfs on player id
merged_wsop_db<- merge(wsop_player_stats,wsop_players,by="player_id" ,all = TRUE)

  


## choosen columns 
columns_for_analysis <-c("player_id","totalhands","totalbbswon","vpiphands","pfrhands","couldcoldcall","didcoldcall","couldthreebet","didthreebet","couldsqueeze","didsqueeze","facingtwopreflopraisers","calledtwopreflopraisers","raisedtwopreflopraisers","smallblindstealattempted","smallblindstealdefended","smallblindstealreraised",
"bigblindstealattempted","bigblindstealdefended","bigblindstealreraised","facedthreebetpreflop","foldedtothreebetpreflop","calledthreebetpreflop","raisedthreebetpreflop","facedfourbetpreflop","foldedtofourbetpreflop","calledfourbetpreflop","raisedfourbetpreflop","sawflop","wonhandwhensawflop","sawshowdown","wonshowdown","flopcontinuationbetpossible","flopcontinuationbetmade","turncontinuationbetpossible","turncontinuationbetmade","rivercontinuationbetpossible","rivercontinuationbetmade","facingflopcontinuationbet","foldedtoflopcontinuationbet","calledflopcontinuationbet","raisedflopcontinuationbet","facingturncontinuationbet","foldedtoturncontinuationbet","calledturncontinuationbet","raisedturncontinuationbet","facingrivercontinuationbet","foldedtorivercontinuationbet","calledrivercontinuationbet","raisedrivercontinuationbet","playername","cashhands")    
              
## create function to round and sum vectors
 trans_func <- function(x,y){
     round(sum(x)/sum(y)*100,1)
 }

 my_wsop_finished_db <- merged_wsop_db %>% 
    select(c(columns_for_analysis)) %>%  
    group_by(playername) %>% 
    summarize(vpip= round(sum(vpiphands)/sum(totalhands)*100,1),
              pfr=round(sum(pfrhands)/sum(totalhands)*100,1),
              total_hands=sum(totalhands),
              coldcall=round(sum(didcoldcall)/sum(couldcoldcall)*100,1),
              threebet=round(sum(didthreebet)/sum(couldthreebet)*100,1),
              squeeze=round(sum(didsqueeze)/sum(couldsqueeze)*100,1),
              squeeze=round(sum(didsqueeze)/sum(couldsqueeze)*100,1),
              cold_call_3b=round(sum(calledtwopreflopraisers)/sum(facingtwopreflopraisers)*100,1),
              cold_4bet=round(sum(raisedtwopreflopraisers)/sum(facingtwopreflopraisers)*100,1),
              defends_sb=round(sum(smallblindstealdefended)/sum(smallblindstealattempted)*100,1),
              three_bet_from_sb=round(sum(smallblindstealreraised)/sum(smallblindstealattempted)*100,1),
              defends_bb=round(sum(bigblindstealdefended)/sum(bigblindstealattempted)*100,1),
              three_bet_from_bb=round(sum(bigblindstealreraised)/sum(bigblindstealattempted)*100,1),
              fold_3_bet=round(sum(foldedtothreebetpreflop)/sum(facedthreebetpreflop)*100,1),
              four_bet=round(sum(raisedthreebetpreflop)/sum(facedthreebetpreflop)*100,1),
              fold_4_bet=round(sum(foldedtofourbetpreflop)/sum(facedfourbetpreflop)*100,1),
              five_bet=round(sum(raisedfourbetpreflop)/sum(facedfourbetpreflop)*100,1),
              wwsf=round(sum(wonhandwhensawflop)/sum(sawflop)*100,1),
              won_sd=round(sum(wonshowdown)/sum(sawshowdown)*100,1),
              flop_cbet=round(sum(wonshowdown)/sum(sawshowdown)*100,1),
              turn_cbet=trans_func(turncontinuationbetmade,turncontinuationbetpossible),
              river_cbet=trans_func(rivercontinuationbetmade,rivercontinuationbetpossible),
              fold_flob_cbet=trans_func(foldedtoflopcontinuationbet,facingflopcontinuationbet),
              fold_turn_cbet=trans_func(foldedtoturncontinuationbet,facingturncontinuationbet),
              fold_river_cbet=trans_func(foldedtorivercontinuationbet,facingrivercontinuationbet),
              
              raise_flop_cbet=trans_func(raisedflopcontinuationbet,facingflopcontinuationbet),
              raise_turn_cbet=trans_func(raisedturncontinuationbet,facingturncontinuationbet),
              raise_river_cbet=trans_func(raisedrivercontinuationbet,facingrivercontinuationbet))
 
              

kable(head(my_wsop_finished_db,100))