Column Types and Memory
The Water Data getters return measurement columns such as value as
float64, and time columns as detailed in Datetime Information. With
pandas 3, text columns that have values, such as monitoring_location_id,
use the pandas default string type. Text columns take most of a result’s
memory. The examples below show how the pyarrow package reduces the memory a
pandas DataFrame uses.
Installing pyarrow
With pandas 3, pandas stores text columns in Arrow memory whenever pyarrow is
installed. Installing pyarrow alone can save considerable memory, and no
dataretrieval call changes.
pip install pyarrow
In a daily-values query for one monitoring location (12,784 rows), the
columns other than geometry used 6.2 MiB without pyarrow and 2.7 MiB with
it, as reported by memory_usage(deep=True). To check which storage a text
column uses with pandas 3:
>>> from dataretrieval import waterdata
>>> df, md = waterdata.get_daily(
... monitoring_location_id="USGS-05427718",
... parameter_code="00060",
... time="1990-01-01/2024-12-31",
... )
>>> df["monitoring_location_id"].dtype.storage
'pyarrow'
pandas 2 stores text as object whether or not pyarrow is installed. Python
3.10 gets pandas 2, because pandas 3 requires Python 3.11 or later.
Converting to Arrow dtypes
With pandas 3, installing pyarrow changes only the storage of text columns.
They keep the pandas default string type, and the other columns keep their
NumPy types. To reduce memory further, convert the columns other than
geometry to Arrow dtypes:
>>> def to_arrow(frame):
... cols = frame.columns.drop("geometry", errors="ignore")
... floats = frame.select_dtypes("float").columns
... frame[cols] = frame[cols].convert_dtypes(dtype_backend="pyarrow")
... frame[floats] = frame[floats].astype("double[pyarrow]")
... return frame
...
>>> df = to_arrow(df)
>>> df.dtypes.drop("geometry")
time_series_id string[pyarrow]
monitoring_location_id string[pyarrow]
parameter_code string[pyarrow]
statistic_id string[pyarrow]
time timestamp[us][pyarrow]
value double[pyarrow]
unit_of_measure string[pyarrow]
approval_status string[pyarrow]
qualifier object
last_modified timestamp[us, tz=UTC][pyarrow]
daily_id string[pyarrow]
dtype: object
The conversion reduces the result above to 2.3 MiB. The listing is from
pandas 3. With pandas 2, the time columns are timestamp[ns][pyarrow] and
timestamp[ns, tz=UTC][pyarrow], and the other types are the same.
convert_dtypesturns a float column whose values are all whole numbers into an integer column.to_arrowthen casts the float columns todouble[pyarrow], sovaluestays floating point. pandas 3.1 deprecatedconvert_integer, the keyword that disabled the integer conversion.convert_dtypesraisesTypeErroron a geopandasgeometrycolumn, soto_arrowleaves it out. The result stays aGeoDataFrame, anddf.to_parquet(path)writes GeoParquet.Columns of lists, such as
qualifier, stayobject.A column with no values in a result becomes
null[pyarrow]. For example,get_monitoring_locations()returns noaquifer_codefor stream gages. Before you write results from several queries to one Parquet dataset or database table, cast such a column to the type it has when populated:>>> import pandas as pd >>> import pyarrow as pa >>> sites, md = waterdata.get_monitoring_locations( ... monitoring_location_id=["USGS-05427718", "USGS-01646500"] ... ) >>> sites = to_arrow(sites) >>> sites["aquifer_code"].dtype null[pyarrow] >>> sites = sites.astype({"aquifer_code": pd.ArrowDtype(pa.string())}) >>> sites["aquifer_code"].dtype string[pyarrow]
The string alias
"string[pyarrow]"selects a different pandas type, which Parquet stores aslarge_string.