I'm running into an interesting issue. I've recreated the issue as best I can and am reproducing the same error. Essentially I have a script that is running through a database and collecting information on different assets that have datetime64[ns, UTC] dtypes. This script can return a dataframe with data (df1_utc) or an empty dataframe (empty_df2_utc). I also have empty dataframe's (result_table & result_table2) that I want to merge with the dataframe's from my script (df1 to result_table & empty_df2 to result_table2).
When the script's dataframe is empty it throws me the error below, but when it is populated it executes the script perfectly and without errors. Can someone help me to figure out why this is happening, and a possible solution?
FYI most of the assets have populated dataframe's, there a few that are not populated. Essentially I just need a solution that I can apply to my code so when it loops through the entire database it can successfully merge the two.
I have tried pd.concat and it works, but I'm trying to understand the root cause of the issue and handle it there rather than applying a band-aid downstream.
Thank you!
Code:
import pandas as pd
from datetime import date, datetime
import numpy as np
data1 = [['2022-06-20 12:05:00+00:00', 13.6]]
df1_utc = pd.DataFrame(data1, columns=['timestamp', 'VoltageDCDC12V'])
df1_utc['timestamp'] = pd.to_datetime(df1_utc['timestamp'], utc=True)
df1_utc['VoltageDCDC12V'] = df1_utc['VoltageDCDC12V'].astype(object)
empty_df2_utc = pd.DataFrame(columns=['timestamp', 'VoltageDCDC12V'])
empty_df2_utc['timestamp'] = pd.to_datetime(empty_df2_utc['timestamp'], utc=True)
empty_df2_utc['VoltageDCDC12V'] = empty_df2_utc['VoltageDCDC12V'].astype(object)
result_table = pd.DataFrame(columns=['timestamp'])
result_table['timestamp'] = pd.to_datetime(result_table['timestamp'])
result_table2 = pd.DataFrame(columns=['timestamp'])
result_table2['timestamp'] = pd.to_datetime(result_table['timestamp'])
print('')
print('df1_utc')
print(df1_utc)
print(df1_utc.dtypes)
print('')
print('empty_df2_utc')
print(empty_df2_utc)
print(empty_df2_utc.dtypes)
print('')
print('result_table')
print(result_table)
print(result_table.dtypes)
print('')
print('result_table2')
print(result_table2)
print(result_table2.dtypes)
print('')
try:
result_table = result_table.merge(df1_utc, on="timestamp", how='outer')
except Exception as e:
print('Result_table to df1_utc merge error:')
print(e)
try:
result_table2 = result_table2.merge(empty_df2_utc, on="timestamp", how='outer')
except Exception as e:
print('Result_table2 to empty_df2_utc merge error:')
print(e)
Output:
df1_utc
timestamp VoltageDCDC12V
0 2022-06-20 12:05:00+00:00 13.6
timestamp datetime64[ns, UTC]
VoltageDCDC12V object
dtype: object
empty_df2_utc
Empty DataFrame
Columns: [timestamp, VoltageDCDC12V]
Index: []
timestamp datetime64[ns, UTC]
VoltageDCDC12V object
dtype: object
result_table
Empty DataFrame
Columns: [timestamp]
Index: []
timestamp datetime64[ns]
dtype: object
result_table2
Empty DataFrame
Columns: [timestamp]
Index: []
timestamp datetime64[ns]
dtype: object
Result_table2 to empty_df2_utc merge error:
You are trying to merge on datetime64[ns] and datetime64[ns, UTC] columns. If you wish to proceed you should use pd.concat
1条答案
按热度按时间uxhixvfz1#
您的
result_table
不支持时区,因此会导致合并错误。如果您排除了
UTC
时区中的所有数据,则可以将代码更改为以下代码,并且不会导致错误: