Dictionary making for a transportation model from a Dataframe

user_9168000 0
Viewed 54

I have this Dataframe for a transportation problem.

Unnamed: 0 Unnamed: 1    c1   c2   c3   c4   c5  capacity
0        NaN         p1   4    5    6    8   10     500.0
1        NaN         p2   6    4    3    5    8     500.0
2        NaN         p3   9    7    4    2    4     500.0
3     demand        NaN  80  270  250  160  180       NaN

I have changed the column name like this,

df.columns = ['Demand', 'Plant', 'c1', 'c2', 'c3', 'c4', 'c5', 'capacity']

I want to make a dictionary like this,

 d = {c1:80, c2:270, c3:250, c4:160, c5:180}  # customer demand
 M = {p1:500, p2:500, p3:500}               # factory capacity
 I = [c1,c2,c3,c4,c5]                         # Customers
 J = [p1,p2,p3]                             # Factories
 cost = {(p1,c1):4,    (p1,c2):5,    (p1,c3):6,
 (p1,c4):8,    (p1,c5):10, ......
  }

For 1st case, I have used the following code,

  M = df.set_index('Plant')['capacity'].to_dict()

It is giving me,

  {'p1': 500.0, 'p2': 500.0, 'p3': 500.0, nan: nan}

I don't want any NaN value. Please help to find this total dictionary (d, M and cost) in a generic way without a NaN.

pandas dataframe numpy dictionary python
1 Answers
df1 = df.set_index(["Unnamed: 0", "Unnamed: 1"])
plants = df1.loc[np.NaN]  # remove demand from dataframe

d = dict(df1.loc["demand"].T.squeeze().dropna().iteritems())
M = dict(plants["capacity"].iteritems())
I = list(plants.drop(columns="capacity").columns)
J = list(plants.index)
cost = dict(plants.drop(columns="capacity").stack().iteritems())

>>> d
{'c1': 80.0, 'c2': 270.0, 'c3': 250.0, 'c4': 160.0, 'c5': 180.0}

>>> M
{'p1': 500.0, 'p2': 500.0, 'p3': 500.0}

>>> I
['c1', 'c2', 'c3', 'c4', 'c5']

>>> J
['p1', 'p2', 'p3']

>>> cost
{('p1', 'c1'): 4,
 ('p1', 'c2'): 5,
 ('p1', 'c3'): 6,
 ('p1', 'c4'): 8,
 ('p1', 'c5'): 10,
 ('p2', 'c1'): 6,
 ('p2', 'c2'): 4,
 ('p2', 'c3'): 3,
 ('p2', 'c4'): 5,
 ('p2', 'c5'): 8,
 ('p3', 'c1'): 9,
 ('p3', 'c2'): 7,
 ('p3', 'c3'): 4,
 ('p3', 'c4'): 2,
 ('p3', 'c5'): 4}
user_152399510
Related