时间戳

首页 > 解决方案 > Pandas 从 excel 创建合并报告

python - Pandas 从 excel 创建合并报告

问题描述

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

标签： pythonpandas

解决方案

尝试这个，

解决此使用 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 中使用和传递 dict 列表。

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

推荐阅读