readabs.read_abs_cat

  1"""Download *timeseries* data from the Australian Bureau of Statistics.
  2
  3Download timeseries data from the Australian Bureau of Statistics (ABS)
  4for a specified ABS catalogue identifier.
  5"""
  6
  7import calendar
  8from functools import cache
  9from pathlib import Path
 10from typing import Any, Unpack
 11
 12import pandas as pd
 13from pandas import DataFrame
 14
 15from readabs.abs_meta_data import metacol
 16from readabs.grab_abs_url import grab_abs_url, grab_abs_zip
 17from readabs.read_support import HYPHEN, ReadArgs
 18
 19# Constants
 20MAX_DATETIME_CHARS = 20
 21TABLE_DESC_ROW = 4
 22TABLE_DESC_COL = 1
 23
 24
 25# --- functions ---
 26# - public -
 27@cache  # minimise slowness for any repeat business
 28def read_abs_cat(
 29    cat: str,
 30    url: str = "",
 31    **kwargs: Unpack[ReadArgs],
 32) -> tuple[dict[str, DataFrame], DataFrame]:
 33    """For a specific catalogue identifier, return the complete ABS Catalogue information as DataFrames.
 34
 35    This function returns the complete ABS Catalogue information as a
 36    python dictionary of pandas DataFrames, as well as the associated metadata
 37    in a separate DataFrame. The function automates the collection of zip and
 38    excel files from the ABS website. If necessary, these files are downloaded,
 39    and saved into a cache directory. The files are then parsed to extract time
 40    series data, and the associated metadata.
 41
 42    By default, the cache directory is `./.readabs_cache/`. You can change the
 43    default directory name by setting the shell environment variable
 44    `READABS_CACHE_DIR` with the name of the preferred directory.
 45
 46    Parameters
 47    ----------
 48    cat : str
 49        The ABS Catalogue Number for the data to be downloaded and made
 50        available by this function. This argument must be specified in the
 51        function call.
 52
 53    **kwargs : Unpack[ReadArgs]
 54        The following parameters may be passed as optional keyword arguments.
 55
 56    url : str = ""
 57        The URL of an ABS landing page. Use this for discontinued series
 58        that are no longer in the ABS Time Series Directory. If provided,
 59        data will be retrieved from this URL instead of looking up the
 60        catalogue number. Example:
 61        `read_abs_cat(cat="8501.0", url="https://www.abs.gov.au/.../jun-2025")`
 62
 63    keep_non_ts : bool = False
 64        A flag for whether to keep the non-time-series tables
 65        that might form part of an ABS catalogue item. Normally, the
 66        non-time-series information is ignored, and not made available to
 67        the user.
 68
 69    history : str = ""
 70        Provide a month-year string to extract historical ABS data.
 71        For example, you can set history="dec-2023" to the get the ABS data
 72        for a catalogue identifier that was originally published in respect
 73        of Q4 of 2023. Note: not all ABS data sources are structured so that
 74        this technique works in every case; but most are.
 75
 76    verbose : bool = False
 77        Setting this to true may help diagnose why something
 78        might be going wrong with the data retrieval process.
 79
 80    ignore_errors : bool = False
 81        Normally, this function will cease downloading when
 82        an error in encountered. However, sometimes the ABS website has
 83        malformed links, and changing this setting is necessitated. (Note:
 84        if you drop a message to the ABS, they will usually fix broken
 85        links with a business day).
 86
 87    get_zip : bool = True
 88        Download the excel files in .zip files.
 89
 90    get_excel_if_no_zip : bool = True
 91        Only try to download .xlsx files if there are no zip
 92        files available to be downloaded. Only downloading individual excel
 93        files when there are no zip files to download can speed up the
 94        download process.
 95
 96    get_excel : bool = False
 97        The default value means that excel files are not
 98        automatically download. Note: at least one of `get_zip`,
 99        `get_excel_if_no_zip`, or `get_excel` must be true. For most ABS
100        catalogue items, it is sufficient to just download the one zip
101        file. But note, some catalogue items do not have a zip file.
102        Others have quite a number of zip files.
103
104    single_excel_only : str = ""
105        If this argument is set to a table name (without the
106        .xlsx extension), only that excel file will be downloaded. If
107        set, and only a limited subset of available data is needed,
108        this can speed up download times significantly. Note: overrides
109        `get_zip`, `get_excel_if_no_zip`, `get_excel` and `single_zip_only`.
110
111    selected_excel : tuple[str, ...] = ()
112        If set to a tuple of table names (without the .xlsx extension),
113        only those excel files will be downloaded. Useful when several
114        specific tables are needed and downloading the full zip would
115        be wasteful. Example:
116        `selected_excel=("62020001", "62020017", "62020X28")`.
117        Must be a tuple (not a list) because `read_abs_cat` uses an
118        internal cache that requires hashable arguments. Note: overrides
119        `get_zip`, `get_excel_if_no_zip`, `get_excel` and `single_zip_only`
120        when at least one matching file is found.
121
122    single_zip_only : str = ""
123        If this argument is set to a zip file name (without
124        the .zip extension), only that zip file will be downloaded.
125        If set, and only a limited subset of available data is needed,
126        this can speed up download times significantly. Note: overrides
127        `get_zip`, `get_excel_if_no_zip`, and `get_excel`.
128
129    cache_only : bool = False
130        If set to True, this function will only access
131        data that has been previously cached. Normally, the function
132        checks the date of the cache data against the date of the data
133        on the ABS website, before deciding whether the ABS has fresher
134        data that needs to be downloaded to the cache.
135
136    zip_file: str | Path = ""
137        If set to a specific zip file name (with or without the .zip
138        extension), this function will only extract data from that zip file
139        on the local file system. This may be useful for debugging purposes.
140
141    Returns
142    -------
143    tuple[dict[str, DataFrame], DataFrame]
144        The function returns a tuple of two items. The first item is a
145        python dictionary of pandas DataFrames (which is the primary data
146        associated with the ABS catalogue item). The second item is a
147        DataFrame of ABS metadata for the ABS collection.
148
149        Note:
150        You can retrieve non-timeseries data using the grab_abs_url()
151        function. That takes the URL for the ABS landing page for the ABS
152        collection you are interested in. The read_abs_cat function is for
153        ABS catalogue identifiers which are timeseries data, for which the
154        metadata can be extracted.
155
156    Example
157    -------
158
159    ```python
160    import readabs as ra
161    from pandas import DataFrame
162    cat_num = "6202.0"  # The ABS labour force survey
163    data: tuple[dict[str, DataFrame], DataFrame] = ra.read_abs_cat(cat=cat_num)
164    abs_dict, meta = data
165    ```
166
167    """
168    # --- get the time series data ---
169    zip_file = kwargs.get("zip_file")
170    raw_abs_dict = (
171        grab_abs_zip(zip_file, **kwargs) if zip_file else grab_abs_url(cat=cat, url=url, **kwargs)
172    )
173    response = _get_time_series_data(cat, raw_abs_dict, **kwargs)
174
175    if not response:
176        response = {}, DataFrame()
177
178    return response  # dictionary of DataFrames, and a DataFrame of metadata
179
180
181# - private -
182def _get_time_series_data(
183    cat: str,
184    abs_dict: dict[str, DataFrame],
185    **kwargs: Any,  # keep_non_ts, verbose, ignore_errors
186) -> tuple[dict[str, DataFrame], DataFrame]:
187    """Extract the time series data for a specific ABS catalogue identifier."""
188    # --- set up ---
189    cat = "<catalogue number missing>" if not cat.strip() else cat.strip()
190    new_dict: dict[str, DataFrame] = {}
191    meta_data = DataFrame()
192
193    # --- group the sheets and iterate over these groups
194    long_groups = _group_sheets(abs_dict)
195    for table, sheets in long_groups.items():
196        args = {
197            "cat": cat,
198            "from_dict": abs_dict,
199            "table": table,
200            "long_sheets": sheets,
201        }
202        new_dict, meta_data = _capture(new_dict, meta_data, args, **kwargs)
203    return new_dict, meta_data
204
205
206def _copy_raw_sheets(
207    from_dict: dict[str, DataFrame],
208    long_sheets: list[str],
209    to_dict: dict[str, DataFrame],
210    *,
211    keep_non_ts: bool,
212) -> dict[str, DataFrame]:
213    """Copy the raw sheets across to the final dictionary.
214
215    Used if the data is not in a timeseries format, and keep_non_ts
216    flag is set to True. Returns an updated final dictionary.
217    """
218    if not keep_non_ts:
219        return to_dict
220
221    for sheet in long_sheets:
222        if sheet in from_dict:
223            to_dict[sheet] = from_dict[sheet]
224        else:
225            # should not happen
226            raise ValueError(f"Glitch: Sheet {sheet} not found in the data.")
227    return to_dict
228
229
230def _capture(
231    to_dict: dict[str, DataFrame],
232    meta_data: DataFrame,
233    args: dict[str, Any],
234    **kwargs: Any,  # keep_non_ts, ignore_errors
235) -> tuple[dict[str, DataFrame], DataFrame]:
236    """Capture the time series data and meta data from an Excel file.
237
238    For a specific Excel file, capture *both* the time series data
239    from the ABS data files as well as the meta data. These data are
240    added to the input 'to_dict' and 'meta_data' respectively, and
241    the combined results are returned as a tuple.
242    """
243    # --- step 0: set up ---
244    keep_non_ts: bool = kwargs.get("keep_non_ts", False)
245    ignore_errors: bool = kwargs.get("ignore_errors", False)
246
247    # --- step 1: capture the meta data ---
248    short_names = [x.split(HYPHEN, 1)[1] for x in args["long_sheets"]]
249    if "Index" not in short_names:
250        print(f"Table {args['table']} has no 'Index' sheet.")
251        to_dict = _copy_raw_sheets(args["from_dict"], args["long_sheets"], to_dict, keep_non_ts=keep_non_ts)
252        return to_dict, meta_data
253    index = short_names.index("Index")
254
255    index_sheet = args["long_sheets"][index]
256    this_meta = _capture_meta(args["cat"], args["from_dict"], index_sheet)
257    if this_meta.empty:
258        to_dict = _copy_raw_sheets(args["from_dict"], args["long_sheets"], to_dict, keep_non_ts=keep_non_ts)
259        return to_dict, meta_data
260
261    meta_data = pd.concat([meta_data, this_meta], axis=0)
262
263    # --- step 2: capture the actual time series data ---
264    data = _capture_data(meta_data, args["from_dict"], args["long_sheets"], **kwargs)
265    if len(data):
266        to_dict[args["table"]] = data
267    else:
268        # a glitch: we have the metadata but not the actual data
269        error = f"Unexpected: {args['table']} has no actual data."
270        if not ignore_errors:
271            raise ValueError(error)
272        print(error)
273        to_dict = _copy_raw_sheets(args["from_dict"], args["long_sheets"], to_dict, keep_non_ts=keep_non_ts)
274
275    return to_dict, meta_data
276
277
278def _capture_data(
279    abs_meta: DataFrame,
280    from_dict: dict[str, DataFrame],
281    long_sheets: list[str],
282    **kwargs: Any,  # verbose
283) -> DataFrame:
284    """Take a list of ABS data sheets and stitch them into a DataFrame.
285
286    Find the DataFrames for those sheets in the from_dict, and stitch them
287    into a single DataFrame with an appropriate PeriodIndex.
288    """
289    # --- step 0: set up ---
290    verbose: bool = kwargs.get("verbose", False)
291    merged_data = DataFrame()
292    header_row: int = 8
293
294    # --- step 1: capture the time series data ---
295    # identify the data sheets in the list of all sheets from Excel file
296    data_sheets = [x for x in long_sheets if x.split(HYPHEN, 1)[1].startswith("Data")]
297
298    for sheet_name in data_sheets:
299        if verbose:
300            print(f"About to cature data from {sheet_name=}")
301
302        # --- capture just the data, nothing else
303        sheet_data = from_dict[sheet_name].copy()
304
305        # get the columns
306        header = sheet_data.iloc[header_row]
307        sheet_data.columns = pd.Index(header)
308        sheet_data = sheet_data[(header_row + 1) :]
309
310        # get the row indexes
311        sheet_data = _index_to_period(sheet_data, sheet_name, abs_meta, verbose=verbose)
312
313        # --- merge data into a single dataframe
314        if len(merged_data) == 0:
315            merged_data = sheet_data
316        else:
317            merged_data = merged_data.merge(
318                right=sheet_data,
319                how="outer",
320                left_index=True,
321                right_index=True,
322                suffixes=("", ""),
323            )
324
325    # --- step 2 - final tidy-ups
326    # remove NA rows
327    merged_data = merged_data.dropna(how="all")
328    # check for NA columns - rarely happens
329    # Note: these empty columns are not removed,
330    # but it is useful to know they are there
331    if merged_data.isna().all().any() and verbose:
332        na_cols = merged_data.columns[merged_data.isna().all()]
333        print(f"Caution: These columns are all NA: {list(na_cols)}")
334
335    # check for duplicate columns - should not happen
336    # Note: these duplicate columns are removed
337    duplicates = merged_data.columns.duplicated()
338    if duplicates.any():
339        if verbose:
340            dup_table = abs_meta[metacol.table].iloc[0]
341            print(f"Note: duplicates removed from {dup_table}: " + f"{merged_data.columns[duplicates]}")
342        merged_data = merged_data.loc[:, ~duplicates].copy()
343
344    # make the data all floats.
345    return merged_data.astype(float).sort_index()
346
347
348def _index_to_period(sheet_data: DataFrame, sheet_name: str, abs_meta: DataFrame, *, verbose: bool) -> DataFrame:
349    """Convert the index of a DataFrame to a PeriodIndex."""
350    index_column = sheet_data[sheet_data.columns[0]].astype(str)
351    sheet_data = sheet_data.drop(sheet_data.columns[0], axis=1)
352    long_row_names = index_column.str.len() > MAX_DATETIME_CHARS  # 19 chars in datetime str
353    if verbose and long_row_names.any():
354        print(f"You may need to check index column for {sheet_name}")
355    index_column = index_column.loc[~long_row_names]
356    sheet_data = sheet_data.loc[~long_row_names]
357
358    proposed_index = pd.to_datetime(index_column)
359
360    # get the correct period index
361    short_name = sheet_name.split(HYPHEN, 1)[0]
362    series_id = sheet_data.columns[0]
363    freq_value = abs_meta[abs_meta[metacol.table] == short_name].loc[series_id, metacol.freq]
364    freq = str(freq_value).upper().strip()[0]
365    freq = "Y" if freq == "A" else freq  # pandas prefers yearly
366    freq = "Q" if freq == "B" else freq  # treat Biannual as quarterly
367    if freq not in ("Y", "Q", "M", "D"):
368        print(f"Check the frequency of the data in sheet: {sheet_name}")
369
370    # create an appropriate period index
371    if freq:
372        if freq in ("Q", "Y"):
373            month = str(calendar.month_abbr[proposed_index.dt.month.max()]).upper()
374            freq = f"{freq}-{month}"
375        sheet_data.index = pd.PeriodIndex(proposed_index, freq=freq)
376    else:
377        raise ValueError(f"With sheet {sheet_name} could not determime PeriodIndex")
378
379    return sheet_data
380
381
382def _capture_meta(
383    cat: str,
384    from_dict: dict[str, DataFrame],
385    index_sheet: str,
386) -> DataFrame:
387    """Capture the metadata from the Index sheet of an ABS excel file.
388
389    Returns a DataFrame specific to the current excel file.
390    Returning an empty DataFrame, means that the meta data could not
391    be identified. Meta data for each ABS data item is organised by row.
392    """
393    # --- step 0: set up ---
394    frame = from_dict[index_sheet]
395
396    # --- step 1: check if the metadata is present in the right place ---
397    # Unfortunately, the header for some of the 3401.0
398    #                spreadsheets starts on row 10
399    starting_rows = 8, 9, 10
400    required = metacol.did, metacol.id, metacol.stype, metacol.unit
401    required_set = set(required)
402
403    header_row = None
404    header_columns = None
405    for row in starting_rows:
406        columns = frame.iloc[row]
407        if required_set.issubset(set(columns)):
408            header_row = row
409            header_columns = columns
410            break
411
412    if header_row is None or header_columns is None:
413        print(f"Table has no metadata in sheet {index_sheet}.")
414        return DataFrame()
415
416    # --- step 2: capture the metadata ---
417    file_meta = frame.iloc[header_row + 1 :].copy()
418    file_meta.columns = pd.Index(header_columns)
419
420    # make damn sure there are no rogue white spaces
421    for col in required:
422        file_meta[col] = file_meta[col].str.strip()
423
424    # remove empty columns and rows
425    file_meta = file_meta.dropna(how="all", axis=1).dropna(how="all", axis=0)
426
427    # populate the metadata
428    file_meta[metacol.table] = index_sheet.split(HYPHEN, 1)[0]
429    tab_desc_value = frame.iloc[TABLE_DESC_ROW, TABLE_DESC_COL]
430    tab_desc = str(tab_desc_value).split(".", 1)[-1].strip()
431    file_meta[metacol.tdesc] = tab_desc
432    file_meta[metacol.cat] = cat
433
434    # drop last row - should just be copyright statement
435    file_meta = file_meta.iloc[:-1]
436
437    # set the index to the series_id
438    file_meta.index = pd.Index(file_meta[metacol.id])
439
440    return file_meta
441
442
443def _group_sheets(
444    abs_dict: dict[str, DataFrame],
445) -> dict[str, list[str]]:
446    """Group the sheets from an Excel file."""
447    keys = list(abs_dict.keys())
448    long_pairs = [(x.split(HYPHEN, 1)[0], x) for x in keys]
449
450    def group(p_list: list[tuple[str, str]]) -> dict[str, list[str]]:
451        groups: dict[str, list[str]] = {}
452        for x, y in p_list:
453            if x not in groups:
454                groups[x] = []
455            groups[x].append(y)
456        return groups
457
458    return group(long_pairs)
459
460
461# --- initial testing ---
462if __name__ == "__main__":
463
464    def simple_test() -> None:
465        """Test the read_abs_cat function."""
466        # ABS Catalogue ID 8731.0 has a mix of time
467        # series and non-time series data. Also,
468        # it has unusually structured Excel files. So, a good test.
469
470        print("Starting test.")
471
472        d, _m = read_abs_cat("8731.0", keep_non_ts=False, verbose=False)
473        print(f"--- {len(d)=} ---")
474        print(f"--- {d.keys()=} ---")
475        for table in d:
476            freq_str = getattr(d[table].index, "freqstr", "Unknown")
477            print(f"{table=} {d[table].shape=} {freq_str=}")
478
479        print ("=" * 20)
480
481        # Optional: exercise the local zip_file path. Requires a developer to
482        # have a pre-downloaded ABS zip at this location; skipped if absent.
483        local_zip = Path(".test-data/Qrtly-CPI-Time-series-spreadsheets-all.zip")
484        if local_zip.exists():
485            d, _m = read_abs_cat("", zip_file=str(local_zip), verbose=False)
486            print(f"--- {len(d)=} ---")
487            print(f"--- {d.keys()=} ---")
488            for table in d:
489                freq_str = getattr(d[table].index, "freqstr", "Unknown")
490                print(f"{table=} {d[table].shape=} {freq_str=}")
491        else:
492            print(f"Skipping local zip_file test: {local_zip} not present.")
493
494        print("Test complete.")
495
496    simple_test()