Setting "bad" values to Infinity

Question

Consider..

pos = SkyCoord.from_name('m67')
size = Quantity(1., unit="deg")
 

ra = pos.ra.degree
dec = pos.dec.degree
fov = 1.0
sql = '''SELECT mag_g, mag_r, mag_z
            FROM ls_dr8.tractor
            WHERE Q3C_RADIAL_QUERY(ra,dec,{0},{1},{2})
'''.format(ra, dec, fov/2)
df0 = qc.query(sql=sql, fmt='pandas')

All three columns in df0 are set to dtype=object because each column has one or more value set to 'Infinity' or some it appears. One has to find and convert the 'Infinity' values to something numeric and then convert the columns to numeric, before you can do arithmetic operations on the columns.  Here is what I did:

df0[df0.eq('Infinity').any(1)] = 99.
df1 = df0.astype({'mag_g': 'float64','mag_r': 'float64','mag_z': 'float64'})

When I am less lazy, I will re-write the second line so that columns with dtype=object are detected and updated in some kind of loop. But I digress.

I do not know if that value is in the table or what's in the table gets converted to 'Infinity' by qc.query. Maybe the returned value should be "np.inf" or some other common floating point infinity indicator? 

FWIW, I discovered the pandas version installed in datalab is 0.25.0, but the most recent release is 1.0+. I added a question for that.

Cheers,

dave

Answer 1

Hi, thanks for reaching out.
There's an easier way: The pandas read_csv() function takes a kwarg 'na_values'. This can be used to specify additional values to be treated as NaN. So the trick is to get the query result as a CSV formatted string, and convert locally, using na_values. Data Lab has a 'convert' function handy:

from dl.helpers.utils import convert
result = qc.query(sql=sql, fmt='csv')
df0 = convert(result,'pandas',na_values='Infinity') # <-- this can also be a sequence of strings
df0.dtypes
  mag_g    float64
  mag_r    float64
  mag_z    float64
  dtype: object

The other option of course would be to include another qualifier in the WHERE statement to filter out any unwanted rows on the DB's end.

Regarding our pandas version: we will soon be moving to a new notebook server, and will update the stack. Likely including pandas.

Let us know of we can be of any further assistance.

Cheers,

Robert for the DL team