To read and parse the data from Track-o-Bot


In [1]:
from collections import namedtuple
import sqlite3

DROP_ALL_TABLES = False

Dora R. is a great investigator, and she has access to a large database that she is always checking and she can add and take from it at any time. Her database is called "A gazeta de Geringontzan" - os simply "agazeta.db"


In [2]:
conn = sqlite3.connect('agazeta.db')

In [3]:
c = conn.cursor()

Dora R. has a setup, in case she is creating a new database she enters those small keys to have some meat in her sandwich.

Also, she is going to run her investigation skills to understand what is happening on the historics of people, but first things have to work.

Dora starts by seeing if she can get back the first 2 api's from the data base.


In [44]:
try:
    c.execute('SELECT * FROM apikeys LIMIT 1')
except Exception, e:
    c.execute( 'CREATE TABLE apikeys (id integer primary key, username text, token text, working integer, email text, subscribed integer)')

print 'Look! It is working! I have the top two api usernames and tokens from a gazeta!'
#for row in c.execute('SELECT username FROM apikeys LIMIT 2'):
#    print row


Look! It is working! I have the top two api usernames and tokens from a gazeta!

She is very excited, it is working! First thing she grabs her pen and paper and starts to look for special things on the api's, maybe she would find something interesting.


In [5]:
APIkey = namedtuple('APIkey', ['username', 'token'])
for row in c.execute('SELECT username, token FROM apikeys WHERE working == 1'):
    APIkey.username = row[0]
    APIkey.token = row[1]

She gets everything read with the last of the username and tokens


In [6]:
get = 'https://trackobot.com/profile/history.json?page={page}&username={username}&token={token}'.format(page=1,
                                                                                            username=APIkey.username,
                                                                                            token=APIkey.token)

The file has to be loaded from a json online

Setting up the system to read JSON files from web


In [7]:
import urllib
import json

Dora R. hates when her data comes with a "u" in front, it gets messy and hard to read, so it is pretty much manicure.


In [8]:
def json_load_byteified(file_handle):
    return _byteify(
    json.load(file_handle, object_hook = _byteify),
    ignore_dicts = True
    )

def json_loads_byteified(json_text):
    return _byteify(
        json.loads(json_text, object_hook = _byteify),
        ignore_dicts = True
    )

def _byteify( data, ignore_dicts = False):
    if isinstance(data, unicode):
        return data.encode('utf-8')
    
    if isinstance(data, list):
        return [ _byteify( item, ignore_dicts=True) for item in data ]
    
    if isinstance(data, dict) and not ignore_dicts:
        return {
            _byteify(key, ignore_dicts=True): _byteify(value, ignore_dicts=True)
            for key, value in data.iteritems()
        }
    
    return data

In [9]:
response = urllib.urlopen(get)

In [10]:
resp = response.read()

In [11]:
data = json_loads_byteified(resp)

Dora R. now wants to see what is inside the 'data' json, its keys and values


In [12]:
data.keys()


Out[12]:
['meta', 'history']

In [13]:
data['meta'].keys()


Out[13]:
['total_items', 'next_page', 'current_page', 'total_pages', 'prev_page']

In [14]:
data['meta']


Out[14]:
{'current_page': 1,
 'next_page': 2,
 'prev_page': None,
 'total_items': 917,
 'total_pages': 62}

This is the best thing she could find! Here Dora can see how many pages there are, so she can use it to 'spider' around the data. Her eyes shine when she sees all that many possibilities, all those secrets!


In [15]:
data['history'][0].keys()


Out[15]:
['opponent_deck',
 'added',
 'hero',
 'rank',
 'id',
 'note',
 'card_history',
 'result',
 'opponent',
 'duration',
 'hero_deck',
 'coin',
 'legend',
 'mode']

With 'history' she sees that here lies what is really important, the games!

Now she will run her small trick that returns the number of turns a game had


In [22]:
def total_turns(data, j):
    a = 0
    for i in range(len(data['history'][j]['card_history'])):
        a = data['history'][j]['card_history'][i]['turn'] if data['history'][j]['card_history'][i]['turn'] > a else a
    return a



If she wanted to see who won and with which deck, she would need to do this...


In [23]:
print data['history'][0]['id'],'-', data['history'][0]['added'], data['history'][0]['mode']
print data['history'][0]['hero'], data['history'][0]['hero_deck'], 'vs', data['history'][0]['opponent'], data['history'][0]['opponent_deck']
print 'First player' if not data['history'][0]['coin'] else 'Second player'
print 'Turns', total_turns(data, 0)
print data['history'][0]['result']


