I want to create a dictionary from the values imported from the excel file using python, the excel columns file looks like this:
| University | Year |
|---|---|
| IUB | 2013 |
| IUB | 2013 |
| IUB | 2013 |
| IUB | 2014 |
| IUB | 2015 |
| BZU | 2013 |
| BZU | 2013 |
| BZU | 2014 |
| UCP | 2016 |
| UCP | 2016 |
| UCP | 2013 |
| UCP | 2014 |
The output should look like this :
'IUB': {'2013': '3', '2014': '1', '2015': '1'},
'BZU': {'2013': '2', '2014': '1'},
'UCP': {'2013': '1', '2014': '1', '2016': '2'}
You can use pandas to read your Excel file. Then use groupby ('University, 'Year') and agg to calculate the count for each University/Year.
Format your DataFrame with pivot then export to dictionary:
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)
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}}
You'll have to remove nan values manually if necessary:
for uni, year in output.items():
for y, count in list(year.items()):
if pd.isna(count):
del year[y]
print(output)
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}}
