ICT705 Assignment 2: Data Cleaning, Web Services, and Mashup Project

This assignment solution for ICT705, focusing on Data and System Integration, details a project involving data cleaning, transformation, and merging using the PETL library. The project then creates a RESTful web service using the Bottle framework to provide data via HTTP GET requests. The solution further implements an AJAX mashup with HTML and a web service API to visualize office and service data on Google Maps. The data cleaning process involved formatting phone numbers, while data merging utilized PETL's join function. The RESTful service handles requests for offices and services, returning data in JSON format. The AJAX implementation dynamically loads data from the web service and plots office locations on a map. The solution includes running instructions and a bibliography. The project demonstrates a comprehensive understanding of data integration techniques, web service creation, and front-end mashup development.

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