52323071 - 2016-12-04T04:44:43.000Z ranked
Druid None vs Paladin None
First player
Turns 10
win

Dora R. is also curious about the cards that were used during the game


In [24]:
data['history'][0]['card_history']


Out[24]:
[{'card': {'id': 'GAME_005', 'mana': None, 'name': 'The Coin'},
  'player': 'opponent',
  'turn': 1},
 {'card': {'id': 'CS2_013', 'mana': 2, 'name': 'Wild Growth'},
  'player': 'me',
  'turn': 2},
 {'card': {'id': 'CS2_101', 'mana': 2, 'name': 'Reinforce'},
  'player': 'opponent',
  'turn': 2},
 {'card': {'id': 'EX1_169', 'mana': 0, 'name': 'Innervate'},
  'player': 'me',
  'turn': 3},
 {'card': {'id': 'BRM_028', 'mana': 6, 'name': 'Emperor Thaurissan'},
  'player': 'me',
  'turn': 3},
 {'card': {'id': 'EX1_619', 'mana': 2, 'name': 'Equality'},
  'player': 'opponent',
  'turn': 3},
 {'card': {'id': 'OG_044', 'mana': 4, 'name': 'Fandral Staghelm'},
  'player': 'me',
  'turn': 4},
 {'card': {'id': 'LOE_115', 'mana': 1, 'name': 'Raven Idol'},
  'player': 'me',
  'turn': 4},
 {'card': {'id': 'LOE_115', 'mana': 1, 'name': 'Raven Idol'},
  'player': 'me',
  'turn': 4},
 {'card': {'id': 'NEW1_021', 'mana': 2, 'name': 'Doomsayer'},
  'player': 'opponent',
  'turn': 4},
 {'card': {'id': 'CS2_101', 'mana': 2, 'name': 'Reinforce'},
  'player': 'opponent',
  'turn': 4},
 {'card': {'id': 'CS2_012', 'mana': 4, 'name': 'Swipe'},
  'player': 'me',
  'turn': 5},
 {'card': {'id': 'CS2_017', 'mana': 2, 'name': 'Shapeshift'},
  'player': 'me',
  'turn': 5},
 {'card': {'id': 'EX1_007', 'mana': 3, 'name': 'Acolyte of Pain'},
  'player': 'opponent',
  'turn': 5},
 {'card': {'id': 'EX1_164', 'mana': 5, 'name': 'Nourish'},
  'player': 'me',
  'turn': 6},
 {'card': {'id': 'OG_047', 'mana': 3, 'name': 'Feral Rage'},
  'player': 'me',
  'turn': 6},
 {'card': {'id': 'CS2_017', 'mana': 2, 'name': 'Shapeshift'},
  'player': 'me',
  'turn': 6},
 {'card': {'id': 'EX1_154', 'mana': 2, 'name': 'Wrath'},
  'player': 'me',
  'turn': 7},
 {'card': {'id': 'CS2_017', 'mana': 2, 'name': 'Shapeshift'},
  'player': 'me',
  'turn': 7},
 {'card': {'id': 'LOE_115', 'mana': 1, 'name': 'Raven Idol'},
  'player': 'me',
  'turn': 7},
 {'card': {'id': 'NEW1_021', 'mana': 2, 'name': 'Doomsayer'},
  'player': 'opponent',
  'turn': 7},
 {'card': {'id': 'CS2_012', 'mana': 4, 'name': 'Swipe'},
  'player': 'me',
  'turn': 8},
 {'card': {'id': 'EX1_383', 'mana': 8, 'name': 'Tirion Fordring'},
  'player': 'opponent',
  'turn': 8},
 {'card': {'id': 'AT_044', 'mana': 3, 'name': 'Mulch'},
  'player': 'me',
  'turn': 9},
 {'card': {'id': 'CS2_017', 'mana': 2, 'name': 'Shapeshift'},
  'player': 'me',
  'turn': 9},
 {'card': {'id': 'OG_198', 'mana': 0, 'name': 'Forbidden Healing'},
  'player': 'opponent',
  'turn': 9},
 {'card': {'id': 'CS2_012', 'mana': 4, 'name': 'Swipe'},
  'player': 'me',
  'turn': 10}]

Now Dora is much more confident, she wants to get all the ranked games from this data and this page!


In [25]:
# Only ranked games
for i in range(len(data['history'])):
    if data['history'][i]['mode'] != 'ranked':
        continue
    print data['history'][i]['id'],'-', data['history'][i]['added']
    print data['history'][i]['hero'], data['history'][i]['hero_deck'], 'vs', data['history'][i]['opponent'], data['history'][0]['opponent_deck']
    print 'First player' if not data['history'][i]['coin'] else 'Second player'
    print 'Turns', total_turns(data, i)
    print data['history'][i]['result']


