Openpyxl& Python:键列,值列 - 如何加总值并将总计分配给相应的键 [英] Openpyxl & Python : column of keys, column of values - how to add up the values and assign totals to corresponding keys
问题描述
Col1 Col2
1234 12.5
1234 8.2
1234 9.8
2334 10.1
2334 7.7
4567 9.8
5678 9.9
5678 8.4
我需要在Col2中使用OpenPyxl& Python ie。
1234 30.5
2334 17.8
4567 9.8
5678 18.3
经过几个假启动,我有这样的:
#import modules
import openpyxl
from openpyxl.utils import coordinate_from_string,column_index_from_string
from openpyxl.utils.cell import _get_column_letter
import sys
from datetime import date
from datetime import time
import datetime
import calendar
from openpyxl.styles import颜色,PatternFill,Font,Border
from shutil import copyfile
#set变量
dest_filename ='P:\\Charging\\\可扩展资源\\ChargeableActivity\\January2017ChargeableActivity.xlsx'
total =
#create objects
wb = openpyxl.load_workbook(filename = dest_filename,data_only = True)
ws1 = wb.get_sheet_by_name('ChargeableActivity')
for i in range(1, ws1.max_row):
#convert ws1.cell(row = i,column = 12).value to integer(= Excel convert to number)
if isinstance(ws1.cell(row = i,column = 12).value,long):
RFCNumber = ws1.cell(row = i,column = 12).value
for ws1.iter_cols(min_col = 12,max_col = 12,min_row = 1):
如果ws1.cell(row = i,column = 12).value == RFCNumber:
total = total + ws1.cell(row = i,column = 14).value
print(RFCNumber,'Total =',total)
但输出是累积的, t删除重复的RFC号码:
Col1 Col2
1234 12.5
1234 20.7
1234 30.5
2334 40.6
4567 48.3
5678 58.1
etc
我不是一个编码器,我正在寻找一种方法来节省大量的时间编辑一个大的电子表格。欢迎任何建议。谢谢。
请尝试以下代码:
#定义关键字的一个dict {}:$ pair
ref_total = {}
#获取所有行的数据
对于ws.rows中的行:
#行元组A,B从行元组
cell_A =行[:1] [0]
cell_B =行[1:2] [0]
reference = cell_A.value
如果ref_total.keys()中的引用:
ref_total [reference] + = cell_B.value
else:
ref_total [reference] = cell_B.value $
print('%s%s'%(key,ref_total [key]))
使用Python测试:3.4.2 - openpyxl:2.4.1 - LibreOffice:4.3.3.2 / strong>
Apologies for the false start. I have now read the FAQs and hope my question meets the standards:). I have the following in a spreadsheet :
Col1 Col2
1234 12.5
1234 8.2
1234 9.8
2334 10.1
2334 7.7
4567 9.8
5678 9.9
5678 8.4
i need to total up the figures in Col2 for each reference number in Col1 using OpenPyxl & Python ie.
1234 30.5
2334 17.8
4567 9.8
5678 18.3
After a few false starts i have this :
#import modules
import openpyxl
from openpyxl.utils import coordinate_from_string, column_index_from_string
from openpyxl.utils.cell import _get_column_letter
import sys
from datetime import date
from datetime import time
import datetime
import calendar
from openpyxl.styles import Color, PatternFill, Font, Border
from shutil import copyfile
#set variables
dest_filename = 'P:\\Charging\\Chargeable Resources\\ChargeableActivity\\January2017ChargeableActivity.xlsx'
total = 0
#create objects
wb = openpyxl.load_workbook(filename = dest_filename, data_only=True)
ws1 = wb.get_sheet_by_name('ChargeableActivity')
for i in range(1, ws1.max_row):
#convert ws1.cell(row=i, column=12).value to integer (=Excel convert to number)
if isinstance(ws1.cell(row=i, column=12).value,long):
RFCNumber = ws1.cell(row=i, column=12).value
for col in ws1.iter_cols(min_col=12, max_col = 12, min_row=1):
if ws1.cell(row=i, column=12).value == RFCNumber:
total = total + ws1.cell(row=i, column=14).value
print(RFCNumber,'Total=',total)
But the output is cumulative and doesn't delete duplicate RFC numbers :
Col1 Col2
1234 12.5
1234 20.7
1234 30.5
2334 40.6
4567 48.3
5678 58.1
etc I'm not a coder and am looking for a way to save a lot of time editing a big spreadsheet. Any suggestions welcomed. Thank you.
Try this code:
# Define a dict{} for key:value pairs
ref_total = {}
# Get Data from all rows
for row in ws.rows:
# Slice cells A,B from row tuple
cell_A = row[:1][0]
cell_B = row[1:2][0]
reference = cell_A.value
if reference in ref_total.keys():
ref_total[reference] += cell_B.value
else:
ref_total[reference] = cell_B.value
for key in sorted(ref_total.keys()):
print('%s %s' % (key, ref_total[key]))
Tested with Python:3.4.2 - openpyxl:2.4.1 - LibreOffice: 4.3.3.2
这篇关于Openpyxl& Python:键列,值列 - 如何加总值并将总计分配给相应的键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!