Python Forum
Matplotlib: How do I convert Dates from Excel to use in Matplotlib
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Matplotlib: How do I convert Dates from Excel to use in Matplotlib
#1
A typical date in Excel is d/m/yyyy (eg '6/3/1990').

I want to import a timeseries from excel to plot in Matplotlib.

I note for Python datetime module the date have to be in the format yyyy-mm-dd (eg 1990-03-06) before user can create a datetime object.

So how should I convert the format in Excel to the format required by Python?

Thank you
Reply
#2
(Mar-11-2021, 03:28 AM)JaneTan Wrote: A typical date in Excel is d/m/yyyy (eg '6/3/1990').
This is incorrect. You must distinguish between how dates are stored internally in excel and how they are displayed in cell.

Dates in excel are stored as float numbers representing days from 1 Jan 1900 (with know "bug"
that 1900 is considered leap year, for backward compatibility with Lotus1-2-3. this is bug will be taken care of by the library you use to read the excel file anyway) or from 01 Jan 1904. You can set which system from your excel options

The decimal part represents the time from the start of the day.
Then you can represent the date in cell anyway you want - long, short date, custom number format...

https://docs.microsoft.com/en-us/office/...ate-system
https://docs.microsoft.com/en-us/office/...-leap-year

As far as I know, and always used, the default one is one starting from 01 Jan 1900. The use of 2 systems can cause problems when copy paste/link dates between books from different workbooks, so don't tinker with the options.

Alternatively, I read dates in excel can be stored in XLSX files as an ISO 8601 formatted string, but never looked deeper into it. Probably again some option/

(Mar-11-2021, 03:28 AM)JaneTan Wrote: I note for Python datetime module the date have to be in the format yyyy-mm-dd (eg 1990-03-06) before user can create a datetime object.

First of all, if data in excel are indeed datetime, they will/can be converted to python datetime.datetime objects when you read the excel file.
Second - you can create datetime.datetime object from any [proeprly formatted] string by providing the correct format string or by other sources - e.g. unix timestamp, etc.

(Mar-11-2021, 03:28 AM)JaneTan Wrote: So how should I convert the format in Excel to the format required by Python?

The best would be to convert the dates (assuming they are dates in excel) when reading the file. So what library do you use to work with Excel? Show your code in python tags.
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Forcing matplotlib to NOT use scientific notation when graphing sawtooth500 4 227 Mar-25-2024, 03:00 AM
Last Post: sawtooth500
  Embed Matplotlib Graph to Tkinter? dimidgen 0 182 Mar-04-2024, 07:40 PM
Last Post: dimidgen
  Matplotlib Version Aboutben 2 640 Aug-21-2023, 12:05 PM
Last Post: Aboutben
  Fixed colum width for rowLabels i Matplotlib pandabay 0 400 Jun-10-2023, 03:40 PM
Last Post: pandabay
  matplotlib x-axis text move bottom upward jacklee26 3 929 May-31-2023, 04:28 AM
Last Post: jacklee26
  .py pandas matplotlib .xlsx files QubeStory 1 757 Mar-23-2023, 09:38 AM
Last Post: buran
  How to read csv file update matplotlib column chart regularly SamLiu 2 1,016 Jan-21-2023, 11:33 PM
Last Post: SamLiu
  PIL ImageTk issue with MATPLOTLIB garynewport 0 1,671 Jan-17-2023, 11:32 AM
Last Post: garynewport
  Matplotlib FuncAnimation NewPi 3 1,224 Dec-30-2022, 11:14 PM
Last Post: NewPi
  How do you create a scatterplot of dataframe using matplotlib? asdad 2 828 Dec-07-2022, 04:53 PM
Last Post: Larz60+

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020