52323071 - 2016-12-04T04:44:43.000Z
Druid None vs Paladin None
First player
Turns 10
win
52259065 - 2016-12-03T20:25:26.000Z
Druid None vs Warlock None
First player
Turns 10
win
52217435 - 2016-12-03T16:33:11.000Z
Druid None vs Paladin None
Second player
Turns 9
win
52215440 - 2016-12-03T16:22:23.000Z
Druid None vs Priest None
Second player
Turns 8
win
52213719 - 2016-12-03T16:13:20.000Z
Druid None vs Priest None
First player
Turns 17
loss
52210934 - 2016-12-03T15:57:33.000Z
Druid None vs Warlock None
Second player
Turns 6
win
52210016 - 2016-12-03T15:52:28.000Z
Druid None vs Shaman None
First player
Turns 9
win
52200956 - 2016-12-03T15:02:49.000Z
Druid None vs Warlock None
Second player
Turns 9
win
52199200 - 2016-12-03T14:52:56.000Z
Druid None vs Priest None
First player
Turns 14
loss
52196975 - 2016-12-03T14:40:49.000Z
Druid None vs Warrior None
Second player
Turns 6
loss
52195523 - 2016-12-03T14:33:05.000Z
Mage None vs Warrior None
Second player
Turns 16
loss
52192738 - 2016-12-03T14:17:44.000Z
Mage None vs Paladin None
Second player
Turns 12
loss
52190389 - 2016-12-03T14:03:38.000Z
Mage None vs Rogue None
First player
Turns 8
loss
52188929 - 2016-12-03T13:55:06.000Z
Mage None vs Hunter None
Second player
Turns 10
win
52187721 - 2016-12-03T13:48:08.000Z
Mage None vs Paladin None
First player
Turns 12
win

It all seens perfect, with just one problem, the datetime is weird... she will have to convert it befor being able to add it to the database.


In [26]:
data['history'][0]['added']


Out[26]:
'2016-12-04T04:44:43.000Z'

In [27]:
from datetime import datetime

datetime.strptime('2016-12-02T02:29:09.000Z', '%Y-%m-%dT%H:%M:%S.000Z')


Out[27]:
datetime.datetime(2016, 12, 2, 2, 29, 9)

Dora R. prefers to work with POSIX dates, she says that they are better to make searchs in the tables... maybe she does not know of the julian date calendar.


In [28]:
import time

d = datetime.strptime(data['history'][0]['added'], '%Y-%m-%dT%H:%M:%S.000Z')
print str(int(time.mktime(d.timetuple())))+'!!!', 
print "POSIX seconds! This will help alot when I try to find my data in time windows!"


1480833883!!! POSIX seconds! This will help alot when I try to find my data in time windows!

Hell yes! Her tricks do work perfectly, now she can enter the date and time in a more sane order on the database!

Creating games database into the A Gazeta

Dora R. has enough to be sure that it is possible to get the data that she wants out of the api, now she needs to add into the database.

The API and Keys table is simple, just username and token, along with a working statement, so in case a player regenerates the value we may set this flag as 0 to 'unusable', she finds out it would be great to have the email of the person in the database, so she can talk with the person ans kindly asks for her to donate the key again, and to send her findings directly into the email if they are subscribed.

  • CREATE TABLE apikeys (id integer primary key, username text, token text, working integer, email text, subscribed integer)

Now she needs a table for her findings... she will add the game results from a date window from an apikey and load it up to the database, first it will need an ID, for each new entry, also, she will parse the data instead of loading JSON's, she won't be running it relational, but she need the speed and reliability of a proper SQL machine. Also, she is not going to store games that are NOT ranked, casual and arena games can skew too much the data.

  • CREATE TABLE archive (id integer primary key, matchid integer, date-posix integer, rank integer, hero text, hero_deck text, opponent_hero text, opponent_deck text, coin integer, turns integer, result integer, cards text, opponent_cards text)

In [29]:
try:
    c.execute('SELECT * FROM archive LIMIT 1')
    print 'Yeap, working'
except Exception, e:
    c.execute('''CREATE TABLE archive (id integer primary key, matchid integer, 
                 date_posix integer, rank integer, hero text, hero_deck text, opponent_hero text, opponent_deck text, 
                 coin integer, turns integer, result integer, cards text, opponent_cards text)''')
    print 'Table archive is done!'


Yeap, working

