Skip to main content
Corrected code, edited spelling mistakes and added explanations
Source Link
from datetime import datetime
import pandas as pd
import numpy as np
from tabulate import tabulate

start = datetime.now()

df = pd.read_csv()

#This part essentially splits columns and harmonises the entie dataframe

# This code harmonises the game column e.g. "Talleres (R.E) - Defensores Unidos" should be split as "Talleres - "Defensores Unidos" removing any brackets and its values and removes any date values in the column
df['game'] = df['game'].astype(str).str.replace('(\(\w+\))', '', regex=True)
df['game'] = df['game'].astype(str).str.replace('(\s\d+\S\d+)$', '', regex=True)
# This code removes any numerical values in the league column. Many times the league column has years concatenated which is what we don't want e.g "Brazil  Copa do Nordeste 2020" should be "Brazil  Copa do Nordeste"
df['league'] = df['league'].astype(str).str.replace('(\s\d+\S\d+)$', '', regex=True)
# This part splits the game column into towtwo competing teams i.e. home team and away team by the delimiter "-"
df[['home_team', 'away_team']] = df['game'].str.split(' - ', expand=True, n=1)
# This part splits the gamescore column into towtwo competing teams i.e. home teamscore and away teamscore by the delimiter ":"
df[['home_score', 'away_score']] = df['score'].str.split(':', expand=True)
# This code removes any non numerical values in the home score and away score columns. e.ge scores cancan't have "aet", "canc", ".", etc. We dont want anything that cannot be identified as filetype:int in pandas
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)

# I get UserWarning: Boolean Series key will be reindexed to match DataFrame index. at these parts
df = df[~n]
df = df[~m]
df = df[~n]
df = df[~o]
df = df[df.home_score != '']
df = df[df.away_score != '']
df = df.dropna()

# We now would be keeping only the columns we want
df = df.loc[:, df.columns.intersection(
    ['datetime', 'country', 'league', 'home_team', 'away_team', 'home_odds', 'draw_odds', 'away_odds', 'home_score',
     'away_score'])]

#We are making sure that the columns are as per data types that we would want pandas to identify. Pandas does not seem to do a very good job identifying data types correctly.
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)

# This part removes any leading and trailing whitespaces in the string columns
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
from datetime import datetime
import pandas as pd
import numpy as np
from tabulate import tabulate

start = datetime.now()

df = pd.read_csv()

#This part essentially splits columns and harmonises the entie dataframe

# This code harmonises the game column e.g. "Talleres (R.E) - Defensores Unidos" should be split as "Talleres - "Defensores Unidos" and removes any date values in the column
df['game'] = df['game'].astype(str).str.replace('(\(\w+\))', '', regex=True)
df['game'] = df['game'].astype(str).str.replace('(\s\d+\S\d+)$', '', regex=True)
# This code removes any numerical values in the league column. Many times the league column has years concatenated which is what we don't want e.g "Brazil  Copa do Nordeste 2020" should be "Brazil  Copa do Nordeste"
df['league'] = df['league'].astype(str).str.replace('(\s\d+\S\d+)$', '', regex=True)
# This part splits the game column into tow competing teams i.e. home team and away team by the delimiter "-"
df[['home_team', 'away_team']] = df['game'].str.split(' - ', expand=True, n=1)
# This part splits the game column into tow competing teams i.e. home team and away team by the delimiter ":"
df[['home_score', 'away_score']] = df['score'].str.split(':', expand=True)
# This code removes any non numerical values in the home score and away score columns. e.ge scores can have "aet", "canc", ".", etc. We dont want anything that cannot be identified as filetype:int in pandas
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()

# We now would be keeping only the columns we want
df = df.loc[:, df.columns.intersection(
    ['datetime', 'country', 'league', 'home_team', 'away_team', 'home_odds', 'draw_odds', 'away_odds', 'home_score',
     'away_score'])]

#We are making sure that the columns are as per data types that we would want pandas to identify. Pandas does not seem to do a very good job identifying data types correctly.
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)

# This part removes any leading and trailing whitespaces in the string columns
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
from datetime import datetime
import pandas as pd
import numpy as np
from tabulate import tabulate

start = datetime.now()

df = pd.read_csv()

#This part essentially splits columns and harmonises the entie dataframe

# This code harmonises the game column e.g. "Talleres (R.E) - Defensores Unidos" should be as "Talleres - "Defensores Unidos" removing any brackets and its values and removes any date values in the column
df['game'] = df['game'].astype(str).str.replace('(\(\w+\))', '', regex=True)
df['game'] = df['game'].astype(str).str.replace('(\s\d+\S\d+)$', '', regex=True)
# This code removes any numerical values in the league column. Many times the league column has years concatenated which is what we don't want e.g "Brazil  Copa do Nordeste 2020" should be "Brazil  Copa do Nordeste"
df['league'] = df['league'].astype(str).str.replace('(\s\d+\S\d+)$', '', regex=True)
# This part splits the game column into two competing teams i.e. home team and away team by the delimiter "-"
df[['home_team', 'away_team']] = df['game'].str.split(' - ', expand=True, n=1)
# This part splits the score column into two competing teams i.e. home score and away score by the delimiter ":"
df[['home_score', 'away_score']] = df['score'].str.split(':', expand=True)
# This code removes any non numerical values in the home score and away score columns. e.ge scores can't have "aet", "canc", ".", etc. We dont want anything that cannot be identified as filetype:int in pandas
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)

# I get UserWarning: Boolean Series key will be reindexed to match DataFrame index. at these parts
df = df[~n]
df = df[~m]
df = df[~n]
df = df[~o]
df = df[df.home_score != '']
df = df[df.away_score != '']
df = df.dropna()

# We now would be keeping only the columns we want
df = df.loc[:, df.columns.intersection(
    ['datetime', 'country', 'league', 'home_team', 'away_team', 'home_odds', 'draw_odds', 'away_odds', 'home_score',
     'away_score'])]

#We are making sure that the columns are as per data types that we would want pandas to identify. Pandas does not seem to do a very good job identifying data types correctly.
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)

# This part removes any leading and trailing whitespaces in the string columns
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
edited title
Link
Mast
  • 13.5k
  • 12
  • 54
  • 119

How can I optimise this pandas code? Cleanup dataset for visualization

Post Undeleted by leonardo
Post Deleted by leonardo
Corrected body, updated explanation and added comments to functions
Source Link

This code cleans up a big dataset into a very clean and a flat file that can be further used downstream like visualization.

I am expecting to improve the code to essentially run it faster and cleanup the code to avoid any inefficient functions that should not take further resources than it should.

The problem is that I have written the code as and when I have experienced any functions as needed to be added on the fly and now, I am not very happy with it.

I am seeking help from this community on the best code that can be written which performs the same work that this code does. I would also be learning on the best practices while working with pandas.

There is so much on the internet on Pandas that every function and code seems to be a clean code but when i look at it collectively, its so bad.

from datetime import datetime
import pandas as pd
import numpy as np
from tabulate import tabulate

start = datetime.now()

df = pd.read_csv()

#This part essentially splits columns and harmonises the entie dataframe

# This code harmonises the game column e.g. "Talleres (R.E) - Defensores Unidos" should be split as "Talleres - "Defensores Unidos" and removes any date values in the column
df['game'] = df['game'].astype(str).str.replace('(\(\w+\))', '', regex=True)
df['league']df['game'] = df['league']df['game'].astype(str).str.replace('(\s\d+\S\d+)$', '', regex=True)
df['game']# This code removes any numerical values in the league column. Many times the league column has years concatenated which is what we don't want e.g "Brazil  Copa do Nordeste 2020" should be "Brazil  Copa do Nordeste"
df['league'] = df['game']df['league'].astype(str).str.replace('(\s\d+\S\d+)$', '', regex=True)
# This part splits the game column into tow competing teams i.e. home team and away team by the delimiter "-"
df[['home_team', 'away_team']] = df['game'].str.split(' - ', expand=True, n=1)
# This part splits the game column into tow competing teams i.e. home team and away team by the delimiter ":"
df[['home_score', 'away_score']] = df['score'].str.split(':', expand=True)
# This code removes any non numerical values in the home score and away score columns. e.ge scores can have "aet", "canc", ".", etc. We dont want anything that cannot be identified as filetype:int in pandas
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() 

# We now would be keeping only the columns we want
df = df.loc[:, df.columns.intersection(
    ['datetime', 'country', 'league', 'home_team', 'away_team', 'home_odds', 'draw_odds', 'away_odds', 'home_score',
     'away_score'])]

#We are making sure that the columns are as per data types that we would want pandas to identify. Pandas does not seem to do a very good job identifying data types correctly.
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) 

# This part removes any leading and trailing whitespaces in the string columns
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
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

This code cleans up a big dataset into a very clean and a flat file that can be further used downstream like visualization.

I am expecting to improve the code to essentially run it faster and cleanup the code to avoid any inefficient functions that should not take further resources than it should.

The problem is that I have written the code as and when I have experienced any functions as needed to be added on the fly and now, I am not very happy with it.

I am seeking help from this community on the best code that can be written which performs the same work that this code does. I would also be learning on the best practices while working with pandas.

There is so much on the internet on Pandas that every function and code seems to be a clean code but when i look at it collectively, its so bad.

from datetime import datetime
import pandas as pd
import numpy as np
from tabulate import tabulate

start = datetime.now()

df = pd.read_csv()

#This part essentially splits columns and harmonises the entie dataframe

# This code harmonises the game column e.g. "Talleres (R.E) - Defensores Unidos" should be split as "Talleres - "Defensores Unidos" and removes any date values in the column
df['game'] = df['game'].astype(str).str.replace('(\(\w+\))', '', regex=True)
df['game'] = df['game'].astype(str).str.replace('(\s\d+\S\d+)$', '', regex=True)
# This code removes any numerical values in the league column. Many times the league column has years concatenated which is what we don't want e.g "Brazil  Copa do Nordeste 2020" should be "Brazil  Copa do Nordeste"
df['league'] = df['league'].astype(str).str.replace('(\s\d+\S\d+)$', '', regex=True)
# This part splits the game column into tow competing teams i.e. home team and away team by the delimiter "-"
df[['home_team', 'away_team']] = df['game'].str.split(' - ', expand=True, n=1)
# This part splits the game column into tow competing teams i.e. home team and away team by the delimiter ":"
df[['home_score', 'away_score']] = df['score'].str.split(':', expand=True)
# This code removes any non numerical values in the home score and away score columns. e.ge scores can have "aet", "canc", ".", etc. We dont want anything that cannot be identified as filetype:int in pandas
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() 

# We now would be keeping only the columns we want
df = df.loc[:, df.columns.intersection(
    ['datetime', 'country', 'league', 'home_team', 'away_team', 'home_odds', 'draw_odds', 'away_odds', 'home_score',
     'away_score'])]

#We are making sure that the columns are as per data types that we would want pandas to identify. Pandas does not seem to do a very good job identifying data types correctly.
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) 

# This part removes any leading and trailing whitespaces in the string columns
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
corrected question, added file link
Source Link
Loading
Corrected Title
Link
Loading
deleted 1 character in body
Source Link
Loading
Source Link
Loading