0

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'}
Ali Jafar
  • 137
  • 15

1 Answers1

1

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}}
Tranbi
  • 11,407
  • 6
  • 16
  • 33
  • Thank you for your very informative answer. Can you please tell me what should I have to do If I have one additional column and I want to make It key also. – Ali Jafar Nov 21 '21 at 17:53
  • It depends on where you want your new key. Feel free to open a new question and ask exactly what you need with column content and position of the key! – Tranbi Nov 21 '21 at 18:03
  • {'BZU': {'IT' : {2013: 1.0, 2014: 1.0, 2015: nan, 2016: 2.0}}} {'UCP': {'CS': {2013: 1.0, 2014: 1.0, 2015: nan, 2016: 2.0}}}. Can you please help me in Making this type of list as I am not able to open a new question. – Ali Jafar Nov 22 '21 at 11:37
  • It's not straight-forward to create a nested dict from a DataFrame but the question has already been asked: https://stackoverflow.com/questions/19798112/convert-pandas-dataframe-to-a-nested-dict – Tranbi Nov 22 '21 at 12:30