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