python - Pandas 从 Excel 创建合并报告

Question

我有一个包含以下详细信息的 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

Answer 1

试试这个，

解决这个问题的一种方法是使用 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