Exploring openpyxl
Discover the automation of Excel file handling with openpyxl, gaining valuable skills for seamless data management.
We'll cover the following...
There are many reasons to prefer .csv over .xlsx files; for instance, they’re faster, consume less memory, and are text editor-compatible. But what if our boss has only asked for a .xlsx file? We’ll have to fulfill these requirements. To export the distances between all stores as a .xlsx file, we only need to modify the code in lines 34 and 35.
# Loop OSM API and create five Excel output files import pandas as pd import requests import json from itertools import islice df = pd.read_csv('MoscowMcD.csv') counterFixed = 0 counterFixed3 = 3 toggle = 0 excelname = 1 # Loop through the Open Street Map (OSM) API and calculate the distance and duration of trips between the stores while counterFixed3 < 16: def get_distance(point1: dict, point2: dict): url = f"""http://router.project-osrm.org/route/v1/driving/{point1["lon"]},{point1["lat"]};{point2["lon"]},{point2["lat"]}?overview=false&alternatives=false""" r = requests.get(url) route = json.loads(r.content)["routes"][0] return (route["distance"], route["duration"]) listDist = [] for i, r in islice(df.iterrows(), counterFixed, None): point1 = {"lat": r["lat"], "lon": r["lon"]} if i ==counterFixed3: break for j, o in df[df.index != i].iterrows(): point2 = {"lat": o["lat"], "lon": o["lon"]} dist, duration = get_distance(point1, point2) listDist.append((i, j, duration, dist)) toggle = 1 distancesDf = pd.DataFrame(listDist, columns=["From", "To", "Duration(s)", "Distance(m)"]) distancesDf = distancesDf.merge(df[["Store"]], left_on = "From", right_index=True).rename(columns={"Store":"StartLocation"}) distancesDf = distancesDf.merge(df[["Store"]], left_on = "To", right_index=True).rename(columns={"Store":"Destination"}) name = "DistanceStores" + str(excelname) filenames = "%s.xlsx" % name # change .csv to .xlsx distancesDf.to_excel(filenames, index=False) # change .to_csv to .to_excel counterFixed = counterFixed +3 counterFixed3 = counterFixed3 +3 excelname = excelname +1 # Enter "ls" in the terminal to view the files
Export output as Excel files
Introduction to openpyxl
The openpyxl
library comes to the rescue when we need to work extensively with Excel in Python.
pip install openpyxl
...