Skip to main content
 首页 » 编程设计

python之Pandas 从 Excel 创建合并报告

2024年12月31日9zhwl

我有一个包含以下详细信息的 excel 文件。我正在尝试使用 panda 在 excel 中仅获取前 5 种语言及其总和

files   language    blank   comment code 
61         Java     1031     533    3959 
10         Maven    73        66    1213 
12         JSON      0         0    800 
32         XML       16       74    421 
7          HTML      14       16    161 
1      Markdown     23        0      39 
1           CSS      0        0      1 

下面是我的代码

import pandas as pd 
from openpyxl import load_workbook 
 
df = pd.read_csv("myfile_cloc.csv", nrows=20) 
#df =  df.iloc[1:] 
top_five = df.head(5) 
 
print(top_five) 
print(top_five['language']) 
print(top_five['code'].sum()) 
 
d = {'Languages (CLOC) (Top 5 Only)': "", 'LOC (CLOC)Only Code': 0} 
newdf = pd.DataFrame(data=d) 
 
newdf['Languages (CLOC) (Top 5 Only)'] = str(top_five['language']) 
newdf['LOC (CLOC)Only Code'] = top_five['code'].sum() 
 
#Load excel to append the consolidated info 
writer = newdf.ExcelWriter("myfile_cloc.xlsx", engine='openpyxl') 
book = load_workbook('myfile_cloc.xlsx') 
 
writer.book = book 
newdf.to_excel(writer, sheet_name='top_five', index=False) 
writer.save() 

需要这些方面的建议

newdf['Languages (CLOC) (Top 5 Only)'] = str(top_five['language']) 
newdf['LOC (CLOC)Only Code'] = top_five['code'].sum() 

这样预期输出就可以

Languages (CLOC) (Top 5 Only)  LOC (CLOC)Only Code 
Java,Maven,JSON,XML,HTML          6554 

目前出现错误

raise ValueError('If using all scalar values, you must pass' 
ValueError: If using all scalar values, you must pass an index 

请您参考如下方法:

试试这个,

解决这个问题的一种方法是使用 index 属性

a=df.head() 
df=pd.DataFrame({"Languages (CLOC) (Top 5 Only)": ','.join(a['language'].unique()),"LOC (CLOC)Only Code":a['code'].sum()},index=range(1)) 

解决这个问题的另一种方法, 使用 from_records 并在 Dataframe 中传递字典列表。

df=pd.DataFrame.from_records([{"Languages (CLOC) (Top 5 Only)": ','.join(a['language'].unique()),"LOC (CLOC)Only Code":a['code'].sum()}]) 

输出:

    Languages (CLOC) (Top 5 Only)    LOC (CLOC)Only Code 
0        Java,Maven,JSON,XML,HTML                   6554