1

Sequel to this question: Live statistics chess960 from chess.com?

So suppose I go to like

https://api.chess.com/pub/player/gmwso/games/2020/12

or

https://api.chess.com/pub/player/gmwso/games/2020/12/pgn

there's gonna be a bunch of stuff like say

[UTCDate "2018.01.03"]
[WhiteElo "2706"]
[BlackElo "2940"]

How do I get this data into a spreadsheet like column 1 is all the dates, column 2 is the corresponding white elo, column 3 black elo, col4 white username and col5 black username?


Update 2: Fixed now. see the 'json' vs the 'preformed'. WOW.

Update 1: It appears Mike Steelson has an answer here, where the code is given as

=arrayformula( regexextract(split( substitute(substitute(substitute(getDataJSON(A1;"/games";"/pgn");"[";"");"]";"");"""";"") ;char(10));"\s.*") )

with an example given here

https://docs.google.com/spreadsheets/d/1MX1o5qdy0K3gTMzbimUV3SmFf-0XPCSJ8Vz4IjI-8Ak/copy

It appears there's a problem when it gets to the case of chess960 only. Consider for example this player: Replacing 'gmwso' with the player's username will yield a weird output. i imagine the output will be messier for mixed chess960 and chess.

2

4 Answers 4

3

How do I get this data into a spreadsheet like column 1 is all the dates, column 2 is the corresponding white elo, column 3 black elo, col4 white username and col5 black username?

From the .pgn file downloaded at

https://api.chess.com/pub/player/gmwso/games/2020/12/pgn

I have created a file called chess_games.xlsx and I have inserted the five values you are asking using a python2 script. You need to install chess and openpyxl libraries with pip. The .pgn file is read as a long string.

import pgn
import openpyxl
import os

pgn_text = open('ChessCom_gmwso_202012.pgn').read()

dates = []
welo = []
belo = []
white = []
black = []

whiteLong = []
blackLong = []

def getValues(file):
    test_str = file
    test_date = "[Date"
    res1 = [i for i in range(len(test_str)) if test_str.startswith(test_date, i)]
    test_welo = "[WhiteElo"
    res2 = [i for i in range(len(test_str)) if test_str.startswith(test_welo, i)]
    test_belo = "[BlackElo"
    res3 = [i for i in range(len(test_str)) if test_str.startswith(test_belo, i)]
    test_white = "[White "
    res4 = [i for i in range(len(test_str)) if test_str.startswith(test_white, i)]
    test_black = "[Black "
    res5 = [i for i in range(len(test_str)) if test_str.startswith(test_black, i)]
    for i in res1:
    dates.append(test_str[i+7:i+17])
    for i in res2:
        welo.append(test_str[i+11:i+15])
    for i in res3:
        belo.append(test_str[i+11:i+15])
    for i in res4:
        whiteLong.append(test_str[i+8:i+43]) //The max length of a nickname in chess.com is 35 characters
    for i in res5:
        blackLong.append(test_str[i+8:i+43])
    for value in whiteLong:
        posClose = value.find("]")
        white.append(value[0:posClose-1])
    for value in blackLong:
        posClose = value.find("]")
        black.append(value[0:posClose-1])

def generateExcel(dates,welo,belo,white,black):
    file = 'chess_games.xlsx'
    if os.path.isfile(file):
        wb = openpyxl.load_workbook(filename=file)
    else:
        wb = openpyxl.Workbook()
    ws = wb["chess_games"]
    for k in range (len(dates)):
        ws['A'+str(k+1)] = dates[k]
    for k in range (len(welo)):
        ws['B'+str(k+1)] = welo[k]
    for k in range (len(belo)):
        ws['C'+str(k+1)] = belo[k]
    for k in range (len(white)):
        ws['D'+str(k+1)] = white[k]
    for k in range (len(black)):
        ws['E'+str(k+1)] = black[k]
    wb.save(file)

getValues(pgn_text)
generateExcel(dates,welo,belo,white,black)

Download the output .xlsx file

0
2

First, worth noting that this question has nothing to do with chess. It is a programming question and a simple one at that.

The answer is very simple. Write a program to read the files and parse them. The pseudo-code looks something like this:

Read the file
For each line

  1. find and extract any keywords like "WhiteELO", "UTCDate", etc.
  2. If there is no keyword then throw the line away (or do something else if you need to)
  3. If there is a keyword then extract and store the associated data

Once you have processed all the lines of the game in the pgn then write a formatted line to your output file.

Note that for spreadsheet-readable files you can use something like ";" (semicolon) as the field delimiter. Probably better than "," (comma) since the title and player name fields are likely to contain commas.

Note that there was nothing whatsoever to do with chess in that answer. It was all simple data processing.

0
1

Update 2: Fixed now. see the 'json' vs the 'preformed'. WOW.

Update 1: It appears Mike Steelson has an answer here, where the code is given as

=arrayformula( regexextract(split( substitute(substitute(substitute(getDataJSON(A1;"/games";"/pgn");"[";"");"]";"");"""";"") ;char(10));"\s.*") )

with an example given here

https://docs.google.com/spreadsheets/d/1MX1o5qdy0K3gTMzbimUV3SmFf-0XPCSJ8Vz4IjI-8Ak/copy

It appears there's a problem when it gets to the case of chess960 only. Consider for example this player: Replacing 'gmwso' with the player's username will yield a weird output. i imagine the output will be messier for mixed chess960 and chess.

0

I avoided having to write a programming script to convert a PGN games file into a spreadsheet, but I used a word processing application instead.

The WP app needs to support regular expressions (regex), which allow you to use variables as well as the usual literals. Basically, this is activated by setting a checkbox in the Find/Replace dialog(s), but you need to use special characters to specify how you want the text manipulated. (The helpfile on "regex" explains how, with examples.)

The key is that each variable in a PGN game header stands on its own line. To concatenate them all for use by a spreadsheet, you need to convert the paragraph separator characters, which I'll call [Para] characters, into [Tab] characters.

But first, knowing that all PGN games are separated by a blank line indicates there are two para characters separating them. This makes the [Para] chars ambiguous, except for the pairing characteristic when they're used for game separation. You want the games on separate rows in your spreadsheet, so you need to preserve this game separation info and restore it once the concatenation is complete.

So, first I copied (or opened) the PGN file in(to) MS Word.

Then I regex-replaced all the double-para characters with a special-character pair I reserved for that purpose: "&&". That's the delimiter between games.

(Note: As you manipulate the file in Word, it will immediately start looking weird. Ignore that, and just execute the steps.)

Next, I regex-replaced all the "][Para]" End-of-PGN-Field character pairs with a special-character pair I reserved for that purpose: "]%". The "]" is just a target-finder; it specifies that only the lines ending in a "]" are to be concatenated. The "%" becomes the delimiter between the PGN fields, and between the last field and the start of the game score.

Next, you have to concatenate the moves, which have been arbitrarily split by the PGN file creation software so that they're human-readable using a simple text processor, like Notepad.

To do this, I regex-replaced all remaining [Para] chars with space " " chars. This concatenates the game scores, with at least one space between the end of one line and the beginning of the next from the PGN file. Note that this has no effect on the PGN header fields or the game separations; those were already converted to special characters to prevent them from being affected by this step.

Next I regex-replaced the "%" characters with "[Tab]" characters so the PGN fields will load into separate columns in the spreadsheet when the time comes.

Finally, I regex-replaced the "&&" character pairs with [Para] chars so that each game would be on its own row in the spreadsheet.

Finally, I selected all (you can use "Select-All", instead of trying to drag-select over sometimes 100's of pages, but even that can be done by placing the cursor at the start of the text [Ctrl]-[End]) of the text from the document, opened a fresh spreadsheet, and pasted the text.

A few caveats:

  1. If you find that some of the PGN games have one set of PGN header fields, and some of them have a different set (this shouldn't happen, but might if your PGN file creator adds uncommon fields only for the games that came with that data, and omits them for others), then you can easily detect the disparities by just looking for misalignments in one of the last PGN file columns in the spreadsheet. If this affects only a small number of rows, you can generally correct it with some manual copy-pasta work in the spreadsheet to realign the fields. I always dropped any PGN field that only a few games had.

Before you undertake the manual process, assess the magnitude of the effort. How many rows are non-conforming to the standard content of the majority of the games? If it's a lot, and you don't need the unusual fields, it may be more efficient to just repeat the text file conversion process, but precede it by first removing all of these non-conforming fields and their data. Again, you can use a regex-replace to specify a starting "[", the name of the field plus a [wildcard] character to specify all of the non-essential text, as well as a "]", and then just replace them all with nothing.

Some may observe that if we had known this would be a problem before beginning the first conversion cycle, we could have saved that effort. That's true, but it turns out the most reliable way to find the anomalies is to produce a pilot version of the spreadsheet, and look for the misalignments of PGN fields caused by the interjection of the unusual fields' data in the anomalous rows. This is comprehensive, 100% effective at avoiding false positives and false negatives, and fairly easy. In the conversion process itself, once a step has been executed and verified to operate correctly, can be easily repeated using shortcuts like "Last Used" dropdown box selections in the Regex dialog box, or even automated with simple macros or one master macro (though in that case we're back to programming).

  1. Sometimes there are anomalies in PGN game files that cause a game (or even swathes of them) not to be recognized by a chess reader. (I'll leave to you whether it's worth pursuing and addressing how they occurred.) Therefore, you should always get a count of the PGN games that are supposed to be in the file by counting the number of occurrences of a telltale, standard PGN field (I like to use the "White" field). To get the count, load (a copy of) the PGN file into your word processor and do a replace of "White" with "White". The file will be identical, but the word processor will report the number of replacements.

Then load the file into a chess reader, and check the number of games it reports in the file. If the numbers don't match (the reader's count cannot be higher, but it can be lower), I always isolate the games that the chess reader can't read and try to recover them. Most of the time, it's just a matter of correcting some character anomaly, a missing field delimiter "[" or "]", or (rarely) a missing required PGN field.

If this problem exists, it may cause the reader to stumble and be unable to read several perfectly intact games after the offending game; don't assume that subsequent "unreadable" games all have problems. Often, fixing the first in the set and then trying again magically makes all of them readable.

  1. PGN readers are (by the specification in the standard) very tolerant of lots of sloppiness, so they ignore most disparities between one game and the next, including the order of fields. If you get all of the PGN games in a file from one source, then such disparities shouldn't appear at all. But if you concatenate games from different sources (which I highly discourage, for this reason), they are likely to show up. Then, trying to get the data from multiple sources to align in the right spreadsheet columns for all of the rows can be a real exercise.

Better to load PGN files from different sources into separate spreadsheet tabs, and then merge them later after any column inconsistencies between them have been identified and sorted out.

Why do this analysis prep work at all? I've used it to find games that met several criteria:

  • Upsets, based on the underdog's ELO deficit vs their opponent
  • Miniatures (games with decisive results in under [game-length-limit] moves.)

but the ability to sort, filter and calculate based on the PGN fields alone has made it worthwhile in my research.

1
  • Note that very long game scores, say, above 110 moves, can't be contained in a single spreadsheet cell. Depending on your software, the remaining moves might get truncated or they may end up in the next cell on the row. In dealing with files containing fewer than 20k games, these were rare enough for me to just analyze them manually. Since the spreadsheet cell char limit is a hard stop, you just have to work around it (or switch spreadsheet apps).
    – jaxter
    Jul 5, 2023 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.