pandas read_csv can't handle missing data

pandas read_csv can't handle missing data - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: Data Science (https://python-forum.io/forum-44.html)
+--- Thread: pandas read_csv can't handle missing data (/thread-28204.html)

pandas read_csv can't handle missing data - mrdominikku - Jul-09-2020

I am trying to download data from football data

import os

import pandas as pd

GAMES = ['E0', 'E1', 'E2', 'E3']


def download_statistics():
    for year in range(2003, 2020):
        year_format = str(year)[-2:] + str(year+1)[-2:]
        for game in GAMES:
            previous_data = None
            file_name = f'{game}.csv'
            if os.path.isfile(file_name):
                previous_data = pd.read_csv(file_name)

            url_data = pd.read_csv(f'http://football-data.co.uk/mmz4281/{year_format}/{game}.csv')

            if previous_data is not None:
                combined_data = pd.concat([previous_data, url_data])
                combined_data.to_csv(file_name)
            else:
                url_data.to_csv(file_name)


if __name__ == '__main__':
    download_statistics()

I am aware that some cells missing data, but somehow pandas can't handle them and returning error.

Error:Traceback (most recent call last):
  File "F:\Programowanie\GitHub Repositories\football_predict\venv\lib\site-packages\pandas\io\parsers.py", line 454, in _read
    data = parser.read(nrows)
  File "F:\Programowanie\GitHub Repositories\football_predict\venv\lib\site-packages\pandas\io\parsers.py", line 1133, in read
    ret = self._engine.read(nrows)
  File "F:\Programowanie\GitHub Repositories\football_predict\venv\lib\site-packages\pandas\io\parsers.py", line 2037, in read
    data = self._reader.read(nrows)
  File "pandas\_libs\parsers.pyx", line 860, in pandas._libs.parsers.TextReader.read
  File "pandas\_libs\parsers.pyx", line 875, in pandas._libs.parsers.TextReader._read_low_memory
  File "pandas\_libs\parsers.pyx", line 929, in pandas._libs.parsers.TextReader._read_rows
  File "pandas\_libs\parsers.pyx", line 916, in pandas._libs.parsers.TextReader._tokenize_rows
  File "pandas\_libs\parsers.pyx", line 2071, in pandas._libs.parsers.raise_parser_error
pandas.errors.ParserError: Error tokenizing data. C error: Expected 57 fields in line 305, saw 72

Below code doesn't return error, but returning DataFrame with shape (380,1) and when trying to split data with comma I am receiving another error:

import os
import io

import pandas as pd
import requests

GAMES = ['E0', 'E1', 'E2', 'E3']


def download_statistics():
    for year in range(2003, 2020):
        year_format = str(year)[-2:] + str(year+1)[-2:]
        for game in GAMES:
            previous_data = None
            file_name = f'{game}.csv'
            if os.path.isfile(file_name):
                previous_data = pd.read_csv(file_name)

            response = requests.get(f'http://football-data.co.uk/mmz4281/{year_format}/{game}.csv')
            url_data = pd.read_csv(io.StringIO(response.text), sep='delimiter')
            url_data = url_data[0].str.split(',', expand=True)

            if previous_data is not None:
                combined_data = pd.concat([previous_data, url_data])
                combined_data.to_csv(file_name)
            else:
                url_data.to_csv(file_name)


if __name__ == '__main__':
    download_statistics()

Error:F:/Programowanie/GitHub Repositories/football_predict/data.py:23: ParserWarning: Falling back to the 'python' engine because the 'c' engine does not support regex separators (separators > 1 char and different from '\s+' are interpreted as regex); you can avoid this warning by specifying engine='python'.
  url_data = pd.read_csv(io.StringIO(response.text), sep='delimiter')
Traceback (most recent call last):
  File "F:\Programowanie\GitHub Repositories\football_predict\venv\lib\site-packages\pandas\core\indexes\base.py", line 2646, in get_loc
    return self._engine.get_loc(key)
  File "pandas\_libs\index.pyx", line 111, in pandas._libs.index.IndexEngine.get_loc
  File "pandas\_libs\index.pyx", line 138, in pandas._libs.index.IndexEngine.get_loc
  File "pandas\_libs\hashtable_class_helper.pxi", line 1619, in pandas._libs.hashtable.PyObjectHashTable.get_item
  File "pandas\_libs\hashtable_class_helper.pxi", line 1627, in pandas._libs.hashtable.PyObjectHashTable.get_item
KeyError: 0

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "D:\Program Files\PyCharm 2019.3.4\plugins\python\helpers\pydev\pydevd.py", line 1434, in _exec
    pydev_imports.execfile(file, globals, locals)  # execute the script
  File "D:\Program Files\PyCharm 2019.3.4\plugins\python\helpers\pydev\_pydev_imps\_pydev_execfile.py", line 18, in execfile
    exec(compile(contents+"\n", file, 'exec'), glob, loc)
  File "F:/Programowanie/GitHub Repositories/football_predict/data.py", line 34, in <module>
    download_statistics()
  File "F:/Programowanie/GitHub Repositories/football_predict/data.py", line 24, in download_statistics
    url_data = url_data[0].str.split(',', expand=True)
  File "F:\Programowanie\GitHub Repositories\football_predict\venv\lib\site-packages\pandas\core\frame.py", line 2800, in __getitem__
    indexer = self.columns.get_loc(key)
  File "F:\Programowanie\GitHub Repositories\football_predict\venv\lib\site-packages\pandas\core\indexes\base.py", line 2648, in get_loc
    return self._engine.get_loc(self._maybe_cast_indexer(key))
  File "pandas\_libs\index.pyx", line 111, in pandas._libs.index.IndexEngine.get_loc
  File "pandas\_libs\index.pyx", line 138, in pandas._libs.index.IndexEngine.get_loc
  File "pandas\_libs\hashtable_class_helper.pxi", line 1619, in pandas._libs.hashtable.PyObjectHashTable.get_item
  File "pandas\_libs\hashtable_class_helper.pxi", line 1627, in pandas._libs.hashtable.PyObjectHashTable.get_item
KeyError: 0

Am I missing something here?