43858

Message “Exception ignored” when dealing pandas.datetime type

<h3>Question</h3>

I have a xlsx file with a column containing Dates in the format: "01.01.1900 09:01:25". The file is password protected so I convert it to a dataframe by means of win32com.client library.

Here is the code:

import pandas as pd import win32com.client xlApp = win32com.client.Dispatch("Excel.Application") xlApp.DisplayAlerts = False xlwb = xlApp.Workbooks.Open(File, False, True, None, " ") #Open Workbook password " " xlws = xlwb.Sheets("Sheet 1") #Open Sheet 1 #Get table dimensions LastRow = xlws.Range("A1").CurrentRegion.Rows.Count LastColumn = xlws.Range("A1").CurrentRegion.Columns.Count header=list((xlws.Range(xlws.Cells(1, 1), xlws.Cells(1, LastColumn)).Value)[0]) content = list(xlws.Range(xlws.Cells(2, 1), xlws.Cells(LastRow, LastColumn)).Value) #Get the dataframe df=pd.DataFrame(data=content, columns=header) print (df)

I checked that once imported dtype as been automatically and correctly assigned to datetime64 for that column. The issue is that any time I try to do whatever with any value of that column (just print it or compare it) I get a meesage saying:

File "pandas\_libs\tslibs\timezones.pyx", line 227, in pandas._libs.tslibs.timezones.get_dst_info AttributeError: 'NoneType' object has no attribute 'total_seconds' Exception ignored in: 'pandas._libs.tslib._localize_tso' Traceback (most recent call last): File "pandas\_libs\tslibs\timezones.pyx", line 227, in pandas._libs.tslibs.timezones.get_dst_info AttributeError: 'NoneType' object has no attribute 'total_seconds' Traceback (most recent call last):

Nonetheless the code works perfectly, but the warning message is annoying me.

Is there anything I can do with the datatype to avoid that warning?


<h3>Answer1:</h3>

Opening the excel in this way, the content variable is a list of tuples.

Having a look on those tuples there is a TimeZoneInfo that localizes all the dates in a kind of time zone, in my case "GMT Standard Time".

So once converted to a dataframe, when doing df.dtypes the result is not only "datetime64" but "datetime64 (UTC+0:00) Dublin, Edimburg, ..."

This time zone setting only happens when opening the excel file through win32com.client. If you removed the password, you can open it with pandas.read_excel and discover that there is no timezone set for those datetimes and the mentioned warning does not appear.

Don't know exactly the reason it happens, but I have a solution for the original example. The warning dissapears setting a timezone recognized by tz database as "UTC" or simply None. Something like:

df["col_name"]=df["col_name"].dt.tz_convert(None)

来源:https://stackoverflow.com/questions/51827582/message-exception-ignored-when-dealing-pandas-datetime-type

Recommend

  • Sumifs in Excel-VBA
  • Android: Compressing images creates black borders on left and top margin
  • How to force Delphi to use D8.bat instead of dx.bat to compile Java 1.8 bytecode into DEX bytecode
  • Rails 3 Full_Calendar
  • Was default_marker removed from mapbox-gl.js
  • hadoop jar command points to local filesystem
  • Converting array with Different data type
  • How to set a dynamic form fields to dirty or touched with angular?
  • “RepeatForUnit” item missing in Calendar entry?
  • ng-sidebar (Angular 2) implementation issue
  • wxpython passing information, pointers?
  • Application backgrounding in Xamarin.Mac
  • Django non-rel createsuperuser fails due to non-ascii characters
  • Sharing multiple images on facebook post
  • Core Data and Reflexive Entity relationships (to-one or to-many)
  • Joining across databases with dbplyr
  • PHP users local time
  • Create One-To-One relationship between table and view in EF4?
  • Magento Layered Navigation block. Move to center
  • Hibernate Idempotent Update
  • Custom Data Generator for Keras LSTM with TimeSeriesGenerator
  • How exactly do you use json_decode to pass a javascript array to php?
  • Running iPhone crash Logs from testers on XCode
  • mssql script data insert or update
  • Overwrite text file programmatically
  • Update all WooCommerce product prices to 2 decimals in database
  • GTK3 + OpenGL for windows: multiple GtkGLArea (ie. OpenGL contexts)
  • JQuery Mobile Ajax Navigation in Single-Page Template
  • Java Collections.shuffle() weird behaviour [closed]
  • Annotate objects in a queryset with next and previous object ids
  • how to run ejabberd with Erlang on Heroku?
  • How to specify generic type when the type is only known at runtime?
  • How to decleare char *const argv[] in swift [duplicate]
  • How to encrypt Connectionstring written in web.config from codebehind?
  • Can someone explain this Java code (formatting the output using System.out.format) to me?
  • What does the “id” field in an Android “Google Play Music” broadcast intent correspond to?
  • XSLT Transformation to validate rules in XML document