Associating .csv column data for calculations

Question

I am a complete nube to Python3 and coding so go easy on me please. :)

As a project I'm creating a football league table based on 2018 EPL results. I have been able to break the .csv file containing an entire seasons worth of data into round by round results, into .csv using Pandas module. Now I need to extract the table points for each team by round, based on the home and away goals for each team. I'm having a hard time associating the goals with the teams in each fixture. I can figure out how to apply win/draw/lose (3/1/0) points but only mandrolically per fixture, not dynamically for all fixtures in the round. Then I need to write the table to another .csv file.

FTHG-Full Time Home Goals, FTAG-Full Time Away Goals, FTR-Full Time Result

Example Data

Unnamed: 0,Date,HomeTeam,AwayTeam,FTHG,FTAG,FTR

0,10/08/2018,Man United,Leicester,2,1,H

1,11/08/2018,Bournemouth,Cardiff,2,0,H

2,11/08/2018,Fulham,Crystal Palace,0,2,A

3,11/08/2018,Huddersfield,Chelsea,0,3,A

4,11/08/2018,Newcastle,Tottenham,1,2,A

5,11/08/2018,Watford,Brighton,2,0,H

6,11/08/2018,Wolves,Everton,2,2,D

7,12/08/2018,Arsenal,Man City,0,2,A

8,12/08/2018,Liverpool,West Ham,4,0,H

9,12/08/2018,Southampton,Burnley,0,0,D

Example Code

import pandas as pd

results = pd.read_csv("2018 Round 1.csv")

team = results.iloc[2,2]

if results.iloc[2,4] > results.iloc[2,5]:

    points = 3

elif results.iloc[2, 4] < results.iloc[2, 5]:

    points = 0

else:
    results.iloc[2, 4] = results.iloc[2, 5]

    points = 1


table_entry = (team + " " + str(points))

print(table_entry)

table_entry = pd.to_csv("EPL Table Round 1.csv", index = False)

Thanks for your help.

Ivan · Accepted Answer · 2020-02-13 15:31:17Z

0

I hope this helps :) Please fell free to ask if the code it's not clear

import pandas as pd
import numpy as np

df = pd.read_csv('foot.txt')

#Make a list with all tema names
Home_teams = pd.unique(df['HomeTeam'])
Away_teams = pd.unique(df['AwayTeam'])
teams = np.concatenate((Home_teams, Away_teams))

df_teams =  pd.DataFrame(columns=['team', 'points'])

#For each team in the list...
for team in teams:
    print("*******" + team+ "*****")
    points = 0
    df_home = df[(df['HomeTeam'] == team)]
    res_home = df_home['FTR'].value_counts()
    try:
        points += res_home['H']*3;
    except:
        print("Didn't win when Home")
    try:
        points += res_home['D']*1;
    except:
        print("No Draws")

    df_away = df[(df['AwayTeam'] == team)]
    res_away = df_away['FTR'].value_counts()
    try:
        points += res_away['A']*3;
    except:
        print("Didn't win when Away")

    df_teams = df_teams.append({'team': team, 'points': points}, ignore_index=True)

    print(team +"has "+ str(points) +" points" )

edited Feb 13, 2020 at 15:31

answered Feb 13, 2020 at 10:49

Ivan

1,5512 gold badges20 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Chicka Over a year ago

G'day @Ivan, I had a rethink about how I could go about this after asking my question and came up with something based around the FTR field similar to this but couldn't make it work.columns = pd.("Serial", "HomeTeam", "AwayTeam", "FTHG", "FTAG", "FTR") match_result = () if columns("FTR" == "H"): home_win = columns("HomeTeam") home_win = 3 elif columns("FTR" == "A"): away_win = columns("AwayTeam") away_win = 3 elif columns("FTR" == "D"): draw = columns() draw = 1 else: loss = 0 print("HomeTeam", match_result)

Ivan Over a year ago

I see, we had a similar idea, I recommend you to check how I looped over the tems with for team in teams: . Also , for conditions you may fin useful this notation: df[(df['HomeTeam'] == team)]

Ivan Over a year ago

Also, for sharing coda like that you could do an edit on the question

Ivan Over a year ago

I just added df_teams to make a better visualization

Collectives™ on Stack Overflow

Associating .csv column data for calculations

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related