Lo-Fi Python

Analyzing Messi vs. Ronaldo with the FIFA API + jq + curl

Who is the world's greatest footballer, Messi or Ronaldo? EA Sports surely has calculated the answer to this question in their player ratings. They rate peak Crisitiano Ronaldo, Lionel Messi and Luka Modrić at 99 overall, with Neymar and Lewandowski at 98. Anecdotally, Messi has won 7 Ballon d'Or, the highest individual football honor one can achieve each year. Ronaldo has won 5 B'allon d'Or. Modrić has won 1 Ballon d'Or. Lewandowski was runner up this year, but has never won the honor. Neymar has never won a Ballon d'Or.

In FIFA, a player's video game representation is modeled intricately in a series of traits and specialties characterizing each player. The "Ultimate Team" EA Sports API is viewable as a plain json page or more cheekily with one line of curl and jq, a "command line json processor":

curl 'https://www.easports.com/fifa/ultimate-team/api/fut/item' | jq '.'

Enter this in a shell or command line. The result is beautiful, readable, pretty printed json!

Messi (Top) Vs. Ronaldo (Bottom) FIFA Player Ratings

These ratings represent the players at their peak of their careers. Messi is a better dribbler, while Ronaldo has more power and strength. Messi has the edge in free kicks, curve in his shot and "longshots" 99 to 98 over Cristiano. They are tied at "finishing", each with 99. Ronaldo has the "Power Free-Kick" trait, whereas Messi has "Chip Shot", "Finesse Shot" and "Playmaker" traits giving him an edge.

EA's ratings suggest that both are prominent goal scorers, with a slight edge to Messi in finesse and shooting from distance. However, there's something to be said for kicking the ball really damn hard. Ronaldo has superior raw shot power and a lethal combo of more powerful jump and stronger headers. All this combined with an "Aerial Threat" specialty enables Ronaldo to vault above and around defenders to smash in golazos off the volley. Ronaldo sizes up to 6' 2" (187 cm) vs. Messi's 5' 7" (170 cm) frame. This Portugese man definitely has an advantage in getting higher in the air. But the Argentinian is quite darty.

Messi has incredible accuracy from distance. He's also a better passer all around and has perfect "vision", great qualities for winning football games. Only in crossing does he have a lower passing rating. Ronaldo is also 10 points better at "penalties" or penalty kicks. The closer he gets to the goal, the more dangerous he is. Messi is more dangerous with the ball while dribbling, passing or shooting except when taking a PK.

Advantages can be gained in many different aspects of soccer. EA has developed a fun dataset to model these all time greats across several football skill dimensions. In 2022's version of the game, Messi is rated a 93, with Cristiano 91. Clearly these two are worthy of top honors. Don't forget Robert Lewandowski, with a 92 rating, who consistently lights up the Champions League and Bundesliga.

jq ftw

I had never used jq before this. Really enjoyed the quick, stylish and practical view of some json. This cool terminal display and syntax highlighting was on my Chromebook shell. It's neat how easily you can pretty print json with jq. I rate it a 99 for json pretty processing and pretty printing on the FIFA scale. Read more in the jq documentation!

Copying a pandas Dataframe to Google Sheets with pygsheets

Disclaimer: This endeavor was before I discovered AppScript, which may be an alternative solution to using pygsheets or other python libraries. pygsheets is interesting, but it could be a stretch to justify using it for something that could be done with AppScript. Both are ways to solve a problem by automating Google Sheet operations.

This was done on the Windows 7 OS. First, install libraries with pip. Enter in command prompt or terminal:

python -m pip install pandas
python -m pip install numpy
python -m pip install pygsheets

After installing necessary libraries, follow the steps documented by pygsheets:

1. Create a Google Developer Account at console.developers.google.com
2. Enable Sheets API to account
3. Enable Drive API to account. Same as last step, but search for Drive.
4. Create a Client Secret json file. Select "Credentials" tab, and "Create Credentials". Select Client Secret from options. Export from console and place in same directory as your .py file.
5. Create a Service Account json file by selecting it instead of "Client Secret".
6. Authorize pygsheets with your json files. See below.
7. Copy spreadsheet to Google Sheet with pandas and pygsheets. See below.

After completing the first 5 steps, import pygsheets and authorize your account with the client secret json file:

1
2

import pygsheets
gc = pygsheets.authorize(client_secret='path/to/client_secret[...].json')

You will be prompted by the terminal to go to a hyperlink in a browser, get your authorization code, and enter that authorization code into the terminal.

Now, import both libraries needed and switch to authorize with your service json file. Then, load the csv to a dataframe with pandas. Finally, copy it to an existing Google Sheet with pygsheets:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

import pygsheets
import pandas as pd

"""Select worksheets by id, index, or title."""
gc = pygsheets.authorize(service_file='path/to/service_account_credentials.json')
sh = gc.open('add_google_sheet_name_here')
wks = sh.worksheet_by_title('add_sheet_tab_name_here')

"""Set a pandas dataframe to google sheet, starting at 1st row, 1st column"""
df = pd.read_csv('Example_Data.csv')
wks.set_dataframe(df,(1,1))

[Example] Split and upload a sheet with 40 columns

Google Sheets limits importing to 26 columns and 1,000 rows at a time. So you'll have to load the sheets in chunks if you have more than that. This approach uses numpy's array_split:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

import pygsheets
import pandas as pd
import numpy as np

gc = pygsheets.authorize(client_secret='path/to/client_secret[...].json')
sh = gc.open('add_google_sheet_name_here')
wks = sh.worksheet_by_title('add_sheet_tab_name_here')
df = pd.read_csv('Data_to_GSheets.csv')

# split columns into two dataframes with numpy and pandas
first_half_cols, second_half_cols = np.array_split(df.columns, 2)
first_half = df[first_half_cols]
second_half = df[second_half_cols]

# set both dataframes side-by-side in Google sheet
wks.set_dataframe(first_half,(1,1))
start_column = first_half.shape[1]
wks.set_dataframe(second_half,(1, start_column))

Conclusion

I found the terminal error messages from pygsheets to be very helpful while debugging the above. This module offers many other nifty spreadsheet operations. Solid library. You can now create and edit Google Sheets with Python.

AppsScript should probably be the default tool when working with Google Sheets because it is built in, but Python does have tools available to work with Google Sheets.

Resources

pygsheets Github

pygsheets Documentation

Google Sheets Documentation

pandas Documentation