The setup is good, now Dora R. must take care so she does not add duplicated games to the archive.

First she will make a process that will run through the apikeys and then it will capture the games from each layer and pipeline it to another process that will write it into the archive.


In [30]:
querie = 'SELECT username, token FROM apikeys WHERE working == 1'

In [31]:
def api_getter (verbose=False, limit = 0):
    APIkey = namedtuple('APIkey', ['username', 'token'])
    querie = 'SELECT username, token FROM apikeys WHERE working == 1'
    if limit:
        querie = 'SELECT username, token FROM apikeys WHERE working == 1 LIMIT {limit}'.format(limit=limit)
    
    sqlret = [sqlret for sqlret in c.execute(querie)]
    
    for row in sqlret:
        APIkey.username = row[0]
        APIkey.token = row[1]
        if verbose:
            print '{username} {token}'.format(username=APIkey.username, token=APIkey.token)
        yield (row[0], row[1])

def posix_conversion(date = '2016-12-02T02:29:09.000Z'):
    d = datetime.strptime( date, '%Y-%m-%dT%H:%M:%S.000Z')
    return int(time.mktime(d.timetuple()))

In [32]:
def Maexxna(from_date, limit=0, verbose=False):
    # from_date = datetime
    from_date = time.mktime(from_date.timetuple())
    get = 'https://trackobot.com/profile/history.json?page={page}&username={username}&token={token}'
    for api in api_getter(limit = limit):
        page = 1
        end = False
        try_and_error = 3
        data_history =[]
        
        while(True):
            try:
                response = urllib.urlopen(get.format(page=page, username=api[0], token=api[1]))
                try_and_error = 3
            except Exception as e:
                print 'error', e
                if try_and_error:
                    try_and_error -= 1
                    continue
                else:
                    break
            
            resp = response.read()
            data = json_loads_byteified(resp)                    
            
            if verbose:
                print 'game historic', len(data['history']),
                
            for n in range(len(data['history'])):
                if posix_conversion(data['history'][n]['added']) < from_date:
                    del data['history'][n:len(data['history'])]
                    end = True
                    break
                
            if verbose: print 'valid games',len(data['history'])
                
            data_history += data['history']
                
            # end of the loop
            if data['meta']['total_pages'] == data['meta']['current_page'] or end:
                break
            page += 1
        yield data_history

Maexxna is ready to run, Dora R. makes a last check to see if it can return stuff properly.


In [33]:
from datetime import timedelta

d = datetime.today() - timedelta(days=7)

for i in Maexxna(from_date=d):
    print len(i)
    
print 'Done'


2
85
42
Done

'It is working perfectly!!!' Dora yells, her function works and returns what she needs, altought it may return "empty" history, but thats something she may deal with without much problem. Now she must add those informations to the archive


In [34]:
import pandas as pd

def Starseeker(verbose = False, iterator = False):

    def _turns(data):
        a = 0
        for i in range(len(data)):
            a = data[i]['turn'] if data[i]['turn'] > a else a
        return a

    
    for files in Maexxna(from_date = datetime.today() - timedelta(days=7)):
        df = pd.DataFrame(columns=['id','matchid', 'date_posix', 'rank', 'hero', 'hero_deck',
                                   'opponent_hero', 'opponent_deck', 'coin', 'turns', 'result', 
                                   'cards', 'opponent_cards'
                                  ])
        index = 0
            
        match = []
        for i in range(len(files)):
            # Only ranked games are going to be stored
            if files[i]['mode'] != 'ranked':
                continue
            game = files[i]
            
            my_cards = []
            opponent_cards = []
            for card in game['card_history']:
                if card['player'] == 'me':
                    my_cards.append(card['card']['id'])
                else:
                    opponent_cards.append(card['card']['id'])
            my_cards = ', '.join(my_cards)
            opponent_cards = ', '.join(opponent_cards)

            df.loc[index] = [ int(game['id']),int(game['id']), posix_conversion(game['added']), 
                    None if game['rank'] is None else int(game['rank']) , game['hero'], game['hero_deck'],
                    game['opponent'], game['opponent_deck'], 1 if game['coin'] else 0,  
                    _turns(game['card_history']), 1 if game['result'] == 'win' else 0, 
                    my_cards, opponent_cards ]
            
            df = df.fillna(method='ffill')
            
            index += 1
        
        match = map(tuple, df.values)
        
        sql = 'INSERT OR REPLACE INTO archive VALUES ((SELECT id FROM archive WHERE matchid == ?)'+',?'*12+')'
        c.executemany( sql , match)

In [35]:
Starseeker()

