I am new to python and was trying to solve the vehicle routing problem using google OR tools. The code works fine for me but I have a silly issue. I don't know how to save this into google Sheets. I am using the below code
from __future__ import print_function
Install The Libraries
!pip install ortools
!pip install --upgrade gspread
from ortools.constraint_solver import routing_enums_pb2
from ortools.constraint_solver import pywrapcp
Read the Data
from google.colab import auth
auth.authenticate_user()
import gspread
from google.auth import default
creds, _ = default()
gc = gspread.authorize(creds)
wb = gc.open_by_key("Google Sheet ID")
worksheet = wb.worksheet('Input_Colab')
get_all_values gives a list of rows.
rows = worksheet.get_all_values()
Convert to a DataFrame and render.
import pandas as pd
df = pd.DataFrame.from_records(rows[1:],columns=rows[0])
pd.set_option("display.max_columns",None)
import ast
dm = ast.literal_eval(df.iloc[0]['time_matrix'])
dw = ast.literal_eval(df.iloc[0]['time_windows'])
py = ast.literal_eval(df.iloc[0]['Payload'])
vn = ast.literal_eval(df.iloc[0]['No of Vehicles'])
vc = ast.literal_eval(df.iloc[0]['Vehicle Capacity'])
time_matrix = dm
time_windows = dw
demands = py
num_vehicles = vn
vehicle_capacities = vc
depot_index = 0
time_limit_seconds = 1000
def create_data_model(time_matrix, time_windows, num_vehicles, demands, vehicle_capacities, depot_index):
"""Stores the data for the problem."""
data = {}
data['time_matrix'] = time_matrix
data['time_windows'] = time_windows
data['num_vehicles'] = num_vehicles
data['demands'] = demands
data['vehicle_capacities'] = vehicle_capacities
data['depot'] = depot_index
return data
"""Capacitated Vehicles Routing Problem (CVRP) with Time Windows."""
def print_solution(data, manager, routing, solution):
"""Prints solution on console."""
total_distance = 0
total_load = 0
time_dimension = routing.GetDimensionOrDie('Time')
total_time = 0
for vehicle_id in range(data['num_vehicles']):
index = routing.Start(vehicle_id)
plan_output = 'Route for vehicle {}:\n'.format(vehicle_id)
route_load = 0
while not routing.IsEnd(index):
node_index = manager.IndexToNode(index)
time_var = time_dimension.CumulVar(index)
route_load += data['demands'][node_index]
plan_output += 'Place {0:>2} Arrive at {2:>2}min Depart at {3:>2}min (Load {1:>2})\n'.format(manager.IndexToNode(index), route_load, solution.Min(time_var), solution.Max(time_var))
previous_index = index
index = solution.Value(routing.NextVar(index))
time_var = time_dimension.CumulVar(index)
total_time += solution.Min(time_var)
plan_output +="Place {0:>2} Arrive at {2:>2}min \n\n".format(manager.IndexToNode(index), route_load, solution.Min(time_var), solution.Max(time_var))
# route output
plan_output += 'Load of the route: {}\n'.format(route_load)
plan_output += 'Time of the route: {}min\n'.format(solution.Min(time_var))
plan_output += "--------------------"
print(plan_output)
total_load += route_load
print('Total load of all routes: {}'.format(total_load))
print('Total time of all routes: {}min'.format(total_time))
def main():
"""Solve the VRP with time windows."""
# Instantiate the data problem.
data = create_data_model(time_matrix, time_windows, num_vehicles, demands, vehicle_capacities, depot_index)
# Create the routing index manager.
manager = pywrapcp.RoutingIndexManager(len(data['time_matrix']),
data['num_vehicles'], data['depot'])
# Create Routing Model.
routing = pywrapcp.RoutingModel(manager)
# Create and register a transit callback.
def time_callback(from_index, to_index):
"""Returns the travel time between the two nodes."""
# Convert from routing variable Index to time matrix NodeIndex.
from_node = manager.IndexToNode(from_index)
to_node = manager.IndexToNode(to_index)
return data['time_matrix'][from_node][to_node]
transit_callback_index = routing.RegisterTransitCallback(time_callback)
# Define cost of each arc.
routing.SetArcCostEvaluatorOfAllVehicles(transit_callback_index)
# Add Capacity constraint.
def demand_callback(from_index):
"""Returns the demand of the node."""
# Convert from routing variable Index to demands NodeIndex.
from_node = manager.IndexToNode(from_index)
return data['demands'][from_node]
demand_callback_index = routing.RegisterUnaryTransitCallback(
demand_callback)
routing.AddDimensionWithVehicleCapacity(
demand_callback_index,
0, # null capacity slack
data['vehicle_capacities'], # vehicle maximum capacities
True, # start cumul to zero
'Capacity')
# Add Time Windows constraint.
time = 'Time'
routing.AddDimension(
transit_callback_index,
5, # allow waiting time
480, # maximum time per vehicle
False, # Don't force start cumul to zero.
time)
time_dimension = routing.GetDimensionOrDie(time)
# Add time window constraints for each location except depot.
for location_idx, time_window in enumerate(data['time_windows']):
if location_idx == 0:
continue
index = manager.NodeToIndex(location_idx)
time_dimension.CumulVar(index).SetRange(time_window[0], time_window[1])
# Add time window constraints for each vehicle start node.
for vehicle_id in range(data['num_vehicles']):
index = routing.Start(vehicle_id)
time_dimension.CumulVar(index).SetRange(data['time_windows'][0][0],
data['time_windows'][0][1])
# Instantiate route start and end times to produce feasible times.
for i in range(data['num_vehicles']):
routing.AddVariableMinimizedByFinalizer(
time_dimension.CumulVar(routing.Start(i)))
routing.AddVariableMinimizedByFinalizer(
time_dimension.CumulVar(routing.End(i)))
# Allow to drop nodes.
penalty = 1000
for node in range(1, len(data['time_matrix'])):
routing.AddDisjunction([manager.NodeToIndex(node)], penalty)
# Setting first solution heuristic.
search_parameters = pywrapcp.DefaultRoutingSearchParameters()
search_parameters.first_solution_strategy = (
routing_enums_pb2.FirstSolutionStrategy.PATH_CHEAPEST_ARC)
search_parameters.time_limit.seconds = time_limit_seconds
# Solve the problem.
solution = routing.SolveWithParameters(search_parameters)
# Print solution on console.
if solution:
print_solution(data, manager, routing, solution)
return solution
solution = main()