ICT705 Data and System Integration

This assignment is done as per my course work for subject ICT705. It covers concepts like data cleaning and merging, RESTful web services, and AJAX. The assignment includes code examples and instructions on how to run the program.

ICT705
Assignment 2
[DATE]
[Company name]
[Company address]

ICT705 Data and System Integration
Contents
Executive Summary...................................................................................................................................2
Introduction.................................................................................................................................................2
Concepts.....................................................................................................................................................2
Data cleaning and merging...................................................................................................................2
RESTful web services...........................................................................................................................5
AJAX........................................................................................................................................................8
Running Instruction..................................................................................................................................13
Bibliography..............................................................................................................................................13
Conclusion................................................................................................................................................13
Appendix...................................................................................................................................................13
1

ICT705 Data and System Integration
Executive Summary
The assignment is done as per my course work for subject ICT705. The requirements
accomplished in the assignment are:
 Part 1: Data loading, data transformation and data merging was done using PETL.
 Part 2: A web service was created using Bottle, querying the database using PETL to
provide data on HTTP GET requests.
 Part 3: Mashup was done between HTML, AJAX and web service API to plot the offices
and services data on google maps.
Introduction
The mashup application program is built to demonstrate the ability to use the python and
supportive library PETL and Bottle framework. The application performs the task of cleaning the
data from the raw csv file and transforms it into specific file which is optimally for querying and
servicing user’s requests. It also provides API to access data on HTTP request and finally an
implement to plot data on google maps. The application using python, PETL and Bottle is built to
provide office location to the users based on the services they are looking for.
Concepts
The three concepts of building this application are data cleaning and merging, web API and final
using AJAX to send requests to the server.
Data cleaning and merging
Data cleaning is defined as the activity of cleaning the data with respect to a specified format.
The cleaned result in faster query retrieval and better system performance. The data cleaning
process consist of removing unwanted data like null values, garbage value and incorrect values.
The data merging process is defined as the activity of combining data from multiple file of
table, this is usually done with the help of keys, which help in matching the columns from the
tables that are to be merged.
data_merger.py
import petl as etl
office_data=etl.fromcsv("offices.csv")
#to check the file data
2

ICT705 Data and System Integration
print(etl.head(office_data,5))
print(etl.tail(office_data,5))
phone_data=[["phone","officeid"]]
#cleaning phone numbers using given state code
for rid in range(1,len(office_data)):
state=str(office_data[rid][3])
phone=str(office_data[rid][5])
phone=phone.strip()
regcode=0
if(state.upper()=="NSW"):
regcode=2
elif(state.upper()=="VIC"):
regcode=3
elif(state.upper()=="QLD"):
regcode=7
elif(state.upper()=="SA"):
regcode=9
phone_formatted="+61 "+str(regcode)
phone=phone.replace(" ","")
phone=phone[len(phone)-8:len(phone)]
phone_formatted=phone_formatted+" "+phone[0:4]+"
"+phone[4:8]
print(phone_formatted)
phone_data.append((phone_formatted,office_data[rid][0]))
print(phone_data)
#combining phone numbers cleaned data with other field
3

