Column Names get Mangled for Dam Headwater / Tailwater Stage Observations

[MickeyRush]

The column names assigned to the output of get_record() come out with a mess of quotes and brackets, for dam headwater and tailwater stage observations.

sns = [
'03612600', # Ohio River at Olmsted Dam
'03399800', # Ohio River at Smithland Dam
]
start_date = '2014-10-01'
end_date = '2023-09-30'
param_code = '00065'

for sn in sns:
df = nwis.get_record(sn, start_date, end_date, parameterCd=param_code)
print(df.columns)

Index(['site_no', '00065_headwater, [headwater',
'00065_headwater, [headwater_cd', '00065_tailwater',
'00065_tailwater_cd'],
dtype='object')
Index(['site_no', '00065_headwater', '00065_headwater_cd',
'00065_stage - tailwater, [tailwater',
'00065_stage - tailwater, [tailwater_cd'],
dtype='object')

When written to a csv:
datetime,site_no,00065_headwater,00065_headwater_cd,"00065_stage - tailwater, [tailwater","00065_stage - tailwater, [tailwater_cd"
2014-10-01 05:00:00+00:00,03399800,13.66,A,12.12,A
2014-10-01 05:15:00+00:00,03399800,13.66,A,12.07,A
2014-10-01 05:30:00+00:00,03399800,13.66,A,12.11,A
2014-10-01 05:45:00+00:00,03399800,13.66,A,12.15,A

[MickeyRush]

Temporary fix:

drop_strs = ['headwater, [', 'stage - tailwater, [']
cols = []
for col in df.columns:
for drop_str in drop_strs:
if drop_str in col:
col = col.replace(drop_str, '')
cols.append(col)
df.columns = cols
print(df.columns)

Index(['site_no', '00065_headwater', '00065_headwater_cd', '00065_tailwater',
'00065_tailwater_cd'],
dtype='object')
Index(['site_no', '00065_headwater', '00065_headwater_cd', '00065_tailwater',
'00065_tailwater_cd'],
dtype='object')

[ehinman]

Hi @MickeyRush, thanks for pointing this out. It's been a busy couple weeks for other tasks, but I will look into this as soon as I can (~end of week), and thank you for bringing it to our attention.

[ehinman]

Follow up: I've re-created the issue and have considered options for fixing. The problem is the choice of characters used to describe each gage-parameter, which represent an unexpected combination not covered in the current function syntax. At this point, we are gearing up to create new functions to accommodate the new USGS water data APIs. With the new APIs, data are served in a long format (rather than wide, where each pcode is its own column), and I believe this will cease to be an issue. Thanks for your patience with this. We've had limited resources lately to address edge cases!

[ehinman]

Hey Mickey,

What do you think about trying out the new waterdata module to fix this problem? You can use the new get_time_series_metadata() function to pick out specific continuous timeseries you want, like this:

df, md = waterdata.get_time_series_metadata(
    monitoring_location_id=["USGS-03612600", "USGS-03399800"],
     parameter_code="00065"
)
print(df)

                     time_series_id                    geometry  \
0  00ba713886be442d883a9ce74b5178ce  POINT (-88.42726 37.15922)   
1  0758782ca5c64cbba44873b78afa40e6  POINT (-88.42726 37.15922)   
2  15beb94252164ce0b9a77a90edce7528  POINT (-89.05840 37.17922)   
3  7ca465077c26415c93fd0549c697a270  POINT (-89.05840 37.17922)   
4  8568cfe311b240a0923181bfdc2b1780  POINT (-88.42726 37.15922)   
5  aa47311c6b3c432e9036dadb8364240e  POINT (-89.05840 37.17922)   

  computation_identifier sublocation_identifier state_name  \
0      Max At Event Time                   None   Illinois   
1          Instantaneous      STAGE - TAILWATER   Illinois   
2          Instantaneous              HEADWATER   Illinois   
3          Instantaneous              TAILWATER   Illinois   
4          Instantaneous              HEADWATER   Illinois   
5      Max At Event Time                   None   Illinois   

                    end_utc  primary                      begin  \
0                       NaT  Primary                        NaT   
1 2025-12-04 15:00:00+00:00  Primary 2007-10-01 06:00:00.000001   
2 2025-12-04 16:00:00+00:00  Primary 2013-03-02 06:00:00.000001   
3 2025-12-04 17:00:00+00:00  Primary 2023-04-12 23:00:00.000001   
4 2025-12-04 15:00:00+00:00  Primary 2007-10-01 06:00:00.000001   
5                       NaT  Primary                        NaT   

  computation_period_identifier monitoring_location_id  ... web_description  \
...
4 2025-12-04 09:00:00.000001                  None  
5                        NaT                  None  

[6 rows x 22 columns]

And then you can select the particular timeseries you want and pull year-by-year using the following:

# Pulls just the last year of instantaneous for USGS-03399800 with HEADWATER sublocation identifier
# time series id 8568cfe311b240a0923181bfdc2b1780
df, md = waterdata.get_continuous(
     time_series_id="8568cfe311b240a0923181bfdc2b1780",
     time="2024-12-04/2025-12-04"
 )

Check out the documentation for the new module: https://doi-usgs.github.io/dataretrieval-python/reference/waterdata.html