关闭

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

61人阅读 评论(0) 收藏 举报
分类:
#coding:utf-8

import sys
import xlrd
import time
import threading
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
def addTime(X_thounsand,Startnum,Endnum):
    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 :
                    num = addTime(X_thounsand,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)
    threads = []

    for i in range(30):
        t = threading.Thread(target=run,args=(myqueue,))
        t.start()
        threads.append(t)
    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

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:27304次
    • 积分:800
    • 等级:
    • 排名:千里之外
    • 原创:44篇
    • 转载:57篇
    • 译文:0篇
    • 评论:3条
    文章分类
    最新评论