In [1]:
import pandas as pd, numpy as np, json
from matches_loader import *
from members_loader import *
from clubs_loader import *
from point_utils import *

In [8]:
members=get_members('../data/manual/Evidenta membrilor.xlsm')

In [18]:
data={}
for i in members.T.iteritems():
    grades=i[1][['1 kyu','1 dan','2 dan','3 dan','4 dan','5 dan',
                 '6 dan','7 dan','8 dan']].replace('x',pd.NaT).dropna()
    grades0=i[1][['1 dan','2 dan','3 dan','4 dan','5 dan',
                  '6 dan','7 dan','8 dan']].replace('x',pd.NaT).dropna()
    df=pd.DataFrame(grades0)
    df.columns=['dan']
    df=df.reset_index().set_index('dan').sort_index()
    dummy={}
    grades=pd.to_datetime(grades.astype(str))
    if len(grades)>0:
        mingrade=grades.min().year
        maxgrade=grades.max().year
    else:
        mingrade=np.nan
        maxgrade=np.nan

    if np.isnan(mingrade):
        mingrade=2016 #default starting year
    maxyear=2019 #default max year

    dummy['name']=i[1]['Numele']+' '+i[1]['Prenumele']
    dummy['birth']=str(i[1]['Data naşterii'])[:10]
    dummy['gen']=i[1]['Gen']
    dummy['ekf']=i[1]['Nr. EKF']
    dummy['active']=i[1][231]
    club=i[1]['Club']
    dummy['transfer']=get_transfer(dummy['name'],i[1]['Unnamed: 3'])

    for year in range(mingrade,maxyear):

        #get year from exams
        dummy['dan']=len(df[:str
                            (year)])
        #get club from transfers
        clubs=clubs_loader.get_club_by_year(dummy['transfer'],club,year,mingrade,maxyear)
        clubs=clubs[:1] #remove this step to double count. this limits to first club in transfer years
        for j in range(len(clubs)):
            iclub=clubs_loader.replacer(clubs[j])
            dummy['club']=iclub
            dummy['pretty_club']=clubs_loader.pretty(iclub)
            dummy['age']=year-1-pd.to_datetime(dummy['birth']).year
            data=clubs_loader.add_to_club(data,iclub,year,dummy.copy())

all_data=[]

for club in data:
    for year in data[club]:
        df=pd.DataFrame(data[club][year])
        df['year']=year
        df['club']=club
        df=df.drop('transfer',axis=1)
        all_data.append(df)

members_clean=pd.concat(all_data).reset_index(drop=True)

In [19]:
members_clean


Out[19]:
active age birth club dan ekf gen name pretty_club year
0 Free 27.0 1979-06-06 KYO 0 RO.00076 F Abe (Carțiș) Emilia Kyobukan 2007
1 Transferat NaN NaT KYO 1 RO.00027 M Amurațiu Ștefăniță Kyobukan 2007
2 Inactiv NaN NaT KYO 0 NaN M Călănică Cristian Kyobukan 2007
3 Activ 36.0 1970-10-21 KYO 2 RO.00028 F Grapă Daniela Kyobukan 2007
4 Inactiv 34.0 1972-10-26 KYO 2 RO.00039 F Hulea Violeta Kyobukan 2007
5 Inactiv NaN NaT KYO 0 NaN M Iacob Vasile Kyobukan 2007
6 Activ 25.0 1981-07-27 KYO 3 RO.00065 M Józsa Levente Kyobukan 2007
7 Decedat NaN NaT KYO 0 NaN M Keresztes Csaba Kyobukan 2007
8 Inactiv 25.0 1981-10-18 KYO 0 RO.00079 M Kovács Alexandru Kyobukan 2007
9 Inactiv NaN NaT KYO 0 NaN M Mănciulea Sebastian Kyobukan 2007
10 Inactiv 14.0 1992-06-05 KYO 0 RO.00077 F Mureșan (Egri) Melinda Kyobukan 2007
11 Inactiv 29.0 1977-06-23 KYO 1 RO.00034 M Popa Ciprian Kyobukan 2007
12 Deleted 30.0 1976-12-03 KYO 0 RO.00084 M Ratz Bogdan Kyobukan 2007
13 AS 30.0 1976-12-03 KYO 0 RO.00090 M Ratz Bogdan Kyobukan 2007
14 Transferat 23.0 1983-09-29 KYO 0 RO.00071 F Stoica Loredana Kyobukan 2007
15 Free NaN NaT KYO 1 RO.00026 M Zahan Sorin Kyobukan 2007
16 Free 28.0 1979-06-06 KYO 0 RO.00076 F Abe (Carțiș) Emilia Kyobukan 2008
17 Transferat NaN NaT KYO 1 RO.00027 M Amurațiu Ștefăniță Kyobukan 2008
18 Inactiv NaN NaT KYO 0 NaN M Călănică Cristian Kyobukan 2008
19 Activ 37.0 1970-10-21 KYO 3 RO.00028 F Grapă Daniela Kyobukan 2008
20 Inactiv 35.0 1972-10-26 KYO 2 RO.00039 F Hulea Violeta Kyobukan 2008
21 Inactiv NaN NaT KYO 0 NaN M Iacob Vasile Kyobukan 2008
22 Activ 26.0 1981-07-27 KYO 3 RO.00065 M Józsa Levente Kyobukan 2008
23 Decedat NaN NaT KYO 0 NaN M Keresztes Csaba Kyobukan 2008
24 Inactiv 26.0 1981-10-18 KYO 1 RO.00079 M Kovács Alexandru Kyobukan 2008
25 Inactiv NaN NaT KYO 0 NaN M Mănciulea Sebastian Kyobukan 2008
26 Inactiv 15.0 1992-06-05 KYO 0 RO.00077 F Mureșan (Egri) Melinda Kyobukan 2008
27 Inactiv 30.0 1977-06-23 KYO 1 RO.00034 M Popa Ciprian Kyobukan 2008
28 Deleted 31.0 1976-12-03 KYO 0 RO.00084 M Ratz Bogdan Kyobukan 2008
29 AS 31.0 1976-12-03 KYO 0 RO.00090 M Ratz Bogdan Kyobukan 2008
... ... ... ... ... ... ... ... ... ... ...
3667 Inactiv 49.0 1968-09-27 BSD 0 NaN F Tarcza Zarug Carla Mariana Bushido 2018
3668 Inactiv 35.0 1982-08-15 BSD 0 NaN F Tütsek Kinga Bushido 2018
3669 Activ 8.0 2009-11-18 BSD 0 NaN M Veres Cioanta Iustin Bushido 2018
3670 Activ 21.0 1981-07-27 FDS 1 RO.00065 M Józsa Levente Fudoshin 2003
3671 Inactiv 28.0 1974-01-20 FDS 1 RO.00047 M Székely József-Pál Fudoshin 2003
3672 Activ 22.0 1981-07-27 FDS 1 RO.00065 M Józsa Levente Fudoshin 2004
3673 Inactiv 29.0 1974-01-20 FDS 1 RO.00047 M Székely József-Pál Fudoshin 2004
3674 Activ 23.0 1981-07-27 FDS 2 RO.00065 M Józsa Levente Fudoshin 2005
3675 Inactiv 30.0 1974-01-20 FDS 2 RO.00047 M Székely József-Pál Fudoshin 2005
3676 Inactiv 26.0 1974-01-20 FDS 1 RO.00047 M Székely József-Pál Fudoshin 2001
3677 Inactiv 27.0 1974-01-20 FDS 1 RO.00047 M Székely József-Pál Fudoshin 2002
3678 Inactiv 51.0 1964-08-17 ACKIJ 0 RO.00239 M Sakai Toshinobu ACKIJ 2016
3679 Inactiv 52.0 1964-08-17 ACKIJ 0 RO.00239 M Sakai Toshinobu ACKIJ 2017
3680 Inactiv 53.0 1964-08-17 ACKIJ 0 RO.00239 M Sakai Toshinobu ACKIJ 2018
3681 Inactiv 21.0 1978-06-22 HUN 0 RO.00178 M Szőke Sándor Hungary 2000
3682 Inactiv 22.0 1978-06-22 HUN 0 RO.00178 M Szőke Sándor Hungary 2001
3683 Inactiv 23.0 1978-06-22 HUN 0 RO.00178 M Szőke Sándor Hungary 2002
3684 Inactiv 24.0 1978-06-22 HUN 0 RO.00178 M Szőke Sándor Hungary 2003
3685 Inactiv 25.0 1978-06-22 HUN 0 RO.00178 M Szőke Sándor Hungary 2004
3686 Inactiv 26.0 1978-06-22 HUN 0 RO.00178 M Szőke Sándor Hungary 2005
3687 Inactiv 27.0 1978-06-22 HUN 0 RO.00178 M Szőke Sándor Hungary 2006
3688 Inactiv 28.0 1978-06-22 HUN 0 RO.00178 M Szőke Sándor Hungary 2007
3689 Inactiv 29.0 1978-06-22 HUN 0 RO.00178 M Szőke Sándor Hungary 2008
3690 Inactiv 30.0 1978-06-22 HUN 0 RO.00178 M Szőke Sándor Hungary 2009
3691 Inactiv 31.0 1978-06-22 HUN 0 RO.00178 M Szőke Sándor Hungary 2010
3692 Inactiv 32.0 1978-06-22 HUN 0 RO.00178 M Szőke Sándor Hungary 2011
3693 Inactiv 33.0 1978-06-22 HUN 0 RO.00178 M Szőke Sándor Hungary 2012
3694 Inactiv 34.0 1978-06-22 HUN 0 RO.00178 M Szőke Sándor Hungary 2013
3695 Inactiv 35.0 1978-06-22 HUN 0 RO.00178 M Szőke Sándor Hungary 2014
3696 Inactiv 39.0 1978-06-22 HUN 2 RO.00178 M Szőke Sándor Hungary 2018

3697 rows × 10 columns


In [13]:
grades.astype('time')


---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-13-e4cbeb3de608> in <module>()
----> 1 grades.astype('time')

~\AppData\Local\Continuum\anaconda2\envs\python3\lib\site-packages\pandas\util\_decorators.py in wrapper(*args, **kwargs)
    176                 else:
    177                     kwargs[new_arg_name] = new_arg_value
--> 178             return func(*args, **kwargs)
    179         return wrapper
    180     return _deprecate_kwarg

~\AppData\Local\Continuum\anaconda2\envs\python3\lib\site-packages\pandas\core\generic.py in astype(self, dtype, copy, errors, **kwargs)
   4999             # else, only a single dtype is given
   5000             new_data = self._data.astype(dtype=dtype, copy=copy, errors=errors,
-> 5001                                          **kwargs)
   5002             return self._constructor(new_data).__finalize__(self)
   5003 

~\AppData\Local\Continuum\anaconda2\envs\python3\lib\site-packages\pandas\core\internals.py in astype(self, dtype, **kwargs)
   3712 
   3713     def astype(self, dtype, **kwargs):
-> 3714         return self.apply('astype', dtype=dtype, **kwargs)
   3715 
   3716     def convert(self, **kwargs):

~\AppData\Local\Continuum\anaconda2\envs\python3\lib\site-packages\pandas\core\internals.py in apply(self, f, axes, filter, do_integrity_check, consolidate, **kwargs)
   3579 
   3580             kwargs['mgr'] = self
-> 3581             applied = getattr(b, f)(**kwargs)
   3582             result_blocks = _extend_blocks(applied, result_blocks)
   3583 

~\AppData\Local\Continuum\anaconda2\envs\python3\lib\site-packages\pandas\core\internals.py in astype(self, dtype, copy, errors, values, **kwargs)
    573     def astype(self, dtype, copy=False, errors='raise', values=None, **kwargs):
    574         return self._astype(dtype, copy=copy, errors=errors, values=values,
--> 575                             **kwargs)
    576 
    577     def _astype(self, dtype, copy=False, errors='raise', values=None,

~\AppData\Local\Continuum\anaconda2\envs\python3\lib\site-packages\pandas\core\internals.py in _astype(self, dtype, copy, errors, values, klass, mgr, **kwargs)
    634 
    635         # astype processing
--> 636         dtype = np.dtype(dtype)
    637         if self.dtype == dtype:
    638             if copy:

TypeError: data type "time" not understood