In [1]:
import lib.dbUtil as dbUtil
import lib.cardset as cs
import pandas as pd
import numpy as np
from datetime import datetime, date, time, timedelta
import pprint
import pickle
from tqdm import tqdm, tqdm_notebook
import sys
import os
import matplotlib.pyplot as plt
In [2]:
DATA_DIR = "../data/chipswon"
In [3]:
chipsWonByCards_Q = '''
select
t_e.pid, chips_won, cards, t_e.genus, game_time
from
(select
pid,
abs(sum(count)) as chips_won,
min(time) as game_time,
genus
from
m_table_Economy_cash
where
time between '${date}$' - 1 and '${date}$' and
family like 'normal%' and
class = 'public' and
game_id = 3 and
pid = 10038036527032 and
count < 0
group by
pid, genus) t_e
join
(select
pid,
genus,
family,
class as cards
from
m_table_count_game_action
where
phylum like 'see' and
time between '${date}$' - 1 and '${date}$' and
family like 'normal%' and
game_id = 3 ) t_ga
on t_e.pid = t_ga.pid and t_e.genus = t_ga.genus
order by game_time asc
'''
In [4]:
#final query for pid, chips invested, num players, num_chaals, num_blinds, level
chips_invested_by_Cards_Q = '''
select t7.pid, t7.genus, abs(t7.chips_invested),t7.num_players, t7.lvl, t7.num_blinds, t7.num_chaals, t7.game_time, nvl(cards, 'none') as cards
from
(select t5.*, num_chaals, num_blinds
from
(select t3.*, lvl
from
(select t1.*, t2.num_players
from
(select
pid, genus, abs(sum(count)) as chips_invested, min(time) as game_time
from m_table_Economy_cash
where
time between '${date}$' - 1 and '${date}$' and family like 'normal_11' and game_id = 3
group by pid, genus) as t1
join
(select genus,count(distinct pid) as num_players
from m_table_Economy_Cash
where
time between '${date}$' - 1 and '${date}$' and family like 'normal_11' and game_id = 3
group by genus) as t2
on t1.genus = t2.genus) as t3
join
(select pid , max(count) as lvl from m_table_count_wallet where kingdom = 'level' and time between '${date}$' -1 and '${date}$' group by pid) as t4
on t3.pid = t4.pid) as t5
join
(select pid, genus, num_chaals, num_blinds from m_Table_tpg_cash where time between '${date}$' - 1 and '${date}$' and family like 'normal_11' and phylum = 'game' ) as t6
on t5.pid = t6.pid and t5.genus = t6.genus) as t7
left join
(select
pid,
genus,
class as cards
from
m_table_count_game_action
where
phylum like 'see' and
time between '${date}$' - 1 and '${date}$' and
family like 'normal_11' and
game_id = 3 ) as t8
on t7.pid = t8.pid and t7.genus = t8.genus
'''
In [5]:
# get card Strength from cards
def getCardStrength(cards):
if cards == 'none':
return 0
cl = cards.split(":")
co = cs.CardSet()
return co.getCardStrengthV2([int(cl[0]), int(cl[1]), int(cl[2])])
def getDateTime(dt_str):
dtl = dt_str.split(" ")
dl = dtl[0].split("-")
tl = dtl[1].split(":")
return datetime(year = int(dl[0]), month = int(dl[1]), day = int(dl[2]), hour = int(tl[0]), minute = int(tl[1]), second = int(tl[2]))
# get visual for cards by chips won
def chipsWonByCards(startDate, save = True, loadExisting = False):
if loadExisting == True:
return pd.read_csv(os.path.join(DATA_DIR, str(startDate) + ".csv"))
data = []
cols = ['pid', 'chips_won', 'cards', 'genus', 'game_time']
q = chipsWonByCards_Q.replace('${date}$', str(startDate))
count = 0
result = dbUtil.runQuery(q)
count = count+1
total = result.rowcount
row = result.fetchone()
while row != None:
rowData = (row[0], np.log(int(row[1])), getCardStrength(row[2]), row[3], row[4])
data.append(rowData)
print(("Done: ", count, total))
count = count+1
row = result.fetchone()
df = pd.DataFrame(data, columns = cols)
if save == True:
df.to_csv(os.path.join(DATA_DIR, str(startDate) + ".csv"))
return df
# get visual for cards by chips won
def chips_invested_by_Cards(startDate, save = True, loadExisting = False):
if loadExisting == True:
return pd.read_csv(os.path.join(DATA_DIR, str(startDate) + ".csv"))
data = []
cols = ['pid', 'chips_invested', 'num_players', 'level', 'num_blinds', 'num_chaals', 'cards']
q = chips_invested_by_Cards_Q.replace('${date}$', str(startDate))
count = 0
result = dbUtil.runQuery(q)
count = count+1
total = result.rowcount
row = result.fetchone()
while row != None:
#print(row)
rowData = (row[0], np.log(int(row[2])),int(row[3]), int(row[4]), int(row[5]), int(row[6]), getCardStrength(row[8]))
data.append(rowData)
#print(("Done: ", count, total))
count = count+1
row = result.fetchone()
df = pd.DataFrame(data, columns = cols)
if save == True:
df.to_csv(os.path.join(DATA_DIR, str(startDate) + ".csv"))
return df
In [ ]:
dt = date(2017, 11, 6)
df = chips_invested_by_Cards(dt)
In [ ]:
def plotMultiAxes(df, x, y1, y2):
# [TODO] plot without time duration difference, only maintain order
df['time_int'] = df[x].astype(np.int64)
fig, ax = plt.subplots()
ax2 = ax.twinx()
ax.plot(df['time_int'], df[y1], color='r')
ax.set_xlabel('time')
ax.set_ylabel('y1')
ax2.plot(df['time_int'], df[y2], color='b')
ax2.set_ylabel('y2')
plt.show()
def plotScatter(df):
# show scatter plot with genus vs log chips invested
pass
In [64]:
plotMultiAxes(df, 'game_time','cards', 'chips_won' )
In [7]:
def getData(startDate):
if os.path.exists(os.path.join(DATA_DIR, str(startDate) + ".csv")):
return pd.read_csv(os.path.join(DATA_DIR, str(startDate) + ".csv"))
else:
return None
In [8]:
%matplotlib inline
df = getData(date(2017, 11, 5))
In [9]:
plt.scatter(df.cards, df.chips_invested)
Out[9]:
In [10]:
plt.show()
In [ ]: