My dataframe:
df.head()
Unnamed: 0 game score home_odds draw_odds away_odds country league datetime
0 0 Sport Recife - Imperatriz 2:2 1.36 4.31 7.66 Brazil Copa do Nordeste 2020 2020-02-07 00:00:00
1 1 ABC - America RN 2:1 2.62 3.30 2.48 Brazil Copa do Nordeste 2020 2020-02-02 22:00:00
2 2 Frei Paulistano - Nautico 0:2 5.19 3.58 1.62 Brazil Copa do Nordeste 2020 2020-02-02 00:00:00
3 3 Botafogo PB - Confianca 1:1 2.06 3.16 3.5 Brazil Copa do Nordeste 2020 2020-02-02 22:00:00
4 4 Fortaleza - Ceara 1:1 2.19 2.98 3.38 Brazil Copa do Nordeste 2020 2020-02-02 22:00:00
df.describe()
Unnamed: 0
count 1.115767e+06
mean 5.574871e+05
std 3.220941e+05
min 0.000000e+00
25% 2.785455e+05
50% 5.574870e+05
75% 8.364285e+05
max 1.115370e+06
I use this code to cleanup the dataframe:
from datetime import datetime
import pandas as pd
import numpy as np
from tabulate import tabulate
start = datetime.now()
df = pd.read_csv()
df['game'] = df['game'].astype(str).str.replace('(\(\w+\))', '', regex=True)
df['league'] = df['league'].astype(str).str.replace('(\s\d+\S\d+)$', '', regex=True)
df['game'] = df['game'].astype(str).str.replace('(\s\d+\S\d+)$', '', regex=True)
df[['home_team', 'away_team']] = df['game'].str.split(' - ', expand=True, n=1)
df[['home_score', 'away_score']] = df['score'].str.split(':', expand=True)
df['away_score'] = df['away_score'].astype(str).str.replace('[a-zA-Z\s\D]', '', regex=True)
df['home_score'] = df['home_score'].astype(str).str.replace('[a-zA-Z\s\D]', '', regex=True)
df = df[df.home_score != "."]
df = df[df.home_score != ".."]
df = df[df.home_score != "."]
df = df[df.home_odds != "-"]
df = df[df.draw_odds != "-"]
df = df[df.away_odds != "-"]
m = df[['home_odds', 'draw_odds', 'away_odds']].astype(str).agg(lambda x: x.str.count('/'), 1).ne(0).all(1)
n = df[['home_score']].agg(lambda x: x.str.count('-'), 1).ne(0).all(1)
o = df[['away_score']].agg(lambda x: x.str.count('-'), 1).ne(0).all(1)
df = df[~m]
df = df[~n]
df = df[~o]
df = df[df.home_score != '']
df = df[df.away_score != '']
df = df.dropna()
df = df.loc[:, df.columns.intersection(
['datetime', 'country', 'league', 'home_team', 'away_team', 'home_odds', 'draw_odds', 'away_odds', 'home_score',
'away_score'])]
colt = {
'country': str,
'league': str,
'home_team': str,
'away_team': str,
'home_odds': float,
'draw_odds': float,
'away_odds': float,
'home_score': int,
'away_score': int
}
df = df.astype(colt)
df = df.applymap(lambda x: x.strip() if isinstance(x, str) else x)
# Cleaning data where odds are greater than 100 and less than -1 and dropping duplicates
df = df[df['home_odds'] <= 100]
df = df[df['draw_odds'] <= 100]
df = df[df['away_odds'] <= 100]
df = df.drop_duplicates(['datetime', 'home_score', 'away_score', 'country', 'league', 'home_team', 'away_team'],keep='last')
df = df.applymap(lambda x: x.strip() if isinstance(x, str) else x)
df.to_csv()
time_taken = end - start
print('Time taken to complete: ', time_taken)
df.head()
home_odds draw_odds away_odds country league datetime home_team away_team home_score away_score
-- ----------- ----------- ----------- --------- ---------------- ------------------- --------------- ----------- ------------ ------------
0 1.36 4.31 7.66 Brazil Copa do Nordeste 2020-02-07 00:00:00 Sport Recife Imperatriz 2 2
1 2.62 3.3 2.48 Brazil Copa do Nordeste 2020-02-02 22:00:00 ABC America RN 2 1
2 5.19 3.58 1.62 Brazil Copa do Nordeste 2020-02-02 00:00:00 Frei Paulistano Nautico 0 2
3 2.06 3.16 3.5 Brazil Copa do Nordeste 2020-02-02 22:00:00 Botafogo PB Confianca 1 1
4 2.19 2.98 3.38 Brazil Copa do Nordeste 2020-02-02 22:00:00 Fortaleza Ceara 1 1
It takes me 9 minutes to run this code with warnings:
sys:1: DtypeWarning: Columns (3,4,5) have mixed types.Specify dtype option on import or set low_memory=False.
G:/My Drive/Odds/Code/5. Creating updated training data.py:33: UserWarning: Boolean Series key will be reindexed to match DataFrame index.
df = df[~n]
G:/My Drive/Odds/Code/5. Creating updated training data.py:34: UserWarning: Boolean Series key will be reindexed to match DataFrame index.
df = df[~o]
How can I cleanup this code and run it faster using pandas?
Also, I have a GPU so I can exploit [cudf][1] however I am using the python 3.7 environment and cudf does not seem to support cud and am not familiar with conda yet.
[1]: https://github.com/rapidsai/cudf