Rowwise min() and max() fails for column with NaNs
I would say the best solution is to use the appropriate dtype
. Pandas provides a very well integrated datetime
dtype
. So note, you are using object
dtypes...
>>> df
date_a date_b
0 2015-01-01 2012-07-01
1 2012-06-01 2013-01-01
2 NaN 2014-03-01
3 2016-06-01 2013-04-01
>>> df.dtypes
date_a object
date_b object
dtype: object
But note, the problem disappears when you use
>>> df2 = df.apply(pd.to_datetime)
>>> df2
date_a date_b
0 2015-01-01 2012-07-01
1 2012-06-01 2013-01-01
2 NaT 2014-03-01
3 2016-06-01 2013-04-01
>>> df2.min(axis=1)
0 2012-07-01
1 2012-06-01
2 2014-03-01
3 2013-04-01
dtype: datetime64[ns]
This appears to happen when date
objects are mixed with floats (such as NaN
) in columns. By default, the numeric_only
flag is set because of the single float value. For example, replace your df_nan
with this:
df_float = pd.DataFrame({'date_a' : [date(2015, 1, 1), date(2012, 6, 1),
1.023, date(2016, 6, 1)],
'date_b' : [date(2012, 7, 1), 3.14,
date(2014, 3, 1), date(2013, 4, 1)]})
print(df_float.max(1))
0 NaN
1 NaN
2 NaN
3 NaN
dtype: float64
If the flag is manually set to false, this would rightly throw a TypeError
because:
print(date(2015, 1, 1) < 1.0)
TypeError Traceback (most recent call last)
<ipython-input-362-ccbf44ddb40a> in <module>()
1
----> 2 print(date(2015, 1, 1) < 1.0)
TypeError: unorderable types: datetime.date() < float()
However, pandas seems to coerce everything to NaN
. As a workaround, converting to str
using df.astype
appears to do it:
out = df_nan.astype(str).max(1)
print(out)
0 2015-01-01
1 2013-01-01
2 nan
3 2016-06-01
dtype: object
In this case, sorting lexicographically yields the same solution as before.
Otherwise, as juan suggests, you can cast to datetime
using pd.to_datetime
:
out = df_nan.apply(pd.to_datetime, errors='coerce').max(1)
print(out)
0 2015-01-01
1 2013-01-01
2 2014-03-01
3 2016-06-01
dtype: datetime64[ns]