準備測試數據:
測試數據
打開PyCharm輸入以下代碼
程序代碼
運行效果如下:
運行效果展示
附上實現代碼:
#!/usr/bin/env python
import pandas as pd
OPENPATH = 'test.xls'
SAVEPATH = 'test1.xls'
def total_count(path=OPENPATH, sheetname='testsheet'):
df = pd.read_excel(path, sheet_name=sheetname, names=['值', '計數'])
# 獲取統計項目
item_name = set(df['值'])
# 創建字典統計
total_dict = dict(zip([i for i in item_name], [
0 for _ in range(len(item_name))]))
# 遍歷”值“列,逐個統計數量
for index, item in enumerate(df['值']):
# 如果在set中
if item in item_name:
# 加入計數統計
total_dict[item] += df['計數'][index]
# 返回
return total_dict
def datato_excel(path=SAVEPATH, sheet_name='total', data_dict={}):
report_df = pd.DataFrame.from_dict(data_dict,orient='index')
xl_writer = pd.ExcelWriter(path)
report_df.to_excel(xl_writer, sheet_name)
try:
xl_writer.save()
print('Save completed')
except:
print('Error in saving file')
if __name__ == "__main__":
datato_excel(data_dict=total_count())
不知道這是不是您想要的結果,如果有幫助,請采納壹下,謝謝!