Data Integration and Mashup Application for Clinic Location Search

This report details the development of a Mashup application that integrates data from various sources to enable users to search for clinics based on their location. The project involves data cleaning, merging, and the creation of a RESTful web service to provide clinic information in JSON format. The Mashup application utilizes this service to display clinic locations on a map and allows users to send inquiries to clinics.

EXECUTIVE SUMMARY
This report will lay the emphasis over the various programming model that could be used in
order to achieve the data integration into one single data. This report will help in making the
Python module that is going to clean the input data first and then help in making the incoming
data cleaner from the unwanted data. That cleaned data is going to be merged with another
data and then that data is fetched by the RESTful web server with the help of the bottle
framework. The JSON data that comes from the response of the request entered is going to be
fetched by the Mash up application that will create a blip on the Map which is going to show the
location of the clinic with the demographic location.

Table of Contents
EXECUTIVE SUMMARY......................................................................................................................2
INTRODUCTION................................................................................................................................4
KEY SYSTEM CONCEPTS....................................................................................................................5
Data Cleaning and Merging..........................................................................................................5
RESTful Web Service Implementation........................................................................................10
Mashups.....................................................................................................................................13
Demo Running Instructions............................................................................................................14
Perquisites:.................................................................................................................................14
Data Cleaning Process................................................................................................................14
RESTful web Service initiation....................................................................................................14
Implementation of Mashup web service...................................................................................16
CONCLUSION..................................................................................................................................17
List of Figures
Figure 1: Clinics.csv File....................................................................................................................5
Figure 2: Cleaned Data from the Clinics.csv file...............................................................................6
Figure 3: cliniclocations.xml file.......................................................................................................7
Figure 4: XML to CSV conversion code.............................................................................................8
Figure 5:Code for email cleaning......................................................................................................9
Figure 6: Code for RESTful web server...........................................................................................11
Figure 7: code for RESTful web service..........................................................................................12
Figure 8: Run the data_merger.py file...........................................................................................14
Figure 9: RESTful Web Service........................................................................................................14
Figure 10: Index page of the web Service......................................................................................14
Figure 11: JSON data from the request..........................................................................................15
Figure 12: Open clinic_map.html...................................................................................................16
Figure 13: Home Page....................................................................................................................16

INTRODUCTION
The main purpose of this report is to elaborate the Software that is being made. The Mashup
Software that is made is going to be focused on data integrating. There are different types of
files that have be cleaned and then merged in order to create one single file. The file clinics.csv
file contains the full information about the clinics like the Name, Postcode, Suburb and Latitude
and Longitude. The Clinics.csv file will also contain the email in the incomplete format that
needs to be cleaned. That is going to be thoroughly defined by later. The data cleaning is done
that is going to help in making the later process easier. Another file is the cliniclocations.xml is
the xml file that is going to contain the Latitude and Longitude of the Clinics along with the
clinicid that is processed. The xml is converted into the csv data using xml library function.
There are two more file that are also needed for the final merging. Those files are services.csv
file, it is a comma separated file that embeds the list of all the services provided by the clinics
and the last file contains the clinicservices.csv that contains data for all the clinics along with the
offered services. These files are later combined as one with the name
“clinicservicelocations.csv”. All the data is sorted against the ClinicID.
In the next part that is the RESTful web service. It is going to create a web service server that
runs on the Bottle Framework. The server is going to take the input as /getclinics?serviceid=x,
where x is the clinicid that is going to be entered by the user. As a response to that request the
web server is going to respond with the JSON data that is later used by the Mashup demo in the
task 3.
In third task, a Mashup service that used the JSON data from the second task and the JSON data
is going to be fetched from the web server. Where ever the user is going to enter the service id
along with the postcode the map is going to fetch the JSON data from the previous task and
then show the blip on the map with the demographic location and if the user clicks on the blip
then a drafted mail is sent to the clinic along with the subject “Clinic Enquiry”. This project is
going to use the petl module in order to extract the data from the csv file and then use the XML
file to convert the XML data into the CSV data and then merging it.
This is achieved by the using HTML, CSS, JS, and Python.

KEY SYSTEM CONCEPTS
This report is solely focus on making a Mashup software that is will help in making the final
system project that is made up by various small tasks.
Data Cleaning and Merging
Each time we are preparing any information, the information that arises could be of various
organizations like Tab-delimited content (.TXT) and comma-delimited qualities (.CSV) design.
The preparing is ready to consolidate or coordinate the comparable sort of information into
one. The date could be from comparative source, comparable structure or comparative
compose.
Figure 1: Clinics.csv File

Cleaning of data: The information that arose is for the most part in crude configuration or have
omitted qualities. To deal with those missing information a cleaning procedure is completed in
which the omitted qualities are taken care of and the information is made significant. Data
cleaning is done by the use
Figure 2: Cleaned Data from the Clinics.csv file
In this area, we will centre around the scrubbing part and after that consolidate everything. This
is finished by utilizing Python. So, two documents are there:
 Clinics.csv: It consist of every information about the facilities like; Suburb, state, Email as
metadata, Name, Postcode. This record contains entire information that should be prepared
keeping in mind the end goal to accomplish the coveted outcome that is combining the
information of Clinics.csv as with the information from the locations.xml.
 cliniclocations.xml: This file consists the details about the location of the clinic. This file is going
to contain latitude and longitude of the clinics with the clinicid. This xml file data is later
converted into the csv file.

Figure 3: cliniclocations.xml file

Code for the xml to csv conversion:
Figure 4: XML to CSV conversion code

Code for the csv file cleaning:
Figure 5:Code for email cleaning

