# coding: utf8
import re
import sys
import xlwt
from xlwt import Workbook,easyxf
from xlrd import open_workbook,XL_CELL_TEXT, cellname
from tempfile import TemporaryFile
"""Шаблон цеха"""
book_shablon_ceha = open_workbook('ceha_shablon.xls')
ceha_shablon = book_shablon_ceha.sheet_by_index(0)
"""Шаблон подряд"""
book_shablon_podryad = open_workbook('podryad_shablon.xls')
podryad_shablon = book_shablon_podryad.sheet_by_index(0)
"""Файл цехов на запись"""
book_ceha = xlwt.Workbook('utf8')
ceha = book_ceha.add_sheet('Отчет по цехам')
"""Файл подрядных на запись"""
book_podryad = xlwt.Workbook('utf8')
podryad = book_podryad.add_sheet('Отчет по подрядным')
"""Фонты"""
font_red = xlwt.easyxf('font: height 240,name Times New Roman,colour_index black, bold off,italic off; align: wrap on, vert top, horiz left;pattern: pattern solid, fore_colour white;border: right thin,left thin,top thin,bottom thin;')
font_blue = xlwt.easyxf('font: height 240,name Times New Roman,colour_index black, bold off,italic off; align: wrap on, vert top, horiz left;pattern: pattern solid, fore_colour white;border: right thin,left thin,top thin,bottom thin;')
font_green = xlwt.easyxf('font: height 240,name Times New Roman,colour_index black, bold off,italic off; align: wrap on, vert top, horiz left;pattern: pattern solid, fore_colour white;border: right thin,left thin,top thin,bottom thin;')
font_400 = xlwt.easyxf('font: height 300,name Times New Roman,colour_index black, bold off,italic off; align: wrap on, vert top, horiz left;pattern: pattern solid, fore_colour white;border: right thin,left thin,top thin,bottom thin;')
font_240_i = xlwt.easyxf('font: height 240,name Times New Roman,colour_index black, bold off,italic off; align: wrap on, vert top, horiz left;pattern: pattern solid, fore_colour white;border: right thin,left thin,top thin,bottom thin;')
bez_border = xlwt.easyxf('font: height 240,name Times New Roman,colour_index black, bold off,italic off; align: wrap on, vert top, horiz left;pattern: pattern solid, fore_colour white;')
left_border = xlwt.easyxf('font: height 240,name Times New Roman,colour_index black, bold off,italic off; align: wrap on, vert top, horiz left;pattern: pattern solid, fore_colour white;border:left thin;')
right_border = xlwt.easyxf('font: height 240,name Times New Roman,colour_index black, bold off,italic off; align: wrap on, vert top, horiz left;pattern: pattern solid, fore_colour white;border: right thin;')
verh_border = xlwt.easyxf('font: height 240,name Times New Roman,colour_index black, bold off,italic off; align: wrap on, vert top, horiz left;pattern: pattern solid, fore_colour white;border: top thin;')
niz_border = xlwt.easyxf('font: height 240,name Times New Roman,colour_index black, bold off,italic off; align: wrap on, vert top, horiz left;pattern: pattern solid, fore_colour white;border: bottom thin;')
niz_left = xlwt.easyxf('font: height 240,name Times New Roman,colour_index black, bold off,italic off; align: wrap on, vert top, horiz left;pattern: pattern solid, fore_colour white;border: bottom thin,left thin;')
niz_right = xlwt.easyxf('font: height 240,name Times New Roman,colour_index black, bold off,italic off; align: wrap on, vert top, horiz left;pattern: pattern solid, fore_colour white;border: bottom thin,right thin;')
verh_left = xlwt.easyxf('font: height 240,name Times New Roman,colour_index black, bold off,italic off; align: wrap on, vert top, horiz left;pattern: pattern solid, fore_colour white;border: top thin,left thin;')
verh_right = xlwt.easyxf('font: height 240,name Times New Roman,colour_index black, bold off,italic off; align: wrap on, vert top, horiz left;pattern: pattern solid, fore_colour white;border: top thin,right thin;')
l = easyxf('border: right thin,left thin,top thin,bottom thin')
""" Создаем пустой словарь"""
slovar_iz_shablona_ceha = {}#словарь лицевых счетов и названий цехов
slovar_iz_shablona_podryad = {}#словарь лицевых счетов и названий подрядных организаций
spisok_obshiy_otsortirovaniy = []
slovar_otchet_ceha = {}
""""Прочитали столбик 1 (с 1 до 100) ячейки, загнали в список и отсортировали список """
nomer_pervoy_yacheyki_shablona_ceha = 0 #тут уже не менять
nomer_posledney_yacheyki_shablona_ceha = 100#это последняя ячейка шаблона в цехе с данными
for iterator_schetov_ceha in range(nomer_pervoy_yacheyki_shablona_ceha,nomer_posledney_yacheyki_shablona_ceha):
    lic_shet_shablon_v_slovar = int(ceha_shablon.cell(iterator_schetov_ceha,0).value)
    naz_org_shablon_v_slovar =  (ceha_shablon.cell(iterator_schetov_ceha,1).value)
    slovar_iz_shablona_ceha[lic_shet_shablon_v_slovar] = naz_org_shablon_v_slovar
