I want to create a dictionary from the values imported from the excel file using python, the excel columns file looks like this:

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'}

CodePudding user response：

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}}