RESTful Web Service Implementation
It is a Web Service design which depends on Virtual Hierarchical organizer structure. This Web
Server acknowledges the variable from the client and demonstrates the outcome in like
manner.
http://localhost:8080/ getclinics?serviced=x
•/getclinics = this is the capacity in python code which is called each time the customer
composes this in the URL.
•/serviced=4 = this is the parameter inside the capacity which is put away the postcode entered
by the client and after that gets contrasted and the postcode segment of the
clinicservicelocation.csv document. 4 is the service id of Psychiatry and it is properties or
qualities are shown in the JSON reaction. For this web server, I made a python document named
clinic_locator.py which will conjure the RESTful web server when the client enters the
connection composed previously.
Composed web administration will keep running on any coveted web program. For beginning
the RESTful web benefit which will be begun when the python content record named
"clinic_locator.py" is keep running on Command Prompt contingent on the necessities.

import petl as etl
from bottle import route, request, response, run
import sys
CSVFileLocation = etl.fromcsv("clinicservicelocation.csv")
@route('/')
def index():
return "<h1 style='color:#000000; text-alignment:center; font-size:20xp;'>This is a RESTful
Web Service that is created via Bottle Framework. Website Instruction 101</p> \
<p style='color:#555555; text-alignment:justify; font-size:10xp;'>To check the service
from the web Server first run Data_Merger.py file\
<ul> \
<li style='color:RED'>Please Enter the Below link in order to run
the web Server.</li>\
<li
style='color:BLUE'>http://localhost:8080/getclinics/ServiceID=xxxx</li> \
</ul>\
where x is the serviceID of the Clinic."
@route('/getclinics')
def getclinics():
SetofHeaderValue()
ServiceID = request.query.postcode
RowValue = etl.select(CSVFileLocation, "{Postcode} == '" + ServiceID + "'")
#if 2 records are not returned it didn't find a result
if (RowValue.len() != 2):
Figure 6: Code for RESTful web server

@route("/getclinics/ServiceID")
def getclinics():
if 'ServiceID' not in request.query:
response.status = 400
return { "": False, "Error" : "'ServiceID' Parameter is missing..." }
def SetofHeaderValue():
response.headers['Access-Control-Allow-Origin'] = '*'
response.headers['Access-Control-Allow-Methods'] = 'PUT, GET, POST, DELETE, OPTIONS'
response.headers['Content-type'] = '*'
run(host='localhost', port=8080, debug=True)
Figure 7: code for RESTful web service

Mashups
Mashups is defined as a cross breed application produced from at least two sources like
network application or site pages. Mashups takes use of those applications for making the
solitary graphical interface or the solitary application. So, in this task of project:
•The RESTful Web Service is created which takes contribution from the yield of the code of
python composed on the framework of handling information.
•The output from RESTful Web Service is sustained as a contribution to the application of
Mashup as JSON information and further handling is finished.
In this, the HTML document is made with the assistance of JavaScript and CSS without any
preparation. The record made in this procedure is clinic_map.html. Therefore, the main
objective of the above Mashup is:
•Already created HTML site pages will take include from client as postcode.
•Also, the HTML page extract the information from the network of RESTful web benefit made in
the past advance in the frame of JSON and will show the area on Google outline.
For demonstrating the Google Maps are used that uses Google API is taken. From the API, I
could demonstrate the area of the centre whose postcode has been entered in the option of
search bar.
Mashup Sources:
Two sources are there of this Mashup:
 The RESTful Web Service is achieved in the past advance with a specific end goal to begin the
Web Service.
 The cliniclocationservice.csv document which is made by me in the initial phase after cleaning of
data cleaning and merging of data procedures on the cliniclocations.xml and Clinics.csv records.

Demo Running Instructions
The implementation of this project is done in three tasks
 Data Cleaning and Merging
 Making RESTful Web Service
 Making a Mashup from the previous steps
Perquisites:
 Pandas for dataframe
 Bottle framework
 Petl
Data Cleaning Process
Open Command Prompt, go to the location of the file data_merger.py using cd
Figure 8: Run the data_merger.py file
RESTful web Service initiation
For this again open command prompt and go to the file location and then run clinic_locator.py
file using the python command.
Figure 9: RESTful Web Service
Goto default web browser and enter
 http://localhost:8080/
on the browser search bar. It will initiate the index page of the service.
Figure 10: Index page of the web Service

Figure 11: JSON data from the request

Implementation of Mashup web service
At first with the second step running open the clinic_map.html file. It will open in the default
web browser.
Figure 12: Open clinic_map.html
The web browser is going to show the home page.
Figure 13: Home Page
The user needs to enter the Postcode and choose the medical service from the drop down.

CONCLUSION
In the nutshell, this assessment report focuses on creating a Mashup application using which
patients can find the location of the desired clinic using the recognized postcode of that specific
clinic. In the csv the postcode will search and match up with the location if the location is
displayed it will be shown if it is not there the default location will be display. To achieve this,
several things are done. At first, the data that is specified is prepared for missing values and
then merged with the different file types. The output is then stored in a file which is used by
both clinic_map.html and clinic_locator.py as an input file. This step is considered as an
important step as it will alter entire result. In the next step, net server is designed using Bottle
framework. The cliniclocationservice.csv file is taken as input and returns JSON data which is
laterly used by clinic_map.html to search and display locations. In the final step, a Mashup
application is developed using above two processes. Using the files created above as input this
Mashup application displays the location of the clinic by retrieving postcode from the user. Also,
an Enquiry button is given which will help the user to send a mail with an already filled subject
line "Clinic Enquiry” in it.