Problem parsing a particular table

[LeSasse]

(pystatis==0.4.0) I am not sure if i am not configuring this correctly, but with the following table:

https://www.regionalstatistik.de/genesis//online?operation=table&code=12411-01-01-4&bypass=true&levelindex=1&levelid=1744198492753#abreadcrumb

from pystatis.table import Table

pystatis_table = Table(name="12411-01-01-4")
pystatis_table.get_data()

i get the following error:

hit
Traceback (most recent call last):
  File "/home/leo/projects/geodata/src/test.py", line 4, in <module>
    pystatis_table.get_data()
  File "/home/leo/projects/geodata/.env/lib/python3.12/site-packages/pystatis/table.py", line 150, in get_data
    self.data = pd.read_csv(
                ^^^^^^^^^^^^
  File "/home/leo/projects/geodata/.env/lib/python3.12/site-packages/pandas/io/parsers/readers.py", line 1026, in read_csv
    return _read(filepath_or_buffer, kwds)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/leo/projects/geodata/.env/lib/python3.12/site-packages/pandas/io/parsers/readers.py", line 620, in _read
    parser = TextFileReader(filepath_or_buffer, **kwds)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/leo/projects/geodata/.env/lib/python3.12/site-packages/pandas/io/parsers/readers.py", line 1620, in __init__
    self._engine = self._make_engine(f, self.engine)
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/leo/projects/geodata/.env/lib/python3.12/site-packages/pandas/io/parsers/readers.py", line 1898, in _make_engine
    return mapping[engine](f, **self.options)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/leo/projects/geodata/.env/lib/python3.12/site-packages/pandas/io/parsers/c_parser_wrapper.py", line 161, in __init__
    self._validate_parse_dates_presence(self.names)  # type: ignore[has-type]
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/leo/projects/geodata/.env/lib/python3.12/site-packages/pandas/io/parsers/base_parser.py", line 243, in _validate_parse_dates_presence
    raise ValueError(
ValueError: Missing column provided to 'parse_dates': 'Zeit'

[pmayd]

Hi @LeSasse nice to hear from you again!

Thank you very much it seems like Regionalstatistik finally switched to Zensus format! I will fix this asap! Up until now Regionalstatistik was still on the old database version that used German headers and provided the data as flatfile (ffcsv). But this changed obviously and now the data is send as zip archive and with English headers. But we are already prepared to read this format as all other GENESIS databases are already on this newer version. So I will update pystatis with this fix.

[LeSasse]

Thanks so much for the quick response, that makes a lot of sense!

[pmayd]

Fixed with v0.5.1, please try it out!

[LeSasse]

that particular example works now but i get a similar error for a different table now:

from pystatis.table import Table

pystatis_table = Table(name="12411-02-03-5")
pystatis_table.get_data()

[LeSasse]

Cell In[7], line 4
      1 from pystatis.table import Table
      3 pystatis_table = Table(name="12411-02-03-5")
----> 4 pystatis_table.get_data()

File ~/projects/geodata/.env/lib/python3.12/site-packages/pystatis/table.py:167, in Table.get_data(self, prettify, area, startyear, endyear, timeslices, regionalvariable, regionalkey, stand, language, quality)
    162 raw_data_str = raw_data_header + "".join(
    163     line for line in raw_data_lines[1:] if line[:4].isdigit()
    164 )
    165 data_buffer = StringIO(raw_data_str)
--> 167 self.data = pd.read_csv(
    168     data_buffer,
    169     sep=";",
    170     na_values=["...", ".", "-", "/", "x"],
    171     decimal="," if language == "de" else ".",
    172     dtype={"1_variable_code": str, "1_variable_attribute_code": str},
    173     parse_dates=[config.LANG_TO_COL_MAPPING[language]["time"]],
    174     date_format="%Y-%m-%d",
    175 )
    177 if prettify:
    178     self.data = Table.parse_v5_table(self.data, db_name, language)

File ~/projects/geodata/.env/lib/python3.12/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 ~/projects/geodata/.env/lib/python3.12/site-packages/pandas/io/parsers/readers.py:620, in _read(filepath_or_buffer, kwds)
    617 _validate_names(kwds.get("names", None))
    619 # Create the parser.
--> 620 parser = TextFileReader(filepath_or_buffer, **kwds)
    622 if chunksize or iterator:
    623     return parser

File ~/projects/geodata/.env/lib/python3.12/site-packages/pandas/io/parsers/readers.py:1620, in TextFileReader.__init__(self, f, engine, **kwds)
   1617     self.options["has_index_names"] = kwds["has_index_names"]
   1619 self.handles: IOHandles | None = None
-> 1620 self._engine = self._make_engine(f, self.engine)

File ~/projects/geodata/.env/lib/python3.12/site-packages/pandas/io/parsers/readers.py:1898, in TextFileReader._make_engine(self, f, engine)
   1895     raise ValueError(msg)
   1897 try:
-> 1898     return mapping[engine](f, **self.options)
   1899 except Exception:
   1900     if self.handles is not None:

File ~/projects/geodata/.env/lib/python3.12/site-packages/pandas/io/parsers/c_parser_wrapper.py:161, in CParserWrapper.__init__(self, src, **kwds)
    155         self._validate_usecols_names(
    156             usecols,
    157             self.names,  # type: ignore[has-type]
    158         )
    160 # error: Cannot determine type of 'names'
--> 161 self._validate_parse_dates_presence(self.names)  # type: ignore[has-type]
    162 self._set_noconvert_columns()
    164 # error: Cannot determine type of 'names'

File ~/projects/geodata/.env/lib/python3.12/site-packages/pandas/io/parsers/base_parser.py:243, in ParserBase._validate_parse_dates_presence(self, columns)
    233 missing_cols = ", ".join(
    234     sorted(
    235         {
   (...)    240     )
    241 )
    242 if missing_cols:
--> 243     raise ValueError(
    244         f"Missing column provided to 'parse_dates': '{missing_cols}'"
    245     )
    246 # Convert positions to actual column names
    247 return [
    248     col if (isinstance(col, str) or col in columns) else columns[col]
    249     for col in cols_needed
    250 ]

ValueError: Missing column provided to 'parse_dates': 'time'

[pmayd]

Hm very interesting, the problem is that this table is too big and still returns a Job:
"Code": 98,
"Content": "Die Tabelle ist zu gross, um im Dialog abgerufen zu werden. Bitte nutzen sie den Webservice mit dem Parameter job=true (REST) um eine Verarbeitung im Hintergrund zu starten. (Mindestens ein Parameter enthält ungültige Werte. Er wurde angepasst, um den Service starten zu können.: stand)",

But my understanding was that with the switch to archived data (zip instead of raw text) jobs were no longer necessary. Well, I have to check. I guess our logic for handling jobs is no longer correct but we have a solution for this so I just need to find out why this breaks in this case.

[LeSasse]

thank you for your work on this, it is much appreciated, let me know if you need any assistance from my side!

[pmayd]

Hi @LeSasse , are you sure you are testing with the latest version 0.5.1? When I try the table, I don't have your issue:

t = pystatis.Table(name="12411-02-03-5")
t.get_data(quality="off")
t.data

logs:

WARNING:pystatis.http_helper:Die Tabelle ist zu groß, um direkt abgerufen zu werden. Es wird eine Verarbeitung im Hintergrund gestartet.
INFO:pystatis.http_helper:Database selected: regio
INFO:pystatis.http_helper:Code 99: Der Bearbeitungsauftrag wurde erstellt. Die Tabelle kann in Kürze als Ergebnis mit folgendem Namen abgerufen werden: 12411-02-03-5_010542044
WARNING:pystatis.http_helper:Verarbeitung im Hintergrund erfolgreich gestartet. Job-ID: 12411-02-03-5_010542044.
INFO:pystatis.http_helper:Code 0: erfolgreich
INFO:pystatis.http_helper:Code 0: erfolgreich
INFO:pystatis.http_helper:Code 0: erfolgreich
INFO:pystatis.http_helper:Code 0: erfolgreich
INFO:pystatis.http_helper:Code 0: erfolgreich
INFO:pystatis.http_helper:Code 0: erfolgreich
INFO:pystatis.http_helper:Code 0: erfolgreich
INFO:pystatis.http_helper:Code 0: erfolgreich
INFO:pystatis.http_helper:Code 0: erfolgreich
INFO:pystatis.http_helper:Code 0: erfolgreich
INFO:pystatis.http_helper:Code 0: erfolgreich
INFO:pystatis.http_helper:Code 0: erfolgreich
INFO:pystatis.http_helper:Verarbeitung im Hintergrund abgeschlossen. Ergebnis kann jetzt abgerufen werden über [/data/resultfile](https://file+.vscode-resource.vscode-cdn.net/data/resultfile) und Job-ID: 12411-02-03-5_010542044.
INFO:pystatis.cache:Data was successfully cached under [/Users/miay/.pystatis/data/12411-02-03-5/2ceb622297705aa5dc47/20250418.zip.](https://file+.vscode-resource.vscode-cdn.net/Users/miay/.pystatis/data/12411-02-03-5/2ceb622297705aa5dc47/20250418.zip.)
INFO:pystatis.http_helper:Database selected: regio
INFO:pystatis.http_helper:Code 0: erfolgreich

and I can successfully retrieve and see the data

[LeSasse]

Yes, I am fairly sure, but this table has been successfully downloaded with version 0.4.0, so maybe, something is cached somewhere from the old version, that is now interfering?

In [1]: import pystatis

In [2]: print(pystatis.__version__)
0.5.1

In [3]: t = pystatis.Table(name="12411-02-03-5")
   ...: t.get_data(quality="off")
   ...: t.data
hit
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[3], line 2
      1 t = pystatis.Table(name="12411-02-03-5")
----> 2 t.get_data(quality="off")
      3 t.data

[LeSasse]

sure enough rm -rf ~/.pystatis/data/* seems to have fixed it!

[pmayd]

That makes a lot of sense...actually pystatis is caching data and if the database changes on API side, all cached data is "wrong". We have a function pystatis.clear_cache() that you can use as convenience but I am not sure when and how to trigger this automatically...This is some annoyance of course because the user is not aware of that and suddenly gets errors like in your case, but I still think caching is meaningful since the data does not change that often...

One solution could be:

• in case of a cache hit AND and error -> delete cached data via pystatis.clear_cache(tablename) and retry...