Python/Pandas implementation for grouping with a condition and ranking

user_10843894 0
Viewed 66

I want to group by the zip code and form tucks, but if it hits 30000 it should form another truck. I am not able to apply group by and rank it. It might be required to sort the weights in the ascending order to form the right truck. Any help would be really appreciated.

I have the following data:

   Load No.  Zip Code  Pounds    
     1         50507    20000 
     2         50507    8000
     3         50507    5000 
     4         60001    28000
     5         60001    30000
     6         60001    2000
     7         60001    4000
     8         60002    20000
     9         60002    18000
     10        60002    13000

Output:

Load No.     Zip Code  Pounds    Truck   Total Weight
     1         50507    20000     1         28000
     2         50507    8000      1         28000
     3         50507    5000      2         5000
     4         60001    28000     3         30000
     5         60001    30000     5         2000
     6         60001    2000      3         30000
     7         60001    4000      4         4000
     8         60002    20000     6         20000
     9         60002    18000     7         18000
     10        60002    13000     8         13000

I have sorted the data frame: data=data.sort_values(by=['Zip Code','Pounds'])

Also tried grouping by Zip Code but failing to put in the condition(>20000) to form a dense rank: data['Total weight'] = data.groupby('Zip Code')['Pounds'].transform(sum)

pandas rank group-by numpy python
1 Answers

I think I see what you're trying to accomplish, so I completed part of what you're looking for and am leaving the rest for you to determine yourself. The most difficult piece of this problem seems to be intelligently allocating the loads to maximize truck space. Splitting things up is no problem, but it's not as simple as just checking if the load is less than 30,000.

First, a method to intelligently allocate the loads among trucks:

def build_trucks(sorted_loads):

    load_copy = np.array(sorted_loads)

    truck_max = 30000

    # check if any loads are > truck_max and split them into bins that sum to the load

    while len(load_copy) > 0:

        truck = []
        truck_load = 0

        for i, load in enumerate(load_copy):
            if truck_load + load <= truck_max:
                truck.append(i)
                truck_load += load

        yield load_copy[truck]

        load_copy = np.delete(load_copy, truck)

You didn't mention if any loads would ever start as more than 30,000, so I left than incomplete. That itself would be an interesting problem (break 45,000 into two loads: 30,000 and 15,000, and 65,000 into two 30,000 and a 5,000). I ran this against a few tests, including yours:

print(list(build_trucks(np.array([20000, 8000, 5000]))))
print(list(build_trucks(np.array([30000, 28000, 4000, 2000]))))
print(list(build_trucks(np.array([20000, 18000, 13000]))))

print(list(build_trucks(sorted(np.array([25000, 1000, 1000, 4000, 5500]), reverse=True))))

which outputs:

[array([20000,  8000]), array([5000])]
[array([30000]), array([28000,  2000]), array([4000])]
[array([20000]), array([18000]), array([13000])]
[array([25000,  4000,  1000]), array([5500, 1000])]

To see how this behaves, I ran:

grp = data.groupby('zip')

for i, g in grp:
    print(g.sort_values('pounds', ascending=False))
    print()
    print(list(build_trucks(g['pounds'])))
    print()

where data is a DataFrame of the original data you supplied. Hopefully the remainder of the problem becomes apparent to you. If not, feel free to ask and I will do my best to help (I left a lot of this incomplete because it's a great learning problem for you however I didn't want to spend too much of my own time on it). There are likely many ways to accomplish this, this is the first way I saw. I also thought of a recursive way to do this Either may or may not be efficient.

user_32344820
Related