# 某大数据_学生在宿舍的进出时间记录

130人阅读 评论(0)

#coding:utf-8

import sys
import xlrd
import time
from Queue import Queue
from datetime import datetime
from operator import itemgetter
from xlrd import xldate_as_tuple

def dealExcle(filename):
myqueue = Queue()

X_thousand = []
all_content = []

# deal with excel
data = xlrd.open_workbook(filename)
table = data.sheets()[0]

rows = table.nrows
cols = table.ncols

for i in range(rows):

# 去除 excle 第一行
if i == 0:
continue

# 每 10000 个数据 取一个 列表
if i%10000 == 0:
X_thousand = sorted(X_thousand,key=itemgetter(0))
all_content.append(X_thousand)
X_thousand = []

# 读取 excel 每一行 因为时间直接读取, 有问题 会用浮点型显示
row_content = []
for j in range(cols):
ctype = table.cell(i, j).ctype
cell = table.cell_value(i, j)
if ctype == 3:
date = datetime(*xldate_as_tuple(cell, 0))
cell = date.strftime('%Y/%d/%m %H:%M:%S')
row_content.append(cell)

X_thousand.append(row_content)

# 将所有数据导入 队列
for x in all_content:
myqueue.put(x)

return myqueue

def subTime(d2,d1):
temp = time.strptime(d1, "%Y/%d/%m %H:%M:%S")
timestampd1 = time.mktime(temp)

temp = time.strptime(d2, "%Y/%d/%m %H:%M:%S")
timestampd2 = time.mktime(temp)

if timestampd2 > timestampd1:
return timestampd2 - timestampd1
else :
return timestampd1 - timestampd2

# ok
num = 0
for x in xrange(Startnum+1,Endnum+1):
if X_thounsand[x-1][2] == 1 and X_thounsand[x][2] == 0:
num = num + subTime(X_thounsand[x][1],X_thounsand[x-1][1])
return num

# 处理 X_thousands 的数据 并输出
def run(myqueue):
while myqueue.qsize() > 0:
X_thounsand = myqueue.get()

flag = 1
for x in xrange(1,len(X_thounsand)):
if X_thounsand[x-1][0] == X_thounsand[x][0]:
flag = flag + 1
else :
Endnum = x-1
Startnum = Endnum - flag + 1
# 如果 Startnum == Endnum 则说明只有一个数据
if Startnum != Endnum :
print "-"*20
print "学号 : %d | %5s 分钟" %  ( int(X_thounsand[Endnum][0]) ,str(num/60))
flag = 1

if __name__ == '__main__':
if len(sys.argv) != 2:
print "Usage : python run.py xxx.xlsx"
exit()

filename = sys.argv[1]
myqueue = dealExcle(filename)

for i in range(30):
t.start()
for t in threads:
t.join()

# dt = "2016-05-05 20:28:54"
# timeArray = time.strptime(dt, "%Y-%m-%d %H:%M:%S")
# timestamp = time.mktime(timeArray)

# from operator import itemgetter
# sorted(a,key=itemgetter(0))

# >>> from datetime import datetime
# >>> d1 = datetime(2017,11,14,11,30,20)
# >>> d2 = datetime(2017,11,14,17,20,11)
# >>> (d2-d1).seconds
# 20991

# mysql> create table student(
#     -> id char(10),
#     -> time char(25),
#     -> turn char(5));
0
0

个人资料
• 访问：44221次
• 积分：1137
• 等级：
• 排名：千里之外
• 原创：49篇
• 转载：108篇
• 译文：0篇
• 评论：3条
文章分类
最新评论