Difference between revisions of "Kabak: Research Paper Data Preparation"

From Visual Analytics for Business Intelligence
Jump to navigation Jump to search
Line 105: Line 105:
 
||
 
||
 
[[File: Kabakdatacleaning3.png|400px|center]]
 
[[File: Kabakdatacleaning3.png|400px|center]]
 +
|-
 +
|
 +
* Geocoding of postal codes with missing latitudes and longitude via
 +
https://developers.google.com/maps/documentation/geocoding/intro
 +
**Public housing data: 223 missing data
 +
**Private housing data: 338 missing data
 +
||
 +
[[File: GEOCODING.PNG|400px|center]]
 
|}
 
|}
 
<br/>
 
<br/>

Revision as of 12:06, 22 November 2016


OVERVIEW

DATA PREPARATION

ANALYSIS

PROJECT MANAGEMENT


Initial Dataset

DATASET DESCRIPTION DATA USED

Average Monthly Household Electricity Consumption
by Postal Code (Public Housing) & Dwelling Type, 1H & 2H 2015

Link (1H): https://www.ema.gov.sg/cmsmedia/Publications_and_Statistics/Statistics/23RSU.xls

Link (2H): https://www.ema.gov.sg/cmsmedia/Publications_and_Statistics/Statistics/25RSU.xls

  • Average monthly household electricity consumption (kwh)
    • By month
    • By postal code
    • By public housing type
  • Total Average household electricity consumption (kwh)
    • By postal code
    • By public housing type
  • 9379 rows of raw data X 12 sheets = 112,548 rows of raw data

Average Monthly Household Electricity Consumption by Postal Code (Private Apartments), 2015

Link: https://www.ema.gov.sg/cmsmedia/Publications_and_Statistics/Statistics/2RSU.xls

  • Average monthly household electricity consumption (kwh)
    • By month
    • By postal code
  • Total Average household electricity consumption (kwh)
    • By postal code
  • 9911 rows of raw data

Basic Demographics Characteristics (2015)

Link: http://www.singstat.gov.sg/docs/default-source/default-document-library/publications/publications_and_papers/GHS/ghs2015/excel/t7-9.xls

  • Resident Population by Planning Area/Subzone
    • By age group
    • By sex
    • By ethnicity
    • By type of dwelling
  • T7 Age group
    • 378 rows of raw data
  • T8 Ethnicity
    • 378 rows of raw data


Data Cleaning

METHOD DESCRIPTION
  • Stack data to consolidate data table in to 2 columns (Postal Code, Housing Type)
  • Remove rows with missing data
Kabakdatacleaning1.png
  • Concatenate all 12 months data into one consolidated data table
    • By the end of this phase of data cleaning, we have a total of 177,053 rows
Kabakdatacleaning2.png
|-
  • Merging Private Housing Data with Public Housing Data
    • Final consolidated data consist of 241,766 rows
Kabakdatacleaning3.png
  • Geocoding of postal codes with missing latitudes and longitude via

https://developers.google.com/maps/documentation/geocoding/intro

    • Public housing data: 223 missing data
    • Private housing data: 338 missing data
GEOCODING.PNG