# data[].attributes.body
he following tasks are carried out by our team members:

# data[].attributes.body
CLEAN 1: Change the date format of [Lease Commencement Date]
ACTION: Change to 'MMM yy' format
REASON: The date was changed from 'MMM yy' to 'dd/mm/yyyy' after blending. In order for the data to be accurate and for easier reading, the date format will be changed to 'MMM yyyy' format.

CLEAN 2: Missing value in  [Lease Commencement Date]
ACTION : Change to "Jun-2019"
REASON: Rental prices generally increases over the months of the year. Hence, updating the missing data to 'Jun-2019' would statistically align the record to median position.

# data[].attributes.body
TRANFORM: Summarize Sales Data
ACTION:: Summarize data with 'Avg Sales Price' and 'Avg Monthly Gross Rent'
REASON: To display the average sales price & average monthly gross rent by project by unit size (area)

# data[].attributes.body
CLEAN: Clean column [No. Of Bedrooms]
ACTION: Delete rows with 'na'
REASON:  Data without no. of bedroom is not meaningful.

# data[].attributes.body
Presenter: Bala
1) URA datasets:
- Website allows only 5 districts to be downloaded at one time
- The URA dataset is generally quite clean
2) Synchronizing of tasks among team members
3) Use of conditional statements needed to convert Area to Area Range:
CASE
WHEN X AND Y THEN A
ELSE B
END

# data[].attributes.body
Presenter: Lester / Francis
SALES_FINAL

# data[].attributes.body
CLEAN: Change Date Format of column [Date Of Sale]
ACTION: Formatting change to MMM yyyy for all date Of Sale (column)
REASON:  All transactions under Date Of Sale (column) will be standardized showing 1st day of the month eg Dec 2009, Jan 2010 instead of different type of date format that might caused confusion.

# data[].attributes.body
TRANSFORM : Add new column [Area Range (sqft)]
ACTION : Convert Area to Area Range (same as Rental Main)
REASON : The size of the property in the rental data is showing the Area range while the sales data is in a precise area. To have consistent data that is easy for comparison, we convert the Sales data to area range.

# data[].attributes.body
BLEND : Join Sales data & District data
ACTION : Left Outer Join (add column)
REASON : There are 28 districts in Singapore, and each district offers a different living experience.  Instead of the Districts number, the District name itself would be a better reference for both the property agent and the investor.

# data[].attributes.body
CLEAN: Clean column [No Of Units]
ACTION : Delete transactions with multiple units (>1)
REASON: Transactions with multiple units will result in wrong decision making

# data[].attributes.body
BLEND :  Stack Sales datasets 1 - 6 using Union
ACTION : Union sales data of all districts
REASON: The sales data from  URA, 28 districts, are split into 6 files based on Singapore District Code. Thus  combine sales data for all districts together  will provide clearer image for investor on making  decision.

# data[].attributes.body
TRANSFORM: Summarized Rental Data by using cross table
ACTION: Visualization type - Cross Table, export data to file and proceed saving data as Rental_Main.txt
REASON: Transforming the required data using cross table visualization able us to obtain the average gross rent for the individual floor area in the project / building

# data[].attributes.body
BLEND : Join Sales_Main with Rental_Main
ACTION : Using 'Left Outer Join' to add  column [Avg Monthly Gross Rent]  to Sales_Main. Matched [Project Name] to [Building/Project Name] and matched [Area Range (Sqft)] to [Floor Area (Sqft)].
REASON: Require data of value  [Avg Monthly Gross Rent]  for later action of cross table to summarize data with 'Ave Sales Price' and 'Avg Monthly Gross Rent'.

# data[].attributes.body
Presenter: Sunicha

# data[].attributes.body
Presenter: Rachel

# data[].attributes.body
BLEND: Combine the 6 rental datasets into a complete rental datasets (districts 1 - 28)
ACTION: Use Union to stack the 6 rental datasets
REASON: The raw rental data from URA are split into 6 files based on Singapore District Code. We need to combine the rental data to form a complete rental dataset with 28 districts.

# data[].attributes.body
CLEAN: Clean column [Project Name].
REASON: Column [Project Name] contains generic description 'Non-Landed Housing Development' instead of actual project name.
ACTION: Filter and delete 'Non-Landed Housing Development' data records  (total 1944 rows)
REASON: Data without project name is not useful for analysis

# data[].attributes.body
Presenter: Francis

# data[].attributes.body
Presenter: Farlina

# data[].attributes.body
Presenter: Bala
The following tasks are carried out by our team members:

# data[].attributes.body
Presenter: Lynn
RENTAL_MAIN

# data[].attributes.body
Presenter: Sunicha
In this project, we will use the 3 datasets obtained from the URA website:

1. Rental data
2. Sales data
3. District data

Source: https://www.ura.gov.sg/realEstateIIWeb/transaction/search.action
https://www.ura.gov.sg/realEstateIIWeb/resources/misc/list_of_postal_districts.htm

# data[].attributes.body
Presenter: Francis
Real Estate Data For Singapore

For a property investor, it is important to know the sales price as well as the potential rental income generating from a property. URA website provides both sales and rental information. However, they are presented in separate reports. In this project, we will prepare the 3 datasets from URA and attempt to present both the average sales price and average gross rent of the properties in a summarized report.

The scope of the project will cover:

Non-landed private properties

Sales & rental in Year 2019

Single unit sales