Divide individual Pandas dataframe values by corresponding value in dataset

I am working on a CVRP and have gotten to the point where I have a list of vehicle fleets, where each fleet is made up of a homogeneous vehicle type, and the amount of goods that should be in each fleet. The corresponding dataframe looks like this:

Vehicle               Quantity of Goods
v1                     8.0
v2                    12.0
v3                    16.0
v4                    12.0

Since each fleet is made up of a single vehicle type, I want to find the number of vehicles needed to service that quantity. I.E. I want to divide the quantity of goods the fleet has by the corresponding amount that each individual vehicle can hold, rounding up, so that I know exactly how many vehicles are active. The capacity values are found in the same .json file that I have been using to supply data to the other parts of the model, under the index data[“transportation_data”][“vehicle_capacity”], where the associated dictionary is the following:

"vehicle_capacity": {
      "v1": 8,
      "v2": 6,
      "v3": 4,
      "v4": 3
    }

For example, I want to divide v1’s quantity by 8, which would leave me with 1 vehicle used, or v2’s quantity by 6 leaving 2 vehicles used. One thing that I have tried so far is

df = df.apply(lambda x: x.Quantity_Employed / data["transportation_data"]["vehicle_capacity"][x], axis =1)

However when I try this, I receive an error saying unhashable type: “Series.” I have also tried

df = df.apply(lambda x: x.Quantity_Employed / data["transportation_data"]["vehicle_capacity"][x.Vehicles], axis =1)

But I continue to receive an error, this one being ‘Series’ object has no attribute ‘Vehicle.’ I have read through the documentation a bit, and have not found anything along these lines there either. Any help is appreciated, thanks!

Answer

I think the problem lies in the way you access your data, especially with “x.Quantity_Employed”. This works, so the problem is probably in the lambda function:

import pandas as pd

capacity = {'v1' : 8, 'v2' : 12, 'v3' : 16, 'v4' : 12}
df = pd.DataFrame({'vehicle' : ['v1', 'v2', 'v3', 'v4'], 'quantity' : [8, 6, 4, 3]})

f = lambda x: capacity[x['vehicle']] / x['quantity'] 

df.loc[:, 'result'] = df.apply(f, axis=1)