我想用python从excel文件中导入的值创建一个字典,excel列文件如下所示:
大学 | |
---|---|
IUB | 2013 |
IUB | 2013 |
IUB | 2013 |
IUB | 2014 |
IUB | 2015 |
BZU | 2013 |
BZU | 2013 |
BZU | 2014 |
UCP | 2016 |
UCP | 2016 |
UCP | 2013 |
UCP | 2014 |
您可以使用panda读取Excel文件。然后使用groupby
("大学,"年份"(和agg
计算每个大学/年份的计数。
使用pivot
格式化DataFrame,然后导出到字典:
import pandas as pd
df = pd.read_excel("your_excel_file.xlsx")
df['count'] = 0
df = df.groupby(['University', 'Year'], as_index=False)['count'].agg('count')
df = df.pivot(index="Year", columns="University", values="count")
output = df.to_dict()
print(output)
输出:
{'BZU': {2013: 2.0, 2014: 1.0, 2015: nan, 2016: nan}, 'IUB': {2013: 3.0, 2014: 1.0, 2015: 1.0, 2016: nan}, 'UCP': {2013: 1.0, 2014: 1.0, 2015: nan, 2016: 2.0}}
如有必要,您必须手动删除nan
值:
for uni, year in output.items():
for y, count in list(year.items()):
if pd.isna(count):
del year[y]
print(output)
输出:
{'BZU': {2013: 2.0, 2014: 1.0}, 'IUB': {2013: 3.0, 2014: 1.0, 2015: 1.0}, 'UCP': {2013: 1.0, 2014: 1.0, 2016: 2.0}}