ICT705 Data and System Integration
office_data=etl.cut(office_data,"OfficeID","Contact
Name","Suburb","State","Postcode","Email")
office_cleaned_data=etl.join(office_data,phone_data,lkey="Of
ficeID", rkey="officeid")
print(etl.head(office_cleaned_data,5))
print(etl.tail(office_cleaned_data,5))
#loading from xml office locations data
office_location_data=etl.fromxml('Office_Locations.xml','off
ice',{'OfficeID':'officeID','Lon':'Lon','Lat':'Lat'})
office_services_data= etl.fromcsv('Office_Services.csv')
#loading from xml services data
services_data=etl.fromxml('Services.xml','service',
{'ServiceID':'ServiceID','ServiceName':'ServiceName'})
#merging office data with location data
office_service_locations=etl.join(office_cleaned_data,office
_location_data, lkey="OfficeID", rkey="OfficeID")
#merging office locations data with service office link data
office_service_locations=etl.join(office_service_locations,o
ffice_services_data, lkey="OfficeID", rkey="OfficeID")
#merging office location and service link data with services
data
office_service_locations=etl.join(office_service_locations,s
ervices_data, lkey="ServiceID", rkey="ServiceID")
#removing the unwanted field from the data
office_service_locations_final=etl.cut(office_service_locati
ons,"OfficeServiceID","ServiceID","ServiceName","OfficeID","
Contact Name","Suburb","phone","Email","Lat","Lon")
#saving data into csv file
etl.tocsv(office_service_locations_final,'Office_Service_Loc
ations.csv')
4

ICT705 Data and System Integration
print(etl.head(office_service_locations_final,5))
Explanation
As per the requirement the phone number field required cleaning and formatting for this, I have
used PETL library. To make the phone numbers in proper format the state and the assigned
code with them were used. The merging of the data was also done by the PETL join function
(Join Tables, n.d.), which work by coming the table based on the key columns values from the
left and the right table. To remove the unwanted field cut function of PETL was used.
RESTful web services
REST stand for “Representation State transfer”. The goal of the RESTful web service is to allow
web resource manipulation and operation like CRUD operation using HTTP methods such as
GET, POST, PUT, DELETE etc.
office_locator.py
import petl as etl
from bottle import route, request, get, response ,
run ,error
# to get data from files
data = etl.fromcsv("Office_Service_Locations.csv")
service_data=etl.fromxml("Services.xml","service",
{"ServiceID":"ServiceID","ServiceName":"ServiceName"})
# to process http get request for offices
@get('/getoffices')
def get_offices():
response.headers['Access-Control-Allow-Origin'] = '*'
service_id = int(request.query.serviceid)
#print("Service Id:",service_id)
#list to store office data
5

ICT705 Data and System Integration
offices_to_send=[]
if service_id==0:
for oid,sub,sn,cn,ph,email,lat,lon in
etl.values(data, "OfficeID","Suburb","ServiceName","Contact
Name","phone","Email","Lat","Lon"):
offices_to_send.append({
"OfficeID":oid,
"Suburb":sub,
"ServiceName":sn,
"ContactName":cn,
"Phone":ph,
"Email":email,
"Lat":lat,
"Lon":lon
})
# to return json data object
return { "offices": offices_to_send}
# to get data record on for given service id
selected_data=etl.select(data,
"{ServiceID}=='"+str(service_id)+ "'")
for oid,sub,sn,cn,ph,email,lat,lon in
etl.values(selected_data,
"OfficeID","Suburb","ServiceName","Contact
Name","phone","Email","Lat","Lon"):
offices_to_send.append({
6

ICT705 Data and System Integration
"OfficeID":oid,
"Suburb":sub,
"ServiceName":sn,
"ContactName":cn,
"Phone":ph,
"Email":email,
"Lat":lat,
"Lon":lon
})
# to return json data object
return { "offices": offices_to_send}
# to process http get services request
@get('/getservices')
def get_services():
response.headers['Access-Control-Allow-Origin'] = '*'
services_to_send=[]
for sid, sname in
etl.values(service_data,"ServiceID","ServiceName"):
services_to_send.append({
"ServiceID":sid,
"Name":sname
})
return { "services": services_to_send}
# error handling for http codes
@error(404)
def error404(error):
7

ICT705 Data and System Integration
return "<b>Invalid url error</b>"
@error(403)
def error403(error):
return "<b>Forbidden access error</b>"
@error(500)
def error500(error):
return "<b>Internal server error</b>"
run(host='localhost', port=5000, debug=True)
Explanation
As per the assignment specification the bottle framework is used to create the RESTful service
that responds to the HTTP GET request. The two requests that the server reposed to are
getoffices and getservices. For the getservices request the server load the data from service.xml
file create a dictionary object with service id and service name and return it as a JSON object.
For the getoffices the server will query the csv file and return the JSON object based on the
service id passed, the JSON object contain office id, suburb, service name, phone, contact name,
email, latitude of the office location and longitude of the office location. In case of invalid service
request errors will be shown (Request Routes, n.d.).
AJAX
AJAX is a functionality implemented using JavaScript, which allows use to send request the XML
HTTP request to server. With the help of ajax we can dynamically load page content, without
the need of page reload.
office_map.html JavaScript code
function loadServiceList() {
$.ajax( {
8

ICT705 Data and System Integration
url: "http://localhost:5000/getservices",
method: "get",
dataType: "json"
})
.done( function(data) {
console.log(data)
var select_list=
document.getElementById('service_list');
var service
var service_values =data.services
for (var i=0;i<service_values.length;i++) {
$('#service_list')
.append($('<option>', { value :
service_values[i]["ServiceID"] })
.text(service_values[i]["Name"]));
}
});
}
function initMap() {
var service_id=
document.getElementById('service_list').value;
$.ajax( {
url: "http://localhost:5000/getoffices",
9

ICT705 Data and System Integration
method: "get",
dataType: "json",
data:{serviceid:service_id}
})
.done( function(data) {
console.log(data)
var info=[];
var office_data =data.offices
var map;
for (var i=0;i<office_data.length-1;i++) {
if(i==0){
var
mplon=parseFloat(office_data[i]["Lon"]);
var
mplat=parseFloat(office_data[i]["Lat"]);
console.log(mplon);
map = new
google.maps.Map(document.getElementById('map'), {
center: {lat: mplat ,
lng: mplon },
zoom: 12
});
10

ICT705 Data and System Integration
}
info[i]= "Office Id:"+(office_data[i]
["OfficeID"])+
"<br>Contact:"+ office_data[i]
["ContactName"] +
"<br><a href='mailto:"+office_data[i]
["Email"]+"?Subject=Office%20Enquiry'>Send Mail</a>"+
"<br>Phone number:"+office_data[i]
["Phone"]+
"<br>Suburb:"+ office_data[i]
["Suburb"] +
"<br>Service:"+ office_data[i]
["ServiceName"];
var marker = new
google.maps.Marker({
map: map, title: 'M' ,
position: {lat: parseFloat(office_data[i]["Lat"]), lng:
parseFloat(office_data[i]["Lon"])}
});
map.setCenter(marker.getPosition())
var infoWindow = new
google.maps.InfoWindow(), marker, i;
11

ICT705 Data and System Integration
google.maps.event.addListener(marker, 'click',
(function(marker, i) {
return function() {
infoWindow.setContent(info[i]);
infoWindow.open(map,
marker);
}
})(marker, i));
}
});
}
Explanation
The ajax is used to send request and then parse the returned data object to load data on the
web page. The select menu option is loaded based on the data returned from the getservice
request. The office markers are plot on the map with the info windows based on the getoffices
request returned data (CodexWorld, n.d.).
12

ICT705 Data and System Integration
Running Instruction
1. The system must have active internet connection
2. The PETL Package must have installed using pip or any other means
3. The Bottle framework must be installed using pip or any other means
4. Execute the python data_merger.py script, this script with do the data cleaning and
merging operation and proceed an Office_Service_Locations.csv file.
5. Start the Bottle server by executing the python office_locator.py script. The server will
run on the port 5000 and server GET request with specified url and query parameters.
6. Open the office_map.html page in any standard browser.
7. Select the service for which you want to find the offices.
8. Press the locate services button.
9. The google map will load the marker based on the data received from the server.
Bibliography
CodexWorld. (n.d.). Google Maps with Multiple Markers and Info Windows using JavaScript API V3.
Retrieved from codexworld: https://www.codexworld.com/google-maps-with-multiple-markers-
using-javascript-api/
Join Tables. (n.d.). Retrieved from http://petl.readthedocs.org/ .
Request Routes. (n.d.). Retrieved from http://bottlepy.org/doc.
Conclusion
This was challenging assignment, I face major difficulty with the data cleaning and plotting on
the google maps part, but I was able to overcome it. This assignment has taught me a great deal
of what python and its supportive libraries are capable of, apart from this I have also learnt a lot
regarding data cleaning and merging and how linking of columns works based on similar values.
Appendix
Data_merge.py generated csv file
13

ICT705 Data and System Integration
office_locator.py Running Bottle Server and responding to requests.
office_map.html select menu
14

ICT705 Data and System Integration
office_map.html map with markers
15

ICT705 Data and System Integration
16