In [36]:
c.execute('SELECT * from archive')

[description[0] for description in c.description]


Out[36]:
['id',
 'matchid',
 'date_posix',
 'rank',
 'hero',
 'hero_deck',
 'opponent_hero',
 'opponent_deck',
 'coin',
 'turns',
 'result',
 'cards',
 'opponent_cards']

In [37]:
val = [val[1:] for val in c.execute('SELECT * from archive')]
cols = [description[0] for description in c.description]
dt = pd.DataFrame(val, columns = cols[1:])

In [38]:
dt


Out[38]:
matchid date_posix rank hero hero_deck opponent_hero opponent_deck coin turns result cards opponent_cards
0 51743061 1480454547 20 Warlock Zoo Priest C'Thun 1 13 1 OG_302, OG_113, CS2_057, OG_162, EX1_302, OG_2... CS1h_001, OG_335, AT_091, CS1_129, EX1_049, OG...
1 51739245 1480450453 20 Warlock Zoo Shaman C'Thun 0 7 0 CS2_065, EX1_596, CS2_056, OG_162, OG_281, OG_... GAME_005, EX1_258, AT_052, CS2_188, EX1_565, O...
2 52336033 1480843847 15 Rogue Miracle Mage None 1 15 0 GAME_005, EX1_134, EX1_044, KAR_069, LOE_012, ... CS2_034, LOE_002, CS2_022, NEW1_021, LOE_002t,...
3 52335114 1480843183 15 Rogue Miracle Warlock Reno 1 9 1 KAR_069, CS2_083b, CS2_083b, EX1_044, CS2_072,... EX1_302, CS2_056, CS2_057, EX1_016, LOE_023, O...
4 52334596 1480842793 15 Rogue Miracle Rogue N'Zoth 1 7 0 GAME_005, KAR_069, CS2_083b, OG_176, LOE_012, ... CS2_083b, LOE_019, CS2_072, OG_330, OG_330, EX...
5 52334239 1480842507 16 Rogue Miracle Warlock Reno 1 11 1 CS2_072, GAME_005, KAR_069, CS2_083b, OG_116, ... EX1_319, NEW1_021, LOE_023, EX1_012, CS2_056, ...
6 52333612 1480842056 16 Rogue Miracle Priest Dragon 0 5 1 EX1_044, CS2_072, CS2_083b, CS2_072, EX1_145, ... BRM_004, KAR_062, KAR_062, GAME_005, NEW1_023,...
7 52333306 1480841823 16 Rogue Miracle Paladin Aggro 0 8 1 CS2_083b, EX1_012, CS2_083b, EX1_284, CS2_072,... OG_221, OG_156, CS2_173, OG_006, EX1_508, GAME...
8 52332640 1480841318 16 Rogue Miracle Rogue N'Zoth 1 9 0 GAME_005, KAR_069, CS2_072, CS2_083b, EX1_129,... KAR_069, CS2_083b, CS2_083b, LOE_019, EX1_129,...
9 52332124 1480840926 16 Rogue Miracle Druid N'Zoth 0 9 1 KAR_069, CS2_083b, EX1_044, CS2_146, EX1_134, ... CS2_013, AT_037, EX1_154, LOE_115, CS2_017, OG...
10 52331412 1480840387 16 Rogue Miracle Druid Token 0 9 0 EX1_012, CS2_083b, CS2_072, EX1_129, EX1_284, ... CS2_013, NEW1_026, GAME_005, LOE_115, EX1_169,...
11 52330855 1480839967 17 Rogue Miracle Druid Token 1 7 1 CS2_083b, LOE_012, KAR_069, CS2_146, CS2_083b,... CS2_013, CS2_017, EX1_154, CS2_012, EX1_154, C...
12 52330510 1480839698 18 Rogue Miracle Mage Token 0 10 1 CS2_083b, CS2_083b, EX1_145, EX1_134, EX1_129,... EX1_289, CS2_034, EX1_612, EX1_287, CS2_034, C...
13 52329836 1480839172 18 Rogue Miracle Priest Dragon 0 9 1 KAR_069, CS2_083b, EX1_044, CS2_072, EX1_129, ... GAME_005, KAR_062, KAR_062, CS1h_001, EX1_043,...
14 52329342 1480838789 18 Rogue Miracle Priest Dragon 1 7 1 GAME_005, CS2_083b, EX1_012, CS2_072, CS2_083b... KAR_036, LOE_077, KAR_062, KAR_062, EX1_043, A...
15 52328972 1480838480 18 Rogue Miracle Druid Dragon 1 10 0 CS2_083b, GAME_005, CS2_146, EX1_044, CS2_072,... CS2_017, NEW1_021, CS2_012, CS2_013, CS2_017, ...
16 52328450 1480838088 19 Rogue Miracle Paladin Dragon 1 8 1 KAR_069, GAME_005, KAR_069, CS2_083b, KAR_010,... CS2_173, CS2_101_H1, LOE_077, KAR_057, EX1_619...
17 52327967 1480837720 19 Rogue Miracle Druid Dragon 1 10 1 KAR_069, CS2_083b, CS2_072, EX1_134, GAME_005,... EX1_154, CS2_008, CS2_017, OG_202, EX1_284, BR...
18 52327304 1480837229 19 Rogue Miracle Rogue Miracle 1 7 1 GAME_005, CS2_083b, EX1_012, EX1_044, EX1_284,... KAR_069, CS2_072, EX1_522, CS2_073, AT_033, CS...
19 52326871 1480836877 19 Rogue Miracle Shaman Midrange 0 10 0 EX1_124, CS2_083b, CS2_146, CS2_083b, CS2_083b... LOE_018, GAME_005, EX1_238, CS2_049, EX1_238, ...
20 52131143 1480750826 18 Priest Miracle Paladin Midrange 1 3 0 GAME_005, KAR_062, BRM_004 AT_087
21 52131020 1480750726 18 Priest Dragon Priest Control 0 17 0 CS1h_001, BRM_004, CS1h_001, KAR_035, CS1h_001... GAME_005, EX1_625, EX1_625, EX1_625t2, NEW1_02...
22 52130263 1480750106 17 Shaman Aggro Mage Freeze 1 9 0 EX1_008, GAME_005, EX1_008, LOE_018, AT_052, E... EX1_096, EX1_289, EX1_015, CS2_034, LOE_002, E...
23 52129782 1480749703 17 Druid Aggro Rogue N'Zoth 0 8 0 EX1_169, CS2_017, LOE_115, OG_044, EX1_154, CS... OG_330, OG_176, LOE_012, OG_330, LOE_019, CS2_...
24 52129368 1480749336 17 Rogue Miracle Priest Dragon 0 11 0 CS2_083b, EX1_129, LOE_012, EX1_134, EX1_012, ... GAME_005, KAR_035, LOE_077, CS2_004, CS1_112, ...
25 52128952 1480748962 18 Rogue Miracle Druid Dragon 1 7 1 GAME_005, OG_176, LOE_012, CS2_083b, EX1_145, ... CS2_017, CS2_013, CS2_012, KAR_114, CS2_017, E...
26 52128622 1480748689 18 Rogue Miracle Warlock Reno 1 7 1 GAME_005, LOE_012, CS2_072, EX1_134, EX1_581, ... CS2_056, CS2_056, EX1_008, EX1_043, EX1_043, E...
27 52128334 1480748438 18 Rogue Miracle Priest Dragon 0 11 1 CS2_083b, EX1_012, CS2_072, EX1_129, CS2_083b,... NEW1_023, GAME_005, AT_017, EX1_339, CS2_072, ...
28 52127527 1480747754 18 Shaman Midrange Rogue N'Zoth 0 12 0 KAR_063, KAR_073, KAR_114, LOE_018, KAR_073, A... CS2_083b, EX1_012, EX1_284, LOE_012, EX1_613, ...
29 52126952 1480747264 18 Shaman Midrange Warrior Pirate 1 9 0 GAME_005, KAR_073, AT_052, KAR_073, OG_028, EX... LOE_076, CS2_056, NEW1_011, CS2_056, EX1_604, ...
... ... ... ... ... ... ... ... ... ... ... ... ...
83 52195523 1480782785 18 Mage None Warrior Control 1 16 0 CS2_034, EX1_594, KAR_092, CS2_034, GAME_005, ... CS2_102, CS2_102, KAR_114, CS2_106, CS2_102, B...
84 52192738 1480781864 16 Mage None Paladin Control 1 12 0 EX1_096, CS2_024, CS2_034, CS2_033, OG_207, EX... CS2_101, EX1_007, CS2_097, CS2_101, LOE_017, C...
85 52190389 1480781018 16 Mage None Rogue Control 0 8 0 CS2_034, EX1_594, EX1_066, CS2_034, CS2_033, C... GAME_005, EX1_131, OG_070, AT_029, CS2_083b, K...
86 52188929 1480780506 16 Mage None Hunter Control 1 10 1 EX1_287, CS2_024, GAME_005, CS2_034, KAR_092, ... DS1h_292, KAR_006, EX1_554, KAR_005, BRM_013, ...
87 52187721 1480780088 16 Mage None Paladin Aggro 0 12 1 CS2_034, tt_010, CS2_033, KAR_037, EX1_594, CS... CS2_101_H1, OG_310, AT_074, EX1_619, EX1_382, ...
88 52183606 1480778635 16 Warlock Reno Druid Aggro 1 12 0 LOE_023, GAME_005, CS2_062, EX1_043, EX1_302, ... EX1_169, EX1_169, OG_044, LOE_115, AT_042, EX1...
89 52181472 1480777888 16 Warlock Reno Warrior Pirate 0 8 0 LOE_023, BRM_005, EX1_302, BRM_006, OG_241, CS... CS2_146, CS2_106, CS2_146, NEW1_018, NEW1_018,...
90 51924814 1480652949 16 Druid Reno Priest Control 1 14 1 GAME_005, EX1_169, EX1_284, EX1_154, CS2_012, ... OG_234, KAR_035, CS2_004, EX1_334, CS2_235, CS...
91 51922780 1480651854 16 Druid Reno Druid Ramp 0 8 1 EX1_169, EX1_169, EX1_164, CS2_017, EX1_164, C... EX1_154, EX1_169, GAME_005, EX1_110, EX1_096, ...
92 51921928 1480651410 16 Druid Reno Druid Ramp 1 9 0 CS2_013, EX1_284, AT_037, EX1_164, AT_037, GAM... CS2_017, LOE_115, EX1_178, CS2_232
93 51920940 1480650873 18 Druid Reno Warrior C'Thun 0 15 1 AT_037, CS2_017, CS2_017, CS2_017, OG_047, CS2... CS2_106, OG_162, LOE_077, EX1_606, CS2_102, EX...
94 51918673 1480649563 18 Hunter Reno Warlock C'Thun 0 6 1 KAR_005, EX1_539, OG_216, DS1_070, OG_179, BRM... GAME_005, BRM_006, EX1_302, CS2_056
95 51918188 1480649263 18 Hunter Reno Priest C'Thun 0 2 0 OG_179, DS1h_292 GAME_005, AT_116
96 51918045 1480649159 18 Hunter Reno Paladin C'Thun 1 6 0 OG_179, GAME_005, KAR_005, NEW1_031, DS1h_292,... NEW1_021, EX1_382, CS2_093, CS2_097
97 51917685 1480648919 18 Hunter Midrange Mage C'Thun 1 10 1 KAR_005, GAME_005, OG_216, DS1h_292, OG_216, E... CS2_034_H1, CS2_023, CS2_034_H1, AT_004, EX1_2...
98 51916985 1480648372 18 Hunter Midrange Paladin C'Thun 0 8 1 OG_179, KAR_005, OG_216, OG_216, BRM_013, OG_1... EX1_136, EX1_096, GAME_005, CS2_093, CS2_097, ...
99 51916521 1480648022 18 Hunter Midrange Warrior Pirate 1 7 0 OG_179, GAME_005, EX1_536, DS1h_292, EX1_617, ... EX1_409, NEW1_018, EX1_409, CS2_102, CS2_112, ...
100 51916113 1480647701 18 Hunter Midrange Warrior Dragon 0 12 0 EX1_536, DS1_178, EX1_534, DS1_070, EX1_539, E... GAME_005, NEW1_023, CS2_106, EX1_604, OG_314, ...
101 51688346 1480418123 18 Warlock Reno Hunter Midrange 1 13 0 EX1_302, OG_241, GAME_005, AT_111, CS2_056, NE... OG_179, DS1h_292, NEW1_031, EX1_539, DS1_184, ...
102 51687527 1480417459 18 Warlock Reno Shaman Midrange 0 18 0 CS2_056, BRM_006, CS2_062, EX1_043, EX1_016, C... KAR_063, LOE_018, EX1_248, EX1_012, KAR_063, E...
103 51686219 1480416405 18 Warlock Reno Shaman Midrange 0 12 0 CS2_056, NEW1_021, OG_241, CS2_062, CS2_056, L... LOE_018, CS2_049, EX1_248, GAME_005, EX1_238, ...
104 51685332 1480415673 18 Warlock Reno Warlock Reno 0 10 1 LOE_023, CS2_056, AT_111, CS2_056, EX1_105, BR... CS2_056, AT_087, CS2_056, NEW1_021, GAME_005, ...
105 51684487 1480414928 18 Warlock Reno Mage Tempo 0 11 0 NEW1_021, CS2_057, CS2_056, EX1_066, CS2_056, ... NEW1_012, CS2_024, GAME_005, EX1_277, EX1_007,...
106 51683890 1480414414 18 Warlock Reno Mage Freeze 1 11 0 GAME_005, BRM_006, CS2_056, CS2_056, AT_111, L... EX1_015, NEW1_021, LOE_002, LOE_002, EX1_007, ...
107 51651472 1480385486 18 Warlock Reno Paladin Freeze 0 12 1 OG_241, LOE_023, CS2_056, EX1_302, CS2_056, EX... CS2_101, EX1_382, LOE_077, BRM_034, KAR_033, K...
108 51650308 1480384768 18 Warlock Zoo Shaman Midrange 0 9 1 EX1_319, LOE_023, OG_113, OG_241, OG_241, CS2_... GAME_005, AT_052, KAR_063, EX1_248, EX1_012, E...
109 51521503 1480306500 18 Warlock Reno Warrior Pirate 0 10 1 OG_241, CS2_056, CS2_117, CS2_056, EX1_066, CS... CS2_106, OG_312, GAME_005, OG_315, NEW1_018, C...
110 51511998 1480298888 18 Warlock Reno Hunter Pirate 1 8 1 CS2_056, CS2_117, EX1_043, OG_241, EX1_093, BR... DS1h_292, NEW1_031, KAR_114, DS1_184, EX1_536,...
111 51501331 1480292795 18 Warlock Reno Mage Tempo 0 7 1 EX1_066, CS2_056, OG_241, EX1_302, BRM_006, EX... EX1_608, EX1_277, EX1_007, CS2_023, NEW1_012, ...
112 51439888 1480266963 18 Shaman Midrange Shaman Midrange 0 12 0 KAR_063, CS2_049, CS2_049, EX1_238, EX1_575, A... EX1_066, EX1_248, EX1_565, EX1_246, KAR_073, G...

