时间戳

首页 > 解决方案 > how to fetch time series data from excel using openpyxl library?

python - how to fetch time series data from excel using openpyxl library?

问题描述

I am reading Excel file which update sensor data every 8 seconds and save it in auto.in

Excel sheet which i am reading contains:

I am using below code for fetching time series data:

import matplotlib.pyplot as plt
import time
import numpy as np
from openpyxl import load_workbook
import tkinter
import matplotlib.gridspec as gridspec

wb = load_workbook('PLX_DAQ.xlsm')
sheet_1 = wb.get_sheet_by_name('Simple Data')

plt.figure(figsize=(6, 4), facecolor='Grey')
G = gridspec.GridSpec(6, 2)

x = np.zeros(sheet_1.max_row)
y = np.zeros(len(x))
z = np.zeros(len(x))
a = np.zeros(len(x))
b = np.zeros(len(x))

for i in range(1, sheet_1.max_row): 

    x[i] = sheet_1.cell(row=i + 1, column=2).value
    y[i] = sheet_1.cell(row=i + 1, column=3).value
    z[i] = sheet_1.cell(row=i + 1, column=4).value
    a[i] = sheet_1.cell(row=i + 1, column=5).value
    b[i] = sheet_1.cell(row=i + 1, column=6).value
    plt.ion()

list1 = []
list2 = []
list3 = []
list4 = []
list5 = []

for i in range(0,len(x)):
    list1.append(x[i])
    list2.append(y[i])
    list3.append(z[i])
    list4.append(a[i])
    list5.append(b[i])

    time.sleep(8)
    plt.scatter(list1, list2, color='cyan', label='Humidity')
    plt.scatter(list1, list3, color='red', label='Temprature1')
    plt.scatter(list1, list4, color='yellow', label='Pressure')
    plt.scatter(list1, list5, color='green', label='Temprature2')
    plt.grid(True)
    plt.pause(0.5)

this is giving me following error,

runfile('C:/Users/tejas.a.patel/Desktop/Pythonpractice/Spyder/Read Excel_PLX-DAQ live plot-a.py', 
wdir='C:/Users/tejas.a.patel/Desktop/Pythonpractice/Spyder')
C:/Users/tejas.a.patel/Desktop/Pythonpractice/Spyder/Read Excel_PLX-DAQ live plot-a.py:19: 
DeprecationWarning: Call to deprecated function get_sheet_by_name (Use wb[sheetname]).
sheet_1 = wb.get_sheet_by_name('Simple Data')

Traceback (most recent call last):
File "<ipython-input-1-f101b726f4c2>", line 1, in <module> runfile('C:/Users/tejas.a.patel/Desktop/Pythonpractice/Spyder/Read Excel_PLX-DAQ live plot-a.py', 
wdir='C:/Users/tejas.a.patel/Desktop/Pythonpractice/Spyder')
File "C:\ProgramData\Anaconda3\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 827, in runfile
execfile(filename, namespace)
File "C:\ProgramData\Anaconda3\lib\site-packages\spyder_kernels\customize\spydercustomize.py", line 110, in execfile
exec(compile(f.read(), filename, 'exec'), namespace)
File "C:/Users/tejas.a.patel/Desktop/Pythonpractice/Spyder/Read Excel_PLX-DAQ live plot-a.py", line 35, in <module>
x[i] = sheet_1.cell(row=i + 1, column=2).value
TypeError: float() argument must be a string or a number, not 'datetime.time'

please help

标签： pythonexcelopenpyxl

解决方案

The issue is that you have initialized the variable as float:

x = np.zeros(sheet_1.max_row)
y = np.zeros(len(x))
z = np.zeros(len(x))
a = np.zeros(len(x))
b = np.zeros(len(x))

Check by:

x.dtype

You'll get 

dtype('float64')

Now you are trying to assign Python datetime.time object to the variable that is why it is throwing exception. I don't know why you are using x,y,z etc then appending in the list list1, list2,list3.. where you can directly append the values.

Anyway, you are using for some reason then one way out is to convert datetime.time object to seconds (or minutes or hours, whatever you like) then assign.

time_ = (sheet_1.cell(row=i + 1, column=2).value) # ex08:32:10
time_in_sec = time_.hour*60*60 + time_.minute*60 +time_.second

#print(type(time_))
#print(type(time_.isoformat()))
print(time_in_sec)
x[i] = time_in_sec # assign to variables

For more info check the link: https://docs.python.org/3.4/library/datetime.html?highlight=weekday#time-objects

推荐阅读