How to write a Dictionary to Excel in Python

Question

I have the following dictionary in python that represents a From - To Distance Matrix.

graph = {'A':{'A':0,'B':6,'C':INF,'D':6,'E':7},

         'B':{'A':INF,'B':0,'C':5,'D':INF,'E':INF},

         'C':{'A':INF,'B':INF,'C':0,'D':9,'E':3},

         'D':{'A':INF,'B':INF,'C':9,'D':0,'E':7},

         'E':{'A':INF,'B':4,'C':INF,'D':INF,'E':0}

         }

Is it possible to output this matrix into excel or to a csv file so that it has the following format? I have looked into using csv.writer and csv.DictWriter but can not produce the desired output.

Answer 1

You may create a pandas dataframe from that dict, then save to CSV or Excel:

import pandas as pd
df = pd.DataFrame(graph).T  # transpose to look just like the sheet above
df.to_csv('file.csv')
df.to_excel('file.xls')

Answer 2

Probably not the most minimal result, but pandas would solve this marvellously (and if you're doing data analysis of any kind, I can highly recommend pandas!).

Your data is already in a perfectformat for bringing into a Dataframe

INF = 'INF'
graph = {'A':{'A':0,'B':6,'C':INF,'D':6,'E':7},

         'B':{'A':INF,'B':0,'C':5,'D':INF,'E':INF},

         'C':{'A':INF,'B':INF,'C':0,'D':9,'E':3},

         'D':{'A':INF,'B':INF,'C':9,'D':0,'E':7},

         'E':{'A':INF,'B':4,'C':INF,'D':INF,'E':0}
         }
import pandas as pd
pd.DataFrame(graph).to_csv('OUTPUT.csv')

but the output you want is this Transposed, so:

pd.DataFrame(graph).T.to_csv('OUTPUT.csv')

where T returns the transpose of the Dataframe.