Python Read In Google Spreadsheet Using Pandas

Question

I have file in Google sheets I want to read it into a Pandas Dataframe. But gives me an error i don't know what's it. this is the code :

import pandas as pd
sheet_id = "1HUbEhsYnLxJP1IisFcSKtHTYlFj_hHe5v21qL9CVyak"
df = pd.read_csv(f"https://docs.google.com/spreadsheets/d/{sheet_id}/export? 
gid=556844753&format=csv")
print(df)

And this is the error :

File "c:\Users\blaghzao\Documents\Stage PFA(Laghzaoui Brahim)\google_sheet.py", line 3, in <module>
    df = pd.read_csv(f"https://docs.google.com/spreadsheets/d/{sheet_id}/export?gid=556844753&format=csv")
  File "C:\Users\blaghzao\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\util\_decorators.py", line 311, in wrapper
    return func(*args, **kwargs)
  File "C:\Users\blaghzao\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\parsers\readers.py", line 680, in read_csv
    return _read(filepath_or_buffer, kwds)
  File "C:\Users\blaghzao\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\parsers\readers.py", line 581, in _read
    return parser.read(nrows)
  File "C:\Users\blaghzao\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\parsers\readers.py", line 1255, in read
    index, columns, col_dict = self._engine.read(nrows)
  File "C:\Users\blaghzao\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\io\parsers\c_parser_wrapper.py", line 225, in read
    chunks = self._reader.read_low_memory(nrows)
  File "pandas\_libs\parsers.pyx", line 805, in pandas._libs.parsers.TextReader.read_low_memory
  File "pandas\_libs\parsers.pyx", line 861, in pandas._libs.parsers.TextReader._read_rows
  File "pandas\_libs\parsers.pyx", line 847, in pandas._libs.parsers.TextReader._tokenize_rows
  File "pandas\_libs\parsers.pyx", line 1960, in pandas._libs.parsers.raise_parser_error
pandas.errors.ParserError: Error tokenizing data. C error: Expected 89 fields in line 3, saw 243

Sounds a lot like a data alignment issue. Pandas is for data with a consistent number of columns while spreadsheet programs can be filled arbitrarily. Looks like you have 89 columns on your first line but 243 on the third. — Joooeey, Aug 04 '22 at 09:08

Brahim Laghzaoui · Answer 1 · 2022-08-04T09:32:19.513

3

I found the answer, the problem it's just with access permissions of the file.

enter image description here

edited Aug 04 '22 at 09:32

answered Aug 04 '22 at 09:31

Brahim Laghzaoui

61
6

score 1 · Answer 2 · answered Aug 04 '22 at 09:21

As far as I know this error rises using comma delimiter and you have more commas then expected.

Can you try with below read_csv() method to avoid them;

df = pd.read_csv(f"https://docs.google.com/spreadsheets/d/{sheet_id}/export? gid=556844753&format=csv", on_bad_lines='skip')

This will avoid bad lines so you can identify problem depending on skipped lines. I believe your csv format export is not matching with what pandas read_csv() expects.

Nibras Ahmed · Answer 3 · 2022-08-04T09:21:01.487

0

Remove gid from code

import pandas as pd
sheet_id = "1HUbEhsYnLxJP1IisFcSKtHTYlFj_hHe5v21qL9CVyak"
df = pd.read_csv(f"https://docs.google.com/spreadsheets/d/{sheet_id}/export?format=csv")
print(df)

Click on link for sample image

edited Aug 04 '22 at 09:21

answered Aug 04 '22 at 09:20

Nibras Ahmed

1
3

No doesn't work. and gid i want it to specify witch spreadsheet. – Brahim Laghzaoui Aug 04 '22 at 09:25

Python Read In Google Spreadsheet Using Pandas

3 Answers3

Linked