我的脚本的目标是: 1.从Excel文件(> 100,000k行)中读取时间序列数据以及标题(标签,单位) 2.将Excel数字日期转换为最佳pandas DataFrame datetime对象 3.能够使用时间戳引用行和系列标签引用列
到目前为止,我使用xlrd将Excel数据读入列表中。使用每个列表创建pandas Series,并使用时间列表作为索引。将Series与Series标题组合成Python字典。将字典传递给pandas DataFrame。尽管我已经尽力了,但df.index似乎设置为列标题,而我不确定何时将日期转换为datetime对象。
我刚刚开始使用Python 3天,所以任何建议都会很好!这是我的代码:
#Open excel workbook and first sheet
wb = xlrd.open_workbook("C:\GreenCSV\Calgary\CWater.xlsx")
sh = wb.sheet_by_index(0)
#Read rows containing labels and units
Labels = sh.row_values(1, start_colx=0, end_colx=None)
Units = sh.row_values(2, start_colx=0, end_colx=None)
#Initialize list to hold data
Data = [None] * (sh.ncols)
#read column by column and store in list
for colnum in range(sh.ncols):
Data[colnum] = sh.col_values(colnum, start_rowx=5, end_rowx=None)
#Delete unecessary rows and columns
del Labels[3],Labels[0:2], Units[3], Units[0:2], Data[3], Data[0:2]
#Create Pandas Series
s = [None] * (sh.ncols - 4)
for colnum in range(sh.ncols - 4):
s[colnum] = Series(Data[colnum+1], index=Data[0])
#Create Dictionary of Series
dictionary = {}
for i in range(sh.ncols-4):
dictionary[i]= {Labels[i] : s[i]}
#Pass Dictionary to Pandas DataFrame
df = pd.DataFrame.from_dict(dictionary)