Merge excel sheets into a single PDF file

 Here you will find a python code that will merge all excel sheets in a workbook into a single PDF file.

An excel workbook usually contains more than one or more sheets. In this post I want to automate the process of converting several sheets into pdf files then merge those pdf files into one single pdf.

import os
import glob
from PyPDF2 import PdfFileMerger
import win32com.client as client

# ------------------------------------------------
# Convert Excel file to PDF
# ------------------------------------------------
my_xlxs_file = r"C:\Users\`HYJ7\Desktop\XLS to PDF\Sample_quicktransportsolutions.xlsx"

# Open Microsoft Excel in the background
xl_app = client.DispatchEx("Excel.Application")
xl_app.Visible = False
xl_app.DisplayAlerts = False
xl_app.ScreenUpdating = False

pdf_path = os.path.splitext(my_xlxs_file)[0]

# Read Excel File
workbook = xl_app.Workbooks.Open(my_xlxs_file)
sheet_names = [sheet.Name for sheet in workbook.Sheets] # get sheet names

n = 1
for sht in range(len(sheet_names)):
    work_sheet = workbook.Worksheets[sht]

    work_sheet.ExportAsFixedFormat(0, f"{pdf_path}_{n}")
    n += 1

# Close the workbook
workbook.Close()

# ------------------------------------------------
# Merge PDF files into a single file...
# ------------------------------------------------

output_pdf_name = 'TestMergedPDF'
input_dir = r'C:\\Users\\`HYJ7\\Desktop\\XLS to PDF\\'
merge_list = []

for x in os.listdir(input_dir):
    if not x.endswith('.pdf'):
        continue
    merge_list.append(os.path.join(input_dir, x))

merger = PdfFileMerger()

for pdf in merge_list:
    merger.append(pdf)

merger.write(input_dir + f"\\{output_pdf_name}.pdf") #your output directory and pdf_file name
merger.close()

# Delete individual pdf files...
for pdf in merge_list:
    os.remove(pdf)

That is it!

AutoCAD Programming using AutoLISP

What is LISP

LISP stands for "List Processor". It is a programming language that was developed in the late 1950s by John McCarthy. It is one of the oldest high-level programming languages still in use today. LISP is known for its unique syntax, which is based on nested parentheses and prefix notation.

LISP has found applications in various domains, including artificial intelligence, natural language processing, and symbolic mathematics. It has been used for academic research, commercial software development, and prototyping new programming language concepts. LISP has been influential in the development of other programming languages and has inspired many dialects and variants over the years. Some notable LISP dialects include Common Lisp, Scheme, and Clojure.

What is AutoLISP

AutoLISP is a dialect of the LISP programming language that is specifically designed for extending the capabilities of AutoCAD, a popular computer-aided design (CAD) software. AutoLISP allows users to automate repetitive tasks, create custom commands, and add new functionality to AutoCAD.

AutoLISP is a procedural programming language, which means it follows a step-by-step approach to executing instructions. It provides a set of functions and commands that can be used to interact with AutoCAD's drawing objects, manipulate geometry, modify settings, and perform various operations.

With AutoLISP, you can write scripts and programs that automate tasks such as creating objects, modifying attributes, generating reports, and implementing custom design algorithms. These programs can be executed within the AutoCAD environment, providing users with the ability to tailor AutoCAD to their specific needs and streamline their workflows.

AutoLISP programs are typically written in plain text files with a ".lsp" extension. They can be loaded into AutoCAD using the "Appload" command, which makes the functions and commands defined in the AutoLISP program available for use within the software.

AutoLISP has a rich set of built-in functions for manipulating lists, strings, numbers, and other data types. It also provides control structures such as loops and conditionals to enable decision-making and repetition in your programs. Furthermore, AutoLISP supports the use of variables, user-defined functions, and error handling mechanisms. By leveraging AutoLISP, users can enhance their productivity, automate repetitive tasks, and extend the capabilities of AutoCAD according to their specific requirements.

Structure of AutoLISP Syntax

The syntax of AutoLISP follows the general principles of the LISP programming language but with specific features tailored for use within AutoCAD. Here are some key aspects of AutoLISP syntax:

1. Parentheses: AutoLISP uses parentheses extensively to denote function calls and to structure expressions. Each function call is enclosed in parentheses, with the function name followed by its arguments.

   Example: `(setq x (+ 2 3))`

2. Prefix notation: AutoLISP uses prefix notation, which means the function name precedes its arguments. This differs from traditional infix notation used in many other programming languages.

   Example: `(+ 2 3)` instead of `2 + 3`

3. Lists: AutoLISP treats code and data structures uniformly using lists. A list is enclosed in parentheses and can contain any number of elements, which can be atoms (symbols or numbers) or nested lists.

   Example: `(setq mylist '(1 2 3))`

4. Symbols and variables: Symbols in AutoLISP are used to represent variables, function names, and special operators. They are typically strings of alphanumeric characters and can include special characters like dashes and underscores.

   Example: `(setq radius 5)`

5. Quoting: To prevent the evaluation of expressions, the quote function (') or the backquote syntax (`) is used to quote the following expression. This is useful when you want to treat an expression as data rather than executing it.

   Example: `(setq mylist '(1 2 3))`

6. Functions and special operators: AutoLISP provides a wide range of built-in functions and special operators for performing various operations. Functions are invoked by enclosing the function name and its arguments in parentheses.

   Example: `(setq sum (+ 2 3))`

7. Variables and assignments: Variables in AutoLISP are created using the `setq` function, which stands for "set quote." It assigns a value to a symbol, creating a variable or updating its value.

   Example: `(setq x 10)`

8. Control structures: AutoLISP supports control structures like conditional statements and loops for program flow control. The `if` function is used for conditional branching, while the `repeat` and `while` functions are used for creating loops.

   Example:

   ```

   (if (> x 5)

       (setq result "Greater than 5")

       (setq result "Less than or equal to 5"))

   ```

These are some of the fundamental elements of AutoLISP syntax. Understanding these aspects will help you write AutoLISP programs and extend the capabilities of AutoCAD.

Animating SVG maps 101

 Let take a look at how to animate web maps in SVG format. SVG stands for 'Scalable Vector Graphics' and it is a web-friendly vector file format. It defines vector-based graphics in XML format.

Advantages of using SVG over other image formats (like JPEG, PNG and GIF) are:

• SVG images can be created and edited with any text editor
• SVG images can be searched, indexed, scripted, and compressed
• SVG images are scalable
• SVG images can be printed with high quality at any resolution
• SVG images are zoomable
• SVG graphics do NOT lose any quality if they are zoomed or resized
• SVG is an open standard
• SVG files are pure XML

SVG images with a drawing program, like Inkscape, Adobe Illustrator etc. Since we will be SVG maps, we can use GIS software like QGIS, ArcGIS etc to create the map then export it to SVG. We can also use online tool like mapshaper to convert GIS map to SVG format.

Whatever method you used in creating you SVG maps is of less importance since all we care in this post is to animate it using CSS.

I will apply the CSS styles right inside the SVG file, so I will surround it with 'Character Data' tag <![CDATA[ ... ]]> to prevent some characters been parsed as part of the SVG XML tags.

1. Change map color on hover and rotate

<style type="text/css" media="screen">
  <![CDATA[

  path {
    cursor: pointer;
    fill: #fff;
    }


  path:hover {
    fill: #ff68ff;

    transition-property: rotate;
    transition-duration: 30s;
    rotate: -90deg;
    }


  ]]>
</style>

2. On hover change map stroke color and stroke width

<style type="text/css" media="screen">
  <![CDATA[

  path {
    cursor: pointer;
    fill: #fff;
    opacity: 1;

    }


  path:hover {
    transition: stroke-width 1s, stroke 1s;

    stroke-width: 8;
    stroke: #000fff;
    }

  ]]>
</style>

Plotting Survey Data in AutoCAD Using Script

 Often you will find yourself trying to plot multiple survey points in AutoCAD. How about if I tell you that you don't have to manually plot each point after the other? You can type a script command with the data points you want to plot into a file and have it plotted on the fly.

Point	Easting	Northing
P1	331117.87	939830.78
P2	331132.19	939673.41
P3	331144.47	939620.28
P4	331107.64	939601.88
P5	331117.87	939548.75
P6	331173.11	939507.87
P7	331258.45	939526.61
P8	331326.25	939512.55
P9	331438.83	939554.72
P10	331501.51	939534.28
P11	331546.29	939513.83
P12	331630.72	939516.38
P13	331715.15	939521.5
P14	331749.7	939536.83
P15	331766.33	939577.72
P16	331749.7	939665.9
P17	331666.54	939700.4
P18	331557.8	939688.9
P19	331443.94	939678.68
P20	331424.75	939745.13
P21	331391.49	939816.69
P22	331359.51	939883.14
P23	331227.51	939850.09

Plotting using Coordinates

Lets say you got these twenty-three survey points to plot. To use a script to plot the points, you will create a script file with .scr extension (AutoCAD Script (.scr)).

Inside the file, you will type the data like this:-

_POINT 331117.87,939830.78
_POINT 331132.19,939673.41
_POINT 331144.47,939620.28
_POINT 331107.64,939601.88
_POINT 331117.87,939548.75
_POINT 331173.11,939507.87
_POINT 331258.45,939526.61
_POINT 331326.25,939512.55
_POINT 331438.83,939554.72
_POINT 331501.51,939534.28
_POINT 331546.29,939513.83
_POINT 331630.72,939516.38
_POINT 331715.15,939521.50
_POINT 331749.70,939536.83
_POINT 331766.33,939577.72
_POINT 331749.70,939665.90
_POINT 331666.54,939700.40
_POINT 331557.80,939688.90
_POINT 331443.94,939678.68
_POINT 331424.75,939745.13
_POINT 331391.49,939816.69
_POINT 331359.51,939883.14
_POINT 331227.51,939850.09

Run or Load the script file using the SCRIPT command. This will plot all the points starting from the first to the last point.

Make sure you use the PTYPE command to set the point style and size. The end result should look like this image below:-

Mapping GTBank Card Printing Machine Locations

 In this post, I will map the locations of GTBank Card Printing Machine. 

As at the time of writing, GTBank listed 66 locations where you can self-print ATM card instantly. 

These addresses are note geocoded (that is they don't have latitude and longitude coordinates). For GIS mapping purpose, we need the latitude and longitude coordinates.

Lets see if the trending AI tool "ChatGPT" can help complete this geocoding process. Unfortunately, ChatGPT doesn't give me direct result instead it gave hint on where to get the results.

Make use of the hints provided by ChatGPT, I was able to generate the  latitude and longitude coordinates of GTBank card printing machine locations for mapping purpose as seen below.

Thanks for reading.

Format codes for python DateTime object

 Python DateTime object have several format codes as listed on this w3schools page. In this post, we shall extract different components of the table from a datatime object that looks like this: YYYY-MM-DD HH:MM:SS

Where;-

• YYYY = Year
• MM = Month
• DD = Day
• HH = Hour
• MM = Minute
• SS = Second

Example is: '2023-02-01 10:04:19'. 

The table below shows the format codes and their description;-

from datetime import datetime


date_time_string = '2023-02-01 10:04:19'
dt = datetime.fromisoformat(date_time_string)


print(dt.strftime("Weekday, short version >> %a \n"))
print(dt.strftime("Weekday, full version >> %A \n"))
print(dt.strftime("Weekday as a number 0-6, 0 is Sunday >> %w \n"))
print(dt.strftime("Day of month 01-31 >> %d \n"))
print(dt.strftime("Month name, short version >> %b \n"))
print(dt.strftime("Month name, full version >> %B \n"))
print(dt.strftime("Month as a number 01-12 >> %m \n"))
print(dt.strftime("Year, short version, without century >> %y \n"))
print(dt.strftime("Year, full version >> %Y \n"))
print(dt.strftime("Hour 00-23 >> %H \n"))
print(dt.strftime("Hour 00-12 >> %I \n"))
print(dt.strftime("AM/PM >> %p \n"))
print(dt.strftime("Minute 00-59 >> %M \n"))
print(dt.strftime("Second 00-59 >> %S \n"))
print(dt.strftime("Microsecond 000000-999999 >> %f \n"))
print(dt.strftime("UTC offset >> %z \n"))
print(dt.strftime("Timezone >> %Z \n"))
print(dt.strftime("Day number of year 001-366 >> %j \n"))
print(dt.strftime("Week number of year, Sunday as the first day of week, 00-53 >> %U \n"))
print(dt.strftime("Week number of year, Monday as the first day of week, 00-53 >> %W \n"))
print(dt.strftime("Local version of date and time >> %c \n"))
print(dt.strftime("Century >> %C \n"))
print(dt.strftime("Local version of date >> %x \n"))
print(dt.strftime("Local version of time >> %X \n"))
print(dt.strftime("A percent character >> %% \n"))
print(dt.strftime("ISO 8601 year >> %G \n"))
print(dt.strftime("ISO 8601 weekday (1-7) >> %u \n"))
print(dt.strftime("ISO 8601 weeknumber (01-53) >> %V \n"))

That is it!

Data Wrangling of GIS API Data Using Python

Data wrangling in the context of GIS (Geographic Information System) typically involves processing and manipulating spatial data to extract valuable insights or prepare it for further analysis.

In this post, we shall look at extracting API data to prepare it for further analysis in QGIS or any GIS software. Basically, we will use the two different API datasets listed below:-

1. Digital Atlas of the Roman Empire

2. REST countries

Lets get started... So we want to get the API data into a friendly format that a GIS software will read in for further analysis. In this case we want the format to be a spread sheet in .CSV extension.

 Digital Atlas of the Roman Empire

Just as the title suggest, the API provides information on cities of the Roman Empire.

import json
import requests
import pandas as pd

resp = requests.get('http://imperium.ahlfeldt.se/api/geojson.php').text
json_obj = json.loads(resp)
# --------------------------


json_obj_df = pd.DataFrame(json_obj)

data_list = []
for item in json_obj_df['features']:
    coordinates = json_obj_df['features'][0]['geometry']['coordinates']

    name = json_obj_df['features'][0]['properties']['name']
    ids = json_obj_df['features'][0]['properties']['id']
    ancient = json_obj_df['features'][0]['properties']['ancient']
    country = json_obj_df['features'][0]['properties']['country']
    types = json_obj_df['features'][0]['properties']['type']
    numType = json_obj_df['features'][0]['properties']['numType']
    precision = json_obj_df['features'][0]['properties']['precision']

    data = coordinates, name, ids, ancient, country, types, numType, precision
    data_list.append(data)
# --------------------------

data_list_df = pd.DataFrame(data_list)
data_list_df

REST countries

This is an API that provides information about countries via a RESTful API.

The code below is real world application where the data was wrangled and visualized using bokeh library.

# importing the modules
import json
import requests
from datetime import datetime

import pandas as pd

import pandas_bokeh # pip install pandas-bokeh
pandas_bokeh.output_notebook()

from bokeh.plotting import figure, output_file, show



# Bokeh is a Data Visualization library that provides interactive charts and plots.
# Use this command to install Bokeh: pip install bokeh

# Get API content using requests library...
response = requests.get('https://restcountries.com/v3.1/all')
data = json.loads(response.text)


# Write API data to text file...
fname = datetime.today().strftime('%Y%b%d%H%M%S')
with open(f'{fname}.txt', 'w', encoding="utf-8") as f:
    print(data, file=f)

# Write to CSV file..
df = pd.DataFrame(data)
df.to_csv(f'{fname}.csv', encoding="utf-8-sig", index=False)

# Read data from text file...
with open(f'{fname}.txt', 'r', encoding="utf-8") as f:
    txt_data = f.readlines()


# Prepare data for visualization using Bokeh....
common_name = [ x['name']['common'] for x in data ]
official_name = [ x['name']['official'] for x in data ]
population = [ x['population'] for x in data ]
region = [ x['region'] for x in data ]
continent = [ x['continents'][0] for x in data ]
area = [ x['area'] for x in data ]
latlng = [ x['latlng'] for x in data ]
lat_Y = [x[0] for x in latlng]
lng_X = [x[1] for x in latlng]


# Create scatter plot of countries latlong coordinates...
# create a new plot with a title and axis labels
p = figure(title="Coutries Location", x_axis_label="Longitude", y_axis_label="Latitude")

# add circle renderer with additional arguments
p.circle(
    lng_X,
    lat_Y,
    legend_label="Countries",
    fill_color="blue",
    fill_alpha=0.2,
    line_color="blue",
    size=8,
)


# show the results
show(p)

Happy coding...!

Find longest name on a list and add white spaces

 The requirement here is the find the longest name in a list of names and prepend the names with certain length of white spaces

## Find longest name...
names = ["GAJERE VINCENT", "BASHIRU SAHEED OLAMIDE", "IBRAHIM ABUBAKAR ISAH", "AL-HASSAN MUSA ABDULRAHMAN", "OGWUCHE OGBENE VICTORIA", "RAIMI RAFATU AMINAT", "OGBONNA NKWADOCHUKWU VICTORY", "JOB SHIGABA TASHILANI", "ABANG OCHIBE TREASURE", "ANDREW JOSHUA ", "AMEH JOHN OYOCHE", "PHILIP SAMSON ", "SALIHU AWWAL", "DANLADI VICTOR KARSHI", "IBRAHIM FARIDA JIBRIN", "HUSSEIN ONYIOZA AMINAT", "BITRUS JOSEPH ", "YAHAYA ABDULBASID", "MAIKUDI OLLO MIRACLE", "ADEYI SAMSON DIEGO", "BENJAMIN TAGWAI THANKGOD", "EDEGO JAMES EYA", "ABDULMANAN OMEIZA NURUDEEN", "YAHAYA OVAYOZA UMMULKULSUM", "ALKALI UMAR ", "UMAR RABIYAH", "SALUHU SULEIMAN MUHAMMED", "UMAR MUHAMMAD SADAUKI", "OWOICHO INALEGWU SOLOMON", "HUSSAINI FARIDAH OYIEZA", "SUNDAY DANIEL ", "ALKALI ABIMIKU AKPOMOSHI", "JOSEPH MFON RUTH", "EDOR EYARE BLESSING", "IBRAHIM UMAR", "TAR PHILIP ORJIME", "XXX", "KACHALA ANGELA EWA", "ADAMU MARYAM", "YAKUBU OJOMAH RASHIDA", "IORUMBUR ANGEL TERKUMBUR", "IDOKO IRENE OLUCHUKWU", "ADEGOKE QUEEN BISOLA", "TIJANI ABDULMUMIN MARYAM", "NWODO ONYEDIKA DANIEL", "ALFRED EKOSA JOY", "AYOGU CHIDIEBERE BENARD", "IDRIS ISMAIL UMMULKULSUM", "ADUKWU ENE JANET", "MUHAMMED AGBO FATIMA", "MAIKEFFI JOB ", "SAMUEL SHEKWOYEMILO CHRISTIANA", "SAMUEL OSHULEYI", "KYAUTA BOAZ CHONGFI", "QASIM ALIYU ABUBAKAR", "ABANKWA MARIA ORESI", "ABDULLAHI AMINAT OSHEIZA", "IBRAHIM OMEIZA HUSSEIN", "AHMED OMAYIOZA SHERIFAT", "XXX", "AKANKANEE GIFT GODWIN", "ADOGA ATAMPA ATIKU", "ABDULLAHI ADINOYI ABDULBAKI", "GABRIEL ADEOLA REBECCA", "ELKANAH KADALAH JOSHUA", "ISHAQ YAHAYA", "MBASEN SARAH ANGULA", "YUSUF HALIMA", "JAMES MBACHA ", "KANTIOK FELIX ", "BRIGHT OGBENETEGA LUCKY", "PATRICK ORUW MIRACLE", "NICODEMUS RITA ", "ABDULWAHEED ASIYAT ", "MUSA RAFAT ALABA", "JATTO ADEIZA JAMIU", "YAKUBU OLAGOKE ABDULAZEEM", "ADANU PAUL ", "HASSAN KUZHIAGYE GAZA", "HARUNA MUHAMMED NASIRU", "VINCENT EXCELLENT ", "UGWU NMESOMA JESSICA", "JAMES GODWIN", "WILLIAMS LEBO MICHAEL", "OKWOR MARY-CYNTHIA CHIAMAKA", "JOSEPH MINTON HAPPINESS", "EDEANI KOSISOCHUKWU FAVOUR", "ASUVA ADEIZA KASHIM", "NASIRU MULIKAT", "JACOB MARY ", "SOKOKAYE KINSOKO WISDOM", "NWAFOR FAITH CHIDIMMA", "USMAN CHENEMI MERCY", "JEFF FAVOUR DIVINE", "NKWAZEMA IFESINACHI EDWARD", "YAKUBU HUSSEINA", "AKPENNONGON EMMANUEL ORSEER", "YAKUBU JULIUS PIUS", "SODIQ ADAVIZE MAHMUD", "OTACHE HASSAN SULEIMAN", "ABUH HAUWAKULU SANI", "DAVID FAVOUR ", "ABANG NJONG FAVOUR", "SAMUEL SABASTINE MAGODE", "SIMON BEGNASHEBWANA ELISHA", "SULEIMAN KHADIJAT OMOTOLA", "IORWASHIMA MNGUSUUR WINIFRED", "MUHAMMED ABIGE ABDULMALIK", "TOOR FAVOUR MNGUSUUR", "SYLVANUS NENDRIMWA JUDITH", "PATRICK GRACE WANDOO", "KWAV MEMBER GRACE", "EDWARD FRANCIS OWOGOGA", "EMMANUEL FAVOUR AKOCHE", "YONGBA MATTHEW AONDOUNGWA", "OCHI NGBEDE INNOCENT", "EMMANUEL LAMOSI TESTIMONY", "THOMAS JOEL NPEKNOM", ]
print(len(names))

len_name_list = []
for a in names:
    len_name_list.append(len(a))

uniq_len_name = list(set(len_name_list))
print(f'{uniq_len_name[-1]} characters is the Longest name on the list...')


# Add trailing spaces...
for a in names:
    print(a.ljust(uniq_len_name[-1]+5, ' '))

Happy coding!

Generating all possible two letter strings to scrape web data

 Recently, I encountered a situation where I have to generate all possible two letter combinations that makes up web urls I had to scrape.

Here you find the python code that does exactly that:-

# Generate all possible two letter strings
from itertools import product
from string import ascii_lowercase

keywords = [''.join(i) for i in product(ascii_lowercase, repeat = 2)]
len(keywords)

My use case what the generate URLs like this:-

url_list = []
for kwd in keywords:
    for i in range(1, 11):
        main_url = f'https://www.fiverr.com/search/users?query={kwd}&page={i}'
        print(main_url)
        url_list.append(main_url)

That is it!

Wrangling and Mapping ‘list of colleges teaching MBBS in India’ with Python

 In this post, I will demonstrate how to wrangle and map the ‘list of colleges teaching MBBS in India’. The list is available on this webpage.

First I will collected the dataset, clean it into a GIS friendly format, geocode the colleges, then map it on the map of India sourced from GADM. This is a project you could 100% complete without coding using tools like QGIS or ArcGIS, but here I will use python code to do it from scratch.

Data collection

Lets collect the list of data from the web page into CSV file. There are ways to do this in python like using requests module, selenium module, beautifulsoup module, scrapy module etc.

In this situation, I will copy the html element the represent the table into a local file, then extract the table from the local CSV spreadsheet using python.

Using pandas library, with few lines of code we got the list into a CSV file as seen below;-

mbbs_df = pd.read_html(r"mbbs.html")
mbbs_df[0].to_csv(r"mbbs.csv", index=False)
mbbs_df = mbbs_df[0]

mbbs_df