113 rows × 12 columns


In [39]:
heros = [b for b in dt['opponent_hero'].unique()]

In [40]:
for h in heros:
    _h = dt[dt['hero'] == h]['result']
    _o = dt[dt['opponent_hero'] == h]['result']
    _o = pd.Series([0 if o else 1 for o in _o.values])
    print h, _h.sum()+_o.sum(), len(_h)+len(_o), float(_h.sum()+_o.sum())/ float(len(_h)+len(_o))


Priest 10 24 0.416666666667
Shaman 17 32 0.53125
Mage 11 19 0.578947368421
Warlock 16 39 0.410256410256
Rogue 25 38 0.657894736842
Paladin 4 12 0.333333333333
Druid 15 33 0.454545454545
Warrior 8 12 0.666666666667
Hunter 7 17 0.411764705882

In [41]:
pd.Series([0 if o else 1 for o in _o.values])


Out[41]:
0    1
1    1
2    1
3    1
4    0
5    1
dtype: int64

In [42]:
decks = [b for b in dt['opponent_hero'].unique()]
for h in heros:
    _h = list(dt[dt['hero'] == h]['hero_deck'].unique())
    _o = list(dt[dt['opponent_hero'] == h]['opponent_deck'].unique())
    _h = list(set(_h+_o))
    print h, _h


Priest [u'Control', u"C'Thun", u'Miracle', u'Dragon', u'Token', u'Pirate']
Shaman [u"C'Thun", u'Midrange', u'Tempo', u'Dragon', u'Aggro', u'Zoo']
Mage [u"C'Thun", u'Midrange', u'Dragon', u'Tempo', None, u'Zoo', u'Token', u'Freeze']
Warlock [u"C'Thun", u'Midrange', u'Reno', u'Dragon', u'Zoo', u'Pirate']
Rogue [u'Control', u'Reno', u'Token', u'Aggro', u'Miracle', u"N'Zoth", u'Malygos']
Paladin [u'Control', u"C'Thun", u'Midrange', u'Tempo', None, u'Zoo', u'Aggro', u'Freeze', u'Dragon']
Druid [u'Malygos', u'Ramp', None, u'Reno', u'Zoo', u'Token', u'Aggro', u"N'Zoth", u'Dragon', u'Pirate']
Warrior [u'Control', u"C'Thun", u'Dragon', u'Aggro', u'Freeze', u'Pirate']
Hunter [u'Control', u'Midrange', u'Reno', u'Face', u'Malygos', u'Pirate']

In [43]:
list(_o) + list(_h)


Out[43]:
[u'Reno',
 u'Malygos',
 u'Midrange',
 u'Control',
 u'Pirate',
 u'Control',
 u'Midrange',
 u'Reno',
 u'Face',
 u'Malygos',
 u'Pirate']

In [ ]: