Como corrigir um ParserError ao respeitar o delimitador CSV e um segundo ParserError na string de URL não esperada com o delimitador definido?

Sou novo aqui, então espero poder colocar todas as informações necessárias

1. Como o CSV é enorme (10GB), o link da URL está no código abaixo, se necessário
2. Link de URL para a descrição dos dados (tipo de coluna...)
3. O delimitador é \t, mas eles o chamam de CSV (descreva no "arquivo de descrição de dados").

Depois de substituir o delimitador errado (substituir '\n\t' por '\t' quando necessário) no arquivo csv e definir o tipo de dados para cada coluna, estou tentando lê-lo usando o delimitador \t, mas encontro 2 erros.

1)

Erro de análise na linha 1715281: esperavam-se 209 campos, mas foram encontrados 239 --> Para isso, tentei verificar o problema usando getline e, em seguida, dividi a linha com delimiter='\t'. Encontrei 'quantidade de colunas' = len(split(getline)) = 209

erro: Não foi possível analisar a string "URL link". Nenhum problema com todas as linhas anteriores a esta.

Minhas perguntas são:

1. Por que recebo este erro do analisador na linha 1715281? Supondo que eu corrija o delimitador errado no início do meu código
2. É uma boa abordagem usar 'getline' e então comparar o número de colunas no CSV com o comprimento da linha de divisão?
3. Como gerenciar o erro "não é possível analisar a string: URL" quando todas as linhas anteriores não geram um problema?

Encontre abaixo meus códigos, comentários e mensagens de erro completas

import os.path
import pandas as pd
import numpy as np
import linecache

# data file available under: https://static.openfoodfacts.org/data/en.openfoodfacts.org.products.csv.gz
# it's .csv but delimiter is TAB

# generate the path to the file
data_local_path = os.getcwd() + '\\'
csv_filename = 'en.openfoodfacts.org.products.csv'
csv_local_path = data_local_path + csv_filename

# generate the path to create the file corrected
clean_filename = 'en.off.corrected.csv'
clean_local_path = data_local_path + clean_filename

# check if the file is already existing, if not then proceed to wrong delimiter replacement
if not os.path.isfile(clean_local_path):
    with open(csv_local_path, 'r',encoding='utf-8') as csv_file, open(clean_local_path, 'a', encoding='utf-8') as clean_file:
        for row in csv_file:
            clean_file.write(row.replace('\n\t', '\t'))

# columns type are defined under : https://static.openfoodfacts.org/data/data-fields.txt
column_names = pd.read_csv(clean_local_path, sep='\t', encoding = 'utf-8', nrows=0).columns.values
column_types = {col: 'Int64' for (col) in column_names if col.endswith (('_t', '_n'))}
column_types |= {col: float for (col) in column_names if col.endswith (('_100g', '_serving'))}
column_types |= {col: str for (col) in column_names if not col.endswith (('_t', '_n', '_100g', '_serving', '_tags'))}

print ("number of columns detected: ",len(column_names))
# output is "number of columns detected:  209"

print (column_names)

# Load the data
data = pd.read_csv(clean_local_path, sep='\t', encoding='utf_8', 
                   dtype=column_types, parse_dates=[col for (col) in column_names if col.endswith('_datetime')],
                   on_bad_lines='warn'
                  )
# display info
data.info()

A mensagem de erro na linha " data = pd.read_csv..." é:

...\AppData\Local\Temp\ipykernel_2824\611804071.py:2: ParserWarning: Skipping line 1715281: expected 209 fields, saw 239

  data = pd.read_csv(clean_local_path, sep='\t', encoding='utf_8',
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
File lib.pyx:2391, in pandas._libs.lib.maybe_convert_numeric()

ValueError: Unable to parse string "https://images.openfoodfacts.org/images/products/356/007/117/1049/front_fr.3.200.jpg"

During handling of the above exception, another exception occurred:

ValueError                                Traceback (most recent call last)
Cell In[6], line 2
      1 # Load the data
----> 2 data = pd.read_csv(clean_local_path, sep='\t', encoding='utf_8', 
      3                    dtype=column_types, parse_dates=[col for (col) in column_names if col.endswith('_datetime')],
      4                    on_bad_lines='warn'
      5                   )
      6 # display info
      7 data.info()

File ~\AppData\Local\Programs\Python\Python313\Lib\site-packages\pandas\io\parsers\readers.py:1026, in read_csv(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, date_format, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, encoding_errors, dialect, on_bad_lines, delim_whitespace, low_memory, memory_map, float_precision, storage_options, dtype_backend)
   1013 kwds_defaults = _refine_defaults_read(
   1014     dialect,
   1015     delimiter,
   (...)   1022     dtype_backend=dtype_backend,
   1023 )
   1024 kwds.update(kwds_defaults)
-> 1026 return _read(filepath_or_buffer, kwds)

File ~\AppData\Local\Programs\Python\Python313\Lib\site-packages\pandas\io\parsers\readers.py:626, in _read(filepath_or_buffer, kwds)
    623     return parser
    625 with parser:
--> 626     return parser.read(nrows)

File ~\AppData\Local\Programs\Python\Python313\Lib\site-packages\pandas\io\parsers\readers.py:1923, in TextFileReader.read(self, nrows)
   1916 nrows = validate_integer("nrows", nrows)
   1917 try:
   1918     # error: "ParserBase" has no attribute "read"
   1919     (
   1920         index,
   1921         columns,
   1922         col_dict,
-> 1923     ) = self._engine.read(  # type: ignore[attr-defined]
   1924         nrows
   1925     )
   1926 except Exception:
   1927     self.close()

File ~\AppData\Local\Programs\Python\Python313\Lib\site-packages\pandas\io\parsers\c_parser_wrapper.py:234, in CParserWrapper.read(self, nrows)
    232 try:
    233     if self.low_memory:
--> 234         chunks = self._reader.read_low_memory(nrows)
    235         # destructive to chunks
    236         data = _concatenate_chunks(chunks)

File parsers.pyx:838, in pandas._libs.parsers.TextReader.read_low_memory()

File parsers.pyx:921, in pandas._libs.parsers.TextReader._read_rows()

File parsers.pyx:1066, in pandas._libs.parsers.TextReader._convert_column_data()

File parsers.pyx:1105, in pandas._libs.parsers.TextReader._convert_tokens()

File parsers.pyx:1211, in pandas._libs.parsers.TextReader._convert_with_dtype()

File ~\AppData\Local\Programs\Python\Python313\Lib\site-packages\pandas\core\arrays\numeric.py:283, in NumericArray._from_sequence_of_strings(cls, strings, dtype, copy)
    277 @classmethod
    278 def _from_sequence_of_strings(
    279     cls, strings, *, dtype: Dtype | None = None, copy: bool = False
    280 ) -> Self:
    281     from pandas.core.tools.numeric import to_numeric
--> 283     scalars = to_numeric(strings, errors="raise", dtype_backend="numpy_nullable")
    284     return cls._from_sequence(scalars, dtype=dtype, copy=copy)

File ~\AppData\Local\Programs\Python\Python313\Lib\site-packages\pandas\core\tools\numeric.py:232, in to_numeric(arg, errors, downcast, dtype_backend)
    230 coerce_numeric = errors not in ("ignore", "raise")
    231 try:
--> 232     values, new_mask = lib.maybe_convert_numeric(  # type: ignore[call-overload]
    233         values,
    234         set(),
    235         coerce_numeric=coerce_numeric,
    236         convert_to_masked_nullable=dtype_backend is not lib.no_default
    237         or isinstance(values_dtype, StringDtype)
    238         and not values_dtype.storage == "pyarrow_numpy",
    239     )
    240 except (ValueError, TypeError):
    241     if errors == "raise":

File lib.pyx:2433, in pandas._libs.lib.maybe_convert_numeric()

ValueError: Unable to parse string "https://images.openfoodfacts.org/images/products/356/007/117/1049/front_fr.3.200.jpg" at position 1963

'Getline' e 'split' usados ​​para verificar ParserWarning referente à linha 1715281

#get the line where the first warning had occured
line = linecache.getline(csv_local_path,1715281)
print (line)
# Split the string using tab delimiter
split_list = line.split('\t')
# Output the result
print("concerning Parser Warning: Skipping line 1715281: expected 209 fields, saw 239")
print("number of data detected in the raw 1715281: ",len(split_list))
print ("number of columns detected in CSV: ",len(column_names))

# Output is:
# concerning Parser Warning: Skipping line 1715281: expected 209 fields, saw 239
# number of data detected in the raw 1715281:  209
# number of columns detected in CSV:  209

Eu tentei "on_bad_lines='skip'" mas sem sucesso