0

I am trying to plot data on python using matplotlib but for some reason it doesn't allow me to plot the date and time data on the X axis. It complains that it can't convert a string to a float. The excel data looks as follows:

06/11/17 04:13:02 PM    2.1
06/11/17 04:14:32 PM    3.9
06/11/17 04:14:54 PM    2
06/11/17 04:15:08 PM    3.2
06/11/17 04:15:25 PM    1.7

What I want is a graph that shows the date & time (not current time) on the X axis and the other values on the Y axis. I have the excel part working fine but the plotting graph is where I am having issues.

squid22
  • 33
  • 3
  • How are you getting the dataset on python? Are you using pandas? – anon Jun 21 '17 at 19:23
  • @anon Using openpyxl but thats not issue. the same happens if i create two short list as follows: v = [2,3.9,2,3.2,1.7] t = ["06/11/17 04:13:02 PM", blah bla] – squid22 Jun 21 '17 at 19:29
  • You would need to convert that string into a datetime object – anon Jun 21 '17 at 19:33
  • @anon Do you know how? – squid22 Jun 21 '17 at 19:37
  • Show what you've tried. – eyllanesc Jun 21 '17 at 19:38
  • SO is not a programming service. – eyllanesc Jun 21 '17 at 19:39
  • You can save yourself a lot of time using google. In this case use the search term **matplotlib dates on x axis**. One nugget: https://stackoverflow.com/a/9627970/131187. – Bill Bell Jun 21 '17 at 19:46
  • @BillBell Thank you sir :) Eyllanesc is ok if you dont know. I am not a programmer, I am learning python so I am in no rush to get this done. If I figure it out I will post the answer so it might help someone in the future. – squid22 Jun 21 '17 at 20:03
  • eyllanesc is probably fine but a lot of people on here work themselves into a frenzy when they see questions like this. It might be better if you tried looking up answers but then there would still be 10 or 20 millions who don't. :) – Bill Bell Jun 21 '17 at 20:13
  • @BillBell ... good to know :) I will work with what you sent me and I will post an answer if I figure it out later after work. Thanks again – squid22 Jun 21 '17 at 20:21
  • Here we are all programmers, from junior to experts. Here we help you if you show interest in solving it and one way to do it is to show what you have tried. I think it's great that you've solved it. For a next question you should read: https://stackoverflow.com/help/how-to-ask – eyllanesc Jun 21 '17 at 23:53

1 Answers1

0

I figure it out. it turns out that openpyxl already converts the the date and time from excel to datetime() values. Below is my code and it works flawlessly.

import openpyxl
import sys
import matplotlib.pyplot as plt

v = []
d = []

file = sys.argv[1]

wb = openpyxl.load_workbook(file)
sheet = wb.get_sheet_by_name('Sheet1')

for row in range(2,sheet.max_row):
    for stuff in "B":
        valuesData = "{}{}".format("B",row)
        v.append(sheet[valuesData].value)

for row in range(2,sheet.max_row):
    for stuff in "A":
        timeData = "{}{}".format("A",row)
        d.append(sheet[timeData].value)

plt.xlabel("Time")
plt.ylabel("Values")
plt.plot(d,v)
plt.show()
#

Assuming you have an excel sheet with data as follows:

A                         B
1 06/11/17 04:13:02 PM    2.1
2 06/11/17 04:14:32 PM    3.9
3 06/11/17 04:14:54 PM    2
4 06/11/17 04:15:08 PM    3.2
5 06/11/17 04:15:25 PM    1.7

I know its not perfect but works for me. I am sure it can be done much more efficiently.

squid22
  • 33
  • 3