""""Прочитали столбик 1 (с 0 до 31) ячейки, загнали в список и отсортировали список """
nomer_pervoy_yacheyki_shablona_podryad = 0 #тут уже не менять
nomer_posledney_yacheyki_shablona_podryad = 31#это последняя ячейка шаблона в цехе с данными
for iterator_schetov_podryad in range(nomer_pervoy_yacheyki_shablona_podryad,nomer_posledney_yacheyki_shablona_podryad):
    lic_shet_shablon_podryad_v_slovar = int(podryad_shablon.cell(iterator_schetov_podryad,0).value)
    naz_org_shablon_podryad_v_slovar =  (podryad_shablon.cell(iterator_schetov_podryad,1).value)
    slovar_iz_shablona_podryad[lic_shet_shablon_podryad_v_slovar] = naz_org_shablon_v_slovar
for line in open(r'import.txt'):
    shablon = line.split(',')
    #print shablon
    data = shablon[2][0:10]
    vremya = shablon[2][11:16]
    summa = float(shablon[7])
    nomer_telefona = int(shablon[1])
    lic_schet = int(shablon[8])
    kod = shablon[3].decode('cp866')
    nomer = int(shablon[4])
    dlina_razgovora = int(shablon[5])
    napravlenie = shablon[10].decode('cp866')
    naz_org_ceha = slovar_iz_shablona_ceha[lic_schet]
    if data and summa and nomer_telefona and lic_schet and nomer and kod and napravlenie and vremya and dlina_razgovora:
        spisok_obshiy_otsortirovaniy.append([lic_schet,nomer_telefona,naz_org_ceha,data, vremya,kod,nomer, napravlenie, dlina_razgovora,summa])
spisok_obshiy_otsortirovaniy.sort(key = lambda x: (x[0], x[1],x[3],x[4]))
print spisok_obshiy_otsortirovaniy
"""ceha.write(stroka, 0,spisok[0],font_400)
ceha.write(stroka, 1, spisok[1], font_240_i)
book_ceha.save(ceha_nazvanie_faila)
book_ceha.save(TemporaryFile())"""

import codecs
from itertools import groupby
i_cost = 7
i_tel = 1
def by_tel(v):
    return v[i_tel]
with codecs.open("aa.txt","r",encoding="utf-8") as f:
    data=[i.split(",") for i in f]
print [(int(i[0]),sum([float(j[i_cost]) for j in i[1]])) for i in groupby(sorted(data,key=by_tel),by_tel)]
>>> 
[(7005, 6.4), (7025, 589.5), (7050, 16.0), (7057, 52.5), (7059, 68.0), (7063, 48.0), (7076, 24.0), (7104, 4.0), (7106, 28.0), (711, 8.0)]

import pandas as pd
rd = pd.read_table("aa.txt",encoding="utf-8",sep=",",header=None,usecols=[1,7])
rd.columns=["t","p"]
print rd.groupby("t").sum()
>>> 
          p
t          
711     8.0
7005    6.4
7025  589.5
7050   16.0
7057   52.5
7059   68.0
7063   48.0
7076   24.0
7104    4.0
7106   28.0