Creating a dictionary based on rows for a transport problem

Question

So I have the following data:

And I have read it into python using read_excel() and done some transformations giving me the following pandas dataframe:

Now I want to make a dictionary where all pairs in each row returns the "Pr Unit Cost" of the row as the value like so:

{('Macon', 'Tacoma'): 35.5,
    ('Macon', 'San Diego'): 35.5,
    ('Macon', 'Dallas'): 35.5,
    ('Macon', 'Denver'): 35.5,
    ('Macon', 'St. Louis'): 35.5,
    ('Macon', 'Tampa'): 35.5,
    ('Macon', 'Baltimore'): 35.5,
    ('Louisville', 'Tacoma'): 37.5,
    ('Louisville', 'San Diego'): 37.5,
    ('Louisville', 'Dallas'): 37.5,
    ('Louisville', 'Denver'): 37.5,
    ('Louisville', 'St. Louis'): 37.5,
    ('Louisville', 'Tampa'): 37.5,
    ('Louisville', 'Baltimore'): 37.5,
    ('Detroit', 'Tacoma'): 39,
    ('Detroit', 'San Diego'): 39,
    ('Detroit', 'Dallas'): 39,
    ('Detroit', 'Denver'): 39,
    ('Detroit', 'St. Louis'): 39,
    ('Detroit', 'Tampa'): 39,
    ('Detroit', 'Baltimore'): 39,
    ('Phoenix', 'Tacoma'): 36.25,
    ('Phoenix', 'San Diego'): 36.25,
    ('Phoenix', 'Dallas'): 36.25,
    ('Phoenix', 'Denver'): 36.25,
    ('Phoenix', 'St. Louis'): 36.25,
    ('Phoenix', 'Tampa'): 36.25,
    ('Phoenix', 'Baltimore'): 36.25
    }

How can this be achieved in an elegant way?

Answer 1

Use DataFrame.stack for MultiIndex Series, assign repeated values of Pr Unit Cos and then Series.to_dict:

#remove not necessary columns in final dict
df1 = df.drop(['Capacity','Pr Unit Cos'], axis=1)

s = df1.stack(dropna=False)
d = (pd.Series(np.repeat(df['Pr Unit Cos'].to_numpy(), 
              len(df1.columns)), index=s.index)
       .to_dict())