BUG: append/concat for dataframes with DatetimeIndex having same timezone value but different tz classes results in change of Index type to generic Index (in place of DatetimeIndex)

[avnishbm]

• [ Y] I have checked that this issue has not already been reported.

• [Y ] I have confirmed this bug exists on the latest version of pandas.

• [ Y] (optional) I have confirmed this bug exists on the master branch of pandas.

---

Note: Please read this guide detailing how to provide the necessary information for us to reproduce your bug.

Code Sample, a copy-pastable example

import pandas as pd 
from datetime import datetime, timedelta
import dateutil
import psycopg2


#### Same time zone but using different tz classes though with same offset
IST_tz_offset = dateutil.tz.tz.tzoffset(None, 19800) # offset in seconds, 19800s equals 330 mins
IST_tz_postgres_offset = psycopg2.tz.FixedOffsetTimezone(offset=330, name=None) # it is in mins

# create list of lists
data1 = [[pd.Timestamp('2021-03-14 06:00:00', tz = IST_tz_offset), 15], [pd.Timestamp('2021-03-14 06:00:00', tz = IST_tz_offset) - timedelta(hours=1), 14], [pd.Timestamp('2021-03-14 06:00:00', tz = IST_tz_offset) - timedelta(hours=2), 12]] 
# Create the pandas DataFrame 
df1 = pd.DataFrame(data1, columns = ['datetime', 'value']) 
df1.set_index('datetime', inplace = True)

# initialize list of lists 
data2 = [[pd.Timestamp('2021-03-14 06:00:00', tz = IST_tz_postgres_offset) - timedelta(days=1), 10], [pd.Timestamp('2021-03-14 06:00:00', tz = IST_tz_postgres_offset) - timedelta(days = 1, hours=1), 9], [pd.Timestamp('2021-03-14 06:00:00', tz = IST_tz_postgres_offset) - timedelta(days = 1, hours=2), 8]] 
# Create the pandas DataFrame 
df2 = pd.DataFrame(data2, columns = ['datetime', 'value']) 
df2.set_index('datetime', inplace = True)

print ("Same timezones but different tz classes: df1.index[0] = {}, df2.index[0] = {}".format(df1.index[0], df2.index[0]))
print ("Same timezones but different tz classes: type(df1.index) = {}, type(df2.index) = {}".format(type(df1.index), type(df2.index)))
appended_df = df2.append(df1)
concatenated_df = pd.concat([df2, df1])
print ("Same timezones but different tz classes: type(appended_df.index) = {}, type(concatenated_df.index) = {}\n".format(type(appended_df.index), type(concatenated_df.index)))

Problem description

Have hit this issue when trying to append/concat dataframes with DatetimeIndex index types, where the data comes from different sources i.e. in this case, from postgres db and a data providers, and though the data has the same timezone (Asia/Kolkata), as can be seen from sample data printed in code, post append/concat the index type gets converted to "Index" in place of DatetimeIndex. This further caused an issue at our end, for things like resample() which in turn required DatetimeIndex type. The only different in the Timestamp data that we found was that their tz datatype was different even though both had the same offset value (19800 seconds or 330 mins, both being equal).

Sample data when printed:
Same timezones but different tz classes: df1.index[0] = 2021-03-14 06:00:00+05:30, df2.index[0] = 2021-03-13 06:00:00+05:30

Expected Output

In this case, since the timezone was essentially the same for the data, would have expected that the datatype for the index should not have changed after append or concat. It should have remained as DatetimeIndex type.

Output of pd.show_versions()

INSTALLED VERSIONS

commit : 7d32926
python : 3.8.2.final.0
python-bits : 64
OS : Darwin
OS-release : 20.3.0
Version : Darwin Kernel Version 20.3.0: Thu Jan 21 00:07:06 PST 2021; root:xnu-7195.81.3~1/RELEASE_X86_64
machine : x86_64
processor : i386
byteorder : little
LC_ALL : None
LANG : en_GB.UTF-8
LOCALE : en_GB.UTF-8

pandas : 1.2.2
numpy : 1.20.1
pytz : 2021.1
dateutil : 2.8.1
pip : 21.0.1
setuptools : 41.2.0
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : 4.6.2
html5lib : None
pymysql : None
psycopg2 : 2.8.6 (dt dec pq3 ext lo64)
jinja2 : None
IPython : None
pandas_datareader: None
bs4 : 4.9.3
bottleneck : None
fsspec : None
fastparquet : None
gcsfs : None
matplotlib : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pyxlsb : None
s3fs : None
scipy : None
sqlalchemy : 1.3.23
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
numba : None

[mroeschke]

Thanks for the report. As you mentioned "the timezone was essentially the same", but there is a risk that different timezone objects have different definitions which is mentioned in the docs https://pandas.pydata.org/pandas-docs/stable/user_guide/timeseries.html#working-with-time-zones

Because of this, pandas airs on the side of caution and doesn't not assume these timezones are equivalent (except for UTC) and therefore the merge returns an Index of object dtype.

We recommend normalizing timezones first if you are working with different timezone objects e.g. pd.to_datetime(..., utc=True).tz_convert('your_timezone'). Since this is the expected behavior, closing.