0

I have two pandas dataframes; buffers_slots and slots_vessels Both contain only zeros and ones as data. I'm working on a vessel assignment problem where a number of vessels are selected and used to prepare a number of buffers. The link between buffers and vessels are notional "slots".

The buffers_slots dataframe tells me which slot each buffer is prepared in. In it, each row sums to one, i.e. each buffer is made in only one slot.

>>> buffers_slots
              5    7    8
Buffer #24  0.0  0.0  1.0
Buffer #25  1.0  0.0  0.0
Buffer #26  1.0  0.0  0.0
Buffer #27  0.0  0.0  1.0
Buffer #28  0.0  1.0  0.0
Buffer #29  0.0  1.0  0.0

The slots_vessels dataframe tells me which size vessel is in a given slot (it is possible to have empty slots - i.e. each row sums to either 0 or 1. It is also possible to use the same size vessel in more than one slot - i.e. each column may sum to 0, 1 or more than one).

>>> slots_vessels
   1000 L  2000 L  3000 L  4000 L  5000 L  6000 L  7000 L  8000 L
0     0.0     0.0     0.0     0.0     0.0     0.0     0.0     0.0
1     0.0     0.0     0.0     0.0     0.0     0.0     0.0     0.0
2     0.0     0.0     0.0     0.0     0.0     0.0     0.0     0.0
3     0.0     0.0     0.0     0.0     0.0     0.0     0.0     0.0
4     0.0     0.0     0.0     0.0     0.0     0.0     0.0     0.0
5     0.0     1.0     0.0     0.0     0.0     0.0     0.0     0.0
6     0.0     0.0     0.0     0.0     0.0     0.0     0.0     0.0
7     0.0     0.0     0.0     0.0     0.0     0.0     1.0     0.0
8     0.0     0.0     0.0     1.0     0.0     0.0     0.0     0.0

I essentially want to use the slots_vessels dataframe to translate the column names in the buffers_slots dataframe, i.e. replacing 5, 7, 8 with '2000 L', '7000 L' and '4000 L' respectively, giving the following output:

>>> buffers_vessels
              2000 L    7000 L    4000 L
Buffer #24       0.0       0.0       1.0
Buffer #25       1.0       0.0       0.0
Buffer #26       1.0       0.0       0.0
Buffer #27       0.0       0.0       1.0
Buffer #28       0.0       1.0       0.0
Buffer #29       0.0       1.0       0.0

I'm thinking the solution may involve creating a dict mapping slots to vessels, i.e. something like:

>>> slots
{0: None, 1: None, 2: None, 3: None, 4: None, 5: '2000 L', 6: None, 7: '7000 L', 8: '4000 L'}

...but I'm not sure how to create such a dict.

Alternatively, is there some neat way of mapping/translating to achieve my goal?

multipitch
  • 189
  • 2
  • 11
  • 1
    Is there a rule that makes 5 2000L, 7 7000L and 8, 4000L? – Jon Clements Apr 02 '18 at 18:01
  • That comes from the `slots_vessels` table, which is the output of a linear programming problem. If you look e.g. at row `5` of `slots_vessels`, the only column with a 1-value is `'2000 L'` i.e. slot `5` should map to vessel size `'2000 L'`. – multipitch Apr 02 '18 at 18:04

2 Answers2

0

If you have only maximum one 1.0 per row, you could use the function described here to find the column that matches 1.0 to create a new dataframe:

Get column name where value is something in pandas dataframe

cols['column'] = df.apply(lambda x: df.columns[x.argmax()], axis = 1)

And convert this to a dict d_cols.

You could then replace the name of the columns with the help of the dict you have just created:

df.rename(d_cols)
Dric512
  • 3,525
  • 1
  • 20
  • 27
  • `dict(slots_vessels.T.idxmax())` (`argmax` is deprecated, or soon to be) gives the following dict: `{0: '1000 L', 1: '1000 L', 2: '1000 L', 3: '2000 L', 4: '1000 L', 5: '1000 L', 6: '1000 L', 7: '7000 L', 8: '4000 L'}` which is slightly incorrect in that where there is no vessel in a slot it tells me that there is a `1000 L` vessel in a slot. This is actually OK because I'm not interrogating empty slots. Thanks for putting me on the right track – multipitch Apr 02 '18 at 18:49
0

The following did the trick:

slots = dict(slots_vessels.T.idxmax())
buffers_vessels = buffers_slots.rename(slots, axis='columns')

Outputs:

>>> slots
{0: '1000 L', 1: '1000 L', 2: '1000 L', 3: '2000 L', 4: '1000 L', 5: '1000 L', 6: '1000 L', 7: '7000 L', 8: '4000 L'}
>>> buffers_vessels
            2000 L  7000 L  4000 L
Buffer #24     0.0     0.0     1.0
Buffer #25     1.0     0.0     0.0
Buffer #26     1.0     0.0     0.0
Buffer #27     0.0     0.0     1.0
Buffer #28     0.0     1.0     0.0
Buffer #29     0.0     1.0     0.0

The values in the slots dict are a bit misleading since it is saying that there is a 1000 L vessel in every empty slot, but since we aren't interested in empty slots, this error isn't carried through. It would be nice to replace the idxmax function with a "where equal to 1" function if such a thing existed to create the slots dict shown in the OP.

multipitch
  • 189
  • 2
  • 11