Error within nwis.get_record function

[snkligon-ERDC]

When requesting records from sites: '411605084240800', '411607084241200', '411610084240800', an error is returned:

ValueError: You are trying to merge on object and datetime64[ns, pytz.FixedOffset(-300)] columns. If you wish to proceed you should use pd.concat.

[SorooshMani-NOAA]

I get the same error when trying to use stateCd:

from dataretrieval.nwis import get_record
get_record(stateCd='MD', start=datetime.now(), end=datetime.now()-timedelta(days=1), service='iv')

or

from dataretrieval.nwis import get_iv
get_iv(start=datetime.now(), end=datetime.now()-timedelta(days=1),stateCd='MD')

Both lines result in something like:

----> 1 get_record(stateCd='MD', start=datetime.now(), end=datetime.now()-timedelta(days=1),service='iv')

File ~/miniconda3/envs/data/lib/python3.10/site-packages/dataretrieval/nwis.py:576, in get_record(sites, start, end, state, service, *args, **kwargs)
    573     raise TypeError('Unrecognized service: {}'.format(service))
    575 if service == 'iv':
--> 576     df, _ = get_iv(sites=sites, startDT=start, endDT=end, **kwargs)
    577     return df
    579 elif service == 'dv':

File ~/miniconda3/envs/data/lib/python3.10/site-packages/dataretrieval/nwis.py:430, in get_iv(start, end, **kwargs)
    428 start = kwargs.pop('startDT', start)
    429 end = kwargs.pop('endDT', end)
--> 430 return _iv(startDT=start, endDT=end, **kwargs)

File ~/miniconda3/envs/data/lib/python3.10/site-packages/dataretrieval/nwis.py:435, in _iv(**kwargs)
    433 def _iv(**kwargs):
    434     response = query_waterservices('iv', format='json', **kwargs)
--> 435     return _read_json(response.json()), _set_metadata(response, **kwargs)

File ~/miniconda3/envs/data/lib/python3.10/site-packages/dataretrieval/nwis.py:685, in _read_json(json, multi_index)
    682             merged_df = record_df
    684         else:
--> 685             merged_df = update_merge(merged_df, record_df, na_only=True,
    686                                      on=['site_no', 'datetime'])
    688 merged_df = format_response(merged_df)
    689 return merged_df

File ~/miniconda3/envs/data/lib/python3.10/site-packages/dataretrieval/utils.py:79, in update_merge(left, right, na_only, on, **kwargs)
     68 """Performs a combination update and merge.
     69
     70 Args:
   (...)
     75 TODO: na_only
     76 """
     77 #df = left.merge(right, how='outer',
     78 #                left_index=True, right_index=True)
---> 79 df = left.merge(right, how='outer', on=on, **kwargs)
     82 # check for column overlap and resolve update
     83 for column in df.columns:
     84     #if duplicated column, use the value from right

File ~/miniconda3/envs/data/lib/python3.10/site-packages/pandas/core/frame.py:10083, in DataFrame.merge(self, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)
.
.
.
File ~/miniconda3/envs/data/lib/python3.10/site-packages/pandas/core/reshape/merge.py:1349, in _MergeOperation._maybe_coerce_merge_keys(self)
   1347     raise ValueError(msg)
   1348 elif not needs_i8_conversion(lk.dtype) and needs_i8_conversion(rk.dtype):
-> 1349     raise ValueError(msg)
   1350 elif isinstance(lk.dtype, DatetimeTZDtype) and not isinstance(
   1351     rk.dtype, DatetimeTZDtype
   1352 ):
   1353     raise ValueError(msg)

ValueError: You are trying to merge on object and datetime64[ns, pytz.FixedOffset(-240)] columns. If you wish to proceed you should use pd.concat

Interestingly, if I try the testcase https://github.com/USGS-python/dataretrieval/blob/1e0560abdf21a1d85676ed53e068c17643c23c2c/tests/nwis_test.py#L26-L33 it works fine!

[elbeejay]

There no longer seems to be an error when running either of the two examples given by @SorooshMani-NOAA. Maybe this problem was related to some of the datetime handling and was fixed by #62?

[SorooshMani-NOAA]

@elbeejay this was my first attempt at using dataretrieval. Now that I think back, maybe it was the same dataframe time zone handling that you fixed in #60

[elbeejay]

Yes, @SorooshMani-NOAA, I've just checked out the old code and was able to re-create the error with the example snippet above. So I can confirm that we have resolved this issue. Thanks!