CODEWITHSUNDEEP
pythonpandaslinear-programming
Rocky
Rocky

Reputation: 105

Allocation wrapper in python

I am new in python and have output of simple LP problem.

As shown

In this problem, several PART's make a House and several house make a society. There are house and society level target on delivery. If the delivery is not met at any level, poor Part is replaced (replaced = Y) with part delivering 50 units.

Solution is optimized but, we don't have the capacity to replace. So, I want to prioritize replacement.

Ex: I have capacity to do 3 replacement and solution is giving 7 replacement.

Can we postprocess the solution to get the 3 replacement based on priority.

given = 3 replacement max priority = S3 (society 3), H2, H1, S2.

My output would be replacement at U, R and A, leaving other as it is? is it possible to do in python? or this can't be done in python (In that case, I can use an excel macro).

Edit

Raw data:
data = [
    {'Part': 'A', 'House': 'H1', 'Society': 'S1', 'Present_Delivery': 10, 'Replaced': 'Y'},
    {'Part': 'B', 'House': 'H1', 'Society': 'S1', 'Present_Delivery': 30, 'Replaced': ''},
    {'Part': 'C', 'House': 'H1', 'Society': 'S1', 'Present_Delivery': 40, 'Replaced': ''},
    {'Part': 'D', 'House': 'H1', 'Society': 'S1', 'Present_Delivery': 50, 'Replaced': ''},
    {'Part': 'E', 'House': 'H2', 'Society': 'S1', 'Present_Delivery': 50, 'Replaced': ''},
    {'Part': 'F', 'House': 'H2', 'Society': 'S1', 'Present_Delivery': 50, 'Replaced': ''},
    {'Part': 'G', 'House': 'H2', 'Society': 'S1', 'Present_Delivery': 50, 'Replaced': ''},
    {'Part': 'H', 'House': 'H2', 'Society': 'S1', 'Present_Delivery': 50, 'Replaced': ''},

    {'Part': 'I', 'House': 'H3', 'Society': 'S2', 'Present_Delivery': 30, 'Replaced': 'Y'},
    {'Part': 'J', 'House': 'H3', 'Society': 'S2', 'Present_Delivery': 40, 'Replaced': ''},
    {'Part': 'K', 'House': 'H3', 'Society': 'S2', 'Present_Delivery': 50, 'Replaced': ''},
    {'Part': 'L', 'House': 'H4', 'Society': 'S2', 'Present_Delivery': 30, 'Replaced': 'Y'},
    {'Part': 'M', 'House': 'H4', 'Society': 'S2', 'Present_Delivery': 30, 'Replaced': 'Y'},
    {'Part': 'N', 'House': 'H4', 'Society': 'S2', 'Present_Delivery': 50, 'Replaced': ''},
    {'Part': 'O', 'House': 'H5', 'Society': 'S2', 'Present_Delivery': 20, 'Replaced': 'Y'},
    {'Part': 'P', 'House': 'H5', 'Society': 'S2', 'Present_Delivery': 50, 'Replaced': ''},
    {'Part': 'Q', 'House': 'H5', 'Society': 'S2', 'Present_Delivery': 50, 'Replaced': ''},

    {'Part': 'R', 'House': 'H6', 'Society': 'S3', 'Present_Delivery': 20, 'Replaced': 'Y'},
    {'Part': 'S', 'House': 'H6', 'Society': 'S3', 'Present_Delivery': 40, 'Replaced': ''},
    {'Part': 'T', 'House': 'H6', 'Society': 'S3', 'Present_Delivery': 50, 'Replaced': ''},
    {'Part': 'U', 'House': 'H7', 'Society': 'S3', 'Present_Delivery': 15, 'Replaced': 'Y'},
    {'Part': 'V', 'House': 'H7', 'Society': 'S3', 'Present_Delivery': 40, 'Replaced': ''},
    {'Part': 'W', 'House': 'H7', 'Society': 'S3', 'Present_Delivery': 50, 'Replaced': ''},

]

house_targets = {'H1': 140,
                 'H2': 160,
                 'H3': 120,
                 'H4': 110,
                 'H5': 120,
                 'H6': 115,
                 'H7': 105,

                 }

society_targets = {'S1': 330,
                   'S2': 500,
                   'S3': 250}
df = pd.DataFrame(data)
    for house, target in house_targets.items():
        df.loc[df['House'] == house, 'House_Target'] = target
    for society, target in society_targets.items():
        df.loc[df['Society'] == society, 'Society_Target'] = target
    replacement_value = 50
    df.loc[df['Replaced'] == 'Y', 'replacement_value'] = replacement_value - df['Present_Delivery']
    df['replacement_value'].fillna(0, inplace=True)

Upvotes: 2

Views: 309

Answers (2)

ckedar
ckedar

Reputation: 1909

This can be implemented using join, combine_first and sort:

priority = ["S3", "H2", "H1", "S2"]
priority = pd.Series(range(len(priority)), index=priority, name="priority")

df["priority"] = df.join(priority, on="Society").priority.combine_first(df.join(priority, on="House").priority)

result = df[df.replacement_value > 0] .sort_values("priority").head(3)
print(result)

Result:

   Part House Society  Present_Delivery Replaced  House_Target  Society_Target  replacement_value  priority
17    R    H6      S3                20        Y         115.0           250.0               30.0       0.0
20    U    H7      S3                15        Y         105.0           250.0               35.0       0.0
0     A    H1      S1                10        Y         140.0           330.0               40.0       2.0

Upvotes: 0

above_c_level
above_c_level

Reputation: 3939

In pandas the solution is straight forward. You just need to slice the right column with the given priority and sort it. Most of the code is just boiler plate for the wrapper.

Wrapper

import pandas as pd

class ReplacementConstraint:
    def __init__(self, df: pd.DataFrame, *, max_replacements: int, priority: list):
        assert isinstance(df, pd.DataFrame)
        self.df = df
        self.max_replacements = max_replacements
        self.priority = priority
        self.result = pd.DataFrame()

    @staticmethod
    def parse_priority(prio_str):
        """Mapping priority String to column"""
        col_map = {'H': 'House',
                   'S': 'Society'}
        return col_map[prio_str[0]]

    def calculate(self):
        """Sort slices of dataframe with respect to priorities and append to self.result"""
        for prio in self.priority:
            col = self.parse_priority(prio)
            mask = (self.df['Replaced'] == 'Y') & (self.df[col] == prio)
            result_tmp = self.df[mask].sort_values('replacement_value', ascending=False)
            self.result = pd.concat([self.result, result_tmp])
        self.result = self.result.iloc[:self.max_replacements]

Given a dataframe df (see below) you can use the wrapper as:

    wrapper = ReplacementConstraint(df, max_replacements=3, priority=['S3', 'H2', 'H1', 'S2'])
    wrapper.calculate()
    print(wrapper.result)

The solution looks like:

   Part House Society  ...  House_Target Society_Target  replacement_value
20    U    H7      S3  ...         105.0          250.0               35.0
17    R    H6      S3  ...         115.0          250.0               30.0
0     A    H1      S1  ...         140.0          330.0               40.0

Raw data

The typing of the dataframe data took most of my time. Please consider posting text not an image the next time. I post it here so if someone else wants to post another solution, she doesn't need to type it again. Also, by posting the dataframe my solution is reproducible.

data = [
    {'Part': 'A', 'House': 'H1', 'Society': 'S1', 'Present_Delivery': 10, 'Replaced': 'Y'},
    {'Part': 'B', 'House': 'H1', 'Society': 'S1', 'Present_Delivery': 30, 'Replaced': ''},
    {'Part': 'C', 'House': 'H1', 'Society': 'S1', 'Present_Delivery': 40, 'Replaced': ''},
    {'Part': 'D', 'House': 'H1', 'Society': 'S1', 'Present_Delivery': 50, 'Replaced': ''},
    {'Part': 'E', 'House': 'H2', 'Society': 'S1', 'Present_Delivery': 50, 'Replaced': ''},
    {'Part': 'F', 'House': 'H2', 'Society': 'S1', 'Present_Delivery': 50, 'Replaced': ''},
    {'Part': 'G', 'House': 'H2', 'Society': 'S1', 'Present_Delivery': 50, 'Replaced': ''},
    {'Part': 'H', 'House': 'H2', 'Society': 'S1', 'Present_Delivery': 50, 'Replaced': ''},

    {'Part': 'I', 'House': 'H3', 'Society': 'S2', 'Present_Delivery': 30, 'Replaced': 'Y'},
    {'Part': 'J', 'House': 'H3', 'Society': 'S2', 'Present_Delivery': 40, 'Replaced': ''},
    {'Part': 'K', 'House': 'H3', 'Society': 'S2', 'Present_Delivery': 50, 'Replaced': ''},
    {'Part': 'L', 'House': 'H4', 'Society': 'S2', 'Present_Delivery': 30, 'Replaced': 'Y'},
    {'Part': 'M', 'House': 'H4', 'Society': 'S2', 'Present_Delivery': 30, 'Replaced': 'Y'},
    {'Part': 'N', 'House': 'H4', 'Society': 'S2', 'Present_Delivery': 50, 'Replaced': ''},
    {'Part': 'O', 'House': 'H5', 'Society': 'S2', 'Present_Delivery': 20, 'Replaced': 'Y'},
    {'Part': 'P', 'House': 'H5', 'Society': 'S2', 'Present_Delivery': 50, 'Replaced': ''},
    {'Part': 'Q', 'House': 'H5', 'Society': 'S2', 'Present_Delivery': 50, 'Replaced': ''},

    {'Part': 'R', 'House': 'H6', 'Society': 'S3', 'Present_Delivery': 20, 'Replaced': 'Y'},
    {'Part': 'S', 'House': 'H6', 'Society': 'S3', 'Present_Delivery': 40, 'Replaced': ''},
    {'Part': 'T', 'House': 'H6', 'Society': 'S3', 'Present_Delivery': 50, 'Replaced': ''},
    {'Part': 'U', 'House': 'H7', 'Society': 'S3', 'Present_Delivery': 15, 'Replaced': 'Y'},
    {'Part': 'V', 'House': 'H7', 'Society': 'S3', 'Present_Delivery': 40, 'Replaced': ''},
    {'Part': 'W', 'House': 'H7', 'Society': 'S3', 'Present_Delivery': 50, 'Replaced': ''},

]

house_targets = {'H1': 140,
                 'H2': 160,
                 'H3': 120,
                 'H4': 110,
                 'H5': 120,
                 'H6': 115,
                 'H7': 105,

                 }

society_targets = {'S1': 330,
                   'S2': 500,
                   'S3': 250}
df = pd.DataFrame(data)
    for house, target in house_targets.items():
        df.loc[df['House'] == house, 'House_Target'] = target
    for society, target in society_targets.items():
        df.loc[df['Society'] == society, 'Society_Target'] = target
    replacement_value = 50
    df.loc[df['Replaced'] == 'Y', 'replacement_value'] = replacement_value - df['Present_Delivery']
    df['replacement_value'].fillna(0, inplace=True)

Possible improvements

  • The wrapper does not check the target values. I assumed that will not be relevant because of the first optimization.
  • The resulting dataframe does not correct the values of deliveries. That is no big deal. I just wanted to provide a minimal example. You can improve the wrapper to your needs.

Upvotes: 1

Related Questions