BUG: pd.DataFrame incorrectly aligns Series with mismatched DatetimeIndex after resample (Pandas 2.2.3)

[labodyn]

Pandas version checks

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

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

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd
data = {
    '0': pd.Series(
        data=[0.000209, 0.000100],
        index=pd.date_range("2024-11-07 16:00:00", periods=2, freq="8h", name="time").astype('datetime64[ms]'),
        name="0"
    ).resample('8h').last(),
    '1': pd.Series(
        data=[0.001012, 0.000461],
        index=pd.date_range("2023-11-22 16:00:00", periods=2, freq="8h", name="time").astype('datetime64[ms]'),
        name="1"
    ).resample('8h').last(),
}

print(pd.DataFrame(data))

                            0         1
time                                   
2023-11-22 16:00:00       NaN  0.001012
2024-11-07 16:00:00  0.000209       NaN
2936-07-12 00:00:00       NaN       NaN
2937-06-28 00:00:00       NaN       NaN

Issue Description

When creating a pd.DataFrame from two pd.Series with mismatched DatetimeIndex (after resample), the resulting DataFrame does not align the indices correctly. Instead, it includes unexpected datetime values.

Expected Behavior

                            0         1
time                                   
2023-11-22 16:00:00       NaN  0.001012
2023-11-23 00:00:00       NaN  0.000461
2024-11-07 16:00:00  0.000209       NaN
2024-11-08 00:00:00  0.000100       NaN

Installed Versions

INSTALLED VERSIONS ------------------ commit : 0691c5c python : 3.12.3 python-bits : 64 OS : Linux OS-release : 6.8.0-36-generic Version : #36-Ubuntu SMP PREEMPT_DYNAMIC Mon Jun 10 10:49:14 UTC 2024 machine : x86_64 processor : x86_64 byteorder : little LC_ALL : None LANG : C.UTF-8 LOCALE : C.UTF-8

pandas : 2.2.3
numpy : 2.0.2
pytz : 2024.1
dateutil : 2.9.0.post0
pip : 24.0
Cython : None
sphinx : None
IPython : 8.27.0
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : 4.12.3
blosc : None
bottleneck : None
dataframe-api-compat : None
fastparquet : None
fsspec : None
html5lib : None
hypothesis : None
gcsfs : None
jinja2 : 3.1.4
lxml.etree : None
matplotlib : 3.9.2
numba : 0.60.0
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
psycopg2 : None
pymysql : None
pyarrow : 17.0.0
pyreadstat : None
pytest : None
python-calamine : None
pyxlsb : None
s3fs : None
scipy : 1.14.1
sqlalchemy : None
tables : None
tabulate : 0.9.0
xarray : None
xlrd : None
xlsxwriter : None
zstandard : None
tzdata : 2024.1
qtpy : None
pyqt5 : None

[labodyn]

Changing the series datetime type to nanoseconds before combining prevents the bug.

series.index = series.index.astype('datetime64[ns]')

[labodyn]

Example to "fix" bug:

import pandas as pd
data = {
    '0': pd.Series(
        data=[0.000209, 0.000100],
        index=pd.date_range("2024-11-07 16:00:00", periods=2, freq="8h", name="time").astype('datetime64[ms]'),
        name="0"
    ).resample('8h').last(),
    '1': pd.Series(
        data=[0.001012, 0.000461],
        index=pd.date_range("2023-11-22 16:00:00", periods=2, freq="8h", name="time").astype('datetime64[ms]'),
        name="1"
    ).resample('8h').last(),
}

# Fix the bug, for ns datetime format the Series merging to DataFrame works fine.
for series in data.values():
    series.index = series.index.astype('datetime64[ns]')
 
print(pd.DataFrame(data))

                            0         1
time                                   
2023-11-22 16:00:00       NaN  0.001012
2023-11-23 00:00:00       NaN  0.000461
2024-11-07 16:00:00  0.000209       NaN
2024-11-08 00:00:00  0.000100       NaN

[rhshadrach]

Thanks for the report! @labodyn - you've checked the box that you've confirmed this exists on the main branch of pandas., but I am seeing the expected output there. Can you verify if you've run this on the main branch (not the latest released version).