Pandas returns different datetime formats with iloc vs. with condition

user_14775478 0
Viewed 768

I am struggling to get consistent datetime formatting out of a pandas array. I have a df with a calendar date colum of dtype datetime64[ns].

When I access the calendar date directly by iloc, say index 966, I get a Timestamp type

df.iloc[966]['Calendar Day']

Output 1:

Timestamp('1998-09-26 00:00:00')

However, when access the same line with a conditional statement, I get a different output format

a = df[ (df['colA'] == condA ) & (df['colB'] == condB ))]['Calendar Day']
a

results in output 2:

966   1998-09-26
Name: Calendar Day, dtype: datetime64[ns]

My condition is designed such that I can only report at most 1 line, or nan.

I am puzzled: The 2 statement look equal to me, given both times I access the same col of the same row of the same df, once by index, once by condition.

How do I make them equal? I would like to calculate a different, such as

abs( (a-b).days )

to get the relative time difference. That results in AttributeError: 'Series' object has no attribute 'days'

Thanks!

pandas datetime python
2 Answers

A scalar index value passed to iloc gives you a scalar value in return (timestamp in this case), while boolean masking gives you a pd.Series in return. So you neither have different formatting, nor different datatypes.

scalar index:

import pandas as pd
df = pd.DataFrame({'date': pd.to_datetime(['1998-09-25', '1998-09-26', '1998-09-27']),
                   'v0': [1,2,3], 'v1': [4,5,6]})

# a scalar index value gives you a specific value from one row/column:
df.iloc[1]['date']
# Timestamp('1998-09-26 00:00:00')

boolean masking:

# this gives you a series with one element:
# mask:
m = (df['v0'] == 2) & (df['v1'] == 5)
# note that m is also a series, not a scalar value:
# 0    False
# 1    True
# 2    False
# Name: v0, dtype: bool

df[m]['date']
# ...and so is the result if you apply the mask:
# 1   1998-09-26
# Name: date, dtype: datetime64[ns]

same dtype actually...

# note that the dtype also timestamp:
df[m]['date'].iloc[0]
# Timestamp('1998-09-26 00:00:00')

vs. index slice:

df.iloc[1:2]['date'] # ...also gives you pd.Series
# 1   1998-09-26
# Name: date, dtype: datetime64[ns]

...to get the arithmetic working use the dt accessor (since you're working with a Series):

a, b = df.iloc[1]['date'], df[m]['date']
abs( (a-b).dt.days )
# 1    0
# Name: date, dtype: int64
user_101974180

It is probably because df.iloc[966] creates a Series object when given only one index and the types are all changed to a common dtype, whereas in your version with conditional statement there is no type conversion because no Series object is created.
You can confirm this by replacing df.iloc[966] with df.iloc[966:967] and check that you get same type than with conditional statement.
See Pandas DataFrame iloc spoils the data type

user_39658880
Related