Lab 9 Prep

Preparation of data

Here, I follow the steps in the lab handout as well as outlined the steps on how I cleaned the given dataset.
Click here to skip to Part 1 (Data Manipulation), Part 2 (Spatial Data), or Part 3 (Spatial Point Patterns) of the lab submission.

First we load the required libraries

library(sf)
library(sp)
library(rgdal)
library(spatstat)
library(ggmap)
library(maptools)
library(tidyverse)
library(lubridate)

Now that the libraries are imported we move on to the dataset
* First, we read in data

tenders<-read_csv("tabula-tender-bids-from-mar-2012-to-jan-2017.csv",col_names=c("centre","stall","area","trade","bid","month"))

Get rid of empty rows using

tenders %>%
  drop_na(centre)

Getting rid of row 1135 (the split between cooked food and ‘locked-up’ stalls) and setting before to ‘cooked’ and after to ‘lockup’

tenders%>%
  drop_na(centre) %>%
  filter(row_number()!=1135) %>%
  mutate(type = if_else(row_number() < 1135, "cooked","lockup"))

Formatting to get columns in the right class
- Remove all $ and , and cast it as a number
- Date to real date
- price and area can calculate price per square meter

tenders <- tenders%>%
  drop_na(centre) %>%
  filter(row_number()!=1135) %>%
  mutate(type = if_else(row_number() < 1135, "cooked","lockup")) %>%
  mutate(bidNum = as.numeric(str_replace_all(bid,pattern="\\$|,", replacement = ""))) %>%
  mutate(date = dmy(paste0("01-",month))) %>%
  mutate (priceM2 = bidNum / area)

Cleaning up dataset

We’ll need to clean up the dataset. Rows 588 to 636 have missing data, possible due to them being incorrectly read. So comparing the data to the NEA pdf, we amend those rows.

#creating a temp store so that we dont mess up if we run these codes again
temp <- tenders
#shifting the bid column to month column
tenders$month[588:636] <- temp$bid[588:636]
#shifting the trade column to the bid column
tenders$bid[588:636] <- temp$trade[588:636]
#shifting the stall column to the area column
tenders$area[588:636] <- as.double(temp$stall[588:636])
#converting bid to bidNum
tenders$bidNum[588:636] <- as.numeric(gsub(",","", substring(temp$trade[588:636],2)))

#fixing the stall numbers
for(i in 588:600){
  tenders$stall[i] <- str_sub(temp$centre[i],-6)
}
for(i in 601:632){
  tenders$stall[i] <- str_sub(temp$centre[i],-5)
}
for(i in 633:636){
  tenders$stall[i] <- str_sub(temp$centre[i],-6)
}

#fixing the center names
tenders$centre[588:597] <-temp$centre[587] #BLK 51 OLD AIRPORT ROAD
tenders$centre[598:600] <-substr(temp$centre[598],1,nchar(temp$centre[598])-7) #BLK 527 ANG MO KIO AVENUE 10
tenders$centre[601:629] <-substr(temp$centre[601],1,nchar(temp$centre[601])-6) #BLK 531A UPPER CROSS STREET
tenders$centre[630:636] <-temp$centre[637] #BLK 6 JALAN BUKIT MERAH

#adding in the trades
tenders$trade[588:636] <-c("HALAL COOKED FOOD","INDIAN CUISINE","COOKED FOOD","COOKED FOOD","COOKED FOOD","COOKED FOOD","COOKED FOOD","COOKED FOOD","HALAL COOKED FOOD","DRINKS","HALAL COOKED FOOD","COOKED FOOD","DRINKS","COOKED FOOD","COOKED FOOD", "COOKED FOOD","INDIAN CUISINE","HALAL COOKED FOOD","COOKED FOOD","COOKED FOOD","HALAL COOKED FOOD","INDIAN CUISINE","INDIAN CUISINE","INDIAN CUISINE","HALAL COOKED FOOD","HALAL COOKED FOOD","DRINKS","COOKED FOOD","INDIAN CUISINE","INDIAN CUISINE","INDIAN CUISINE","INDIAN CUISINE","HALAL COOKED FOOD","COOKED FOOD","HALAL COOKED FOOD","HALAL COOKED FOOD","HALAL COOKED FOOD","COOKED FOOD","HALAL COOKED FOOD","INDIAN CUISINE","HALAL COOKED FOOD","HALAL COOKED FOOD","COOKED FOOD","HALAL COOKED FOOD","COOKED FOOD","COOKED FOOD","COOKED FOOD","COOKED FOOD","COOKED FOOD")

#adding in dates
for(i in 588:636){
  mon<-substring(temp$bid[i],1,3)
  year<-str_sub(temp$bid[i],-4)
  if(mon=="Jan"){
    mon<-01
  }
  else if(mon=="Feb"){
    mon<-02
  }
  else if(mon=="Mar"){
    mon<-03
  }
  else if(mon=="Apr"){
    mon<-04
  }
  else if(mon=="May"){
    mon<-05
  }
  else if(mon=="Jun"){
    mon<-06
  }
  else if(mon=="Jul"){
    mon<-07
  }
  else if(mon=="Aug"){
    mon<-08
  }
  else if(mon=="Sep"){
    mon<-09
  }
  else if(mon=="Oct"){
    mon<-10
  }
  else if(mon=="Nov"){
    mon<-11
  }
  else{
    mon<-12
  }
  
  string=paste(year,mon,"01", sep="-")
  tenders$date[i]=as.Date(string)
}

#adding PriceM2
tenders <- tenders %>%
  mutate (priceM2 = bidNum / area)

Next, rows 1971, 1972, 1999, 2004, 2030, 2074, 2112, 2286, 2362, 2390, 2445, 2467, 2468, 2517, 2557, 2568, 2569, 2590, 2670, 2676, 2743 have missing data, so lets fill them in

tenders$stall[1972] <- "01-140"
tenders$area[1972] <- as.double(6.38)

tenders$stall[1973] <- "01-33"
tenders$area[1973] <- as.double(6.27)

tenders$stall[1999] <- "01-102"
tenders$area[1999] <- as.double(6.02)

tenders$stall[2004] <- "01-130"
tenders$area[2004] <- as.double(6.02)

tenders$stall[2030] <- "01-109"
tenders$area[2030] <- as.double(5.50)

tenders$area[2074] <- as.double(5.04)

tenders$stall[2112] <- "01-27"
tenders$area[2112] <- as.double(8.00)

tenders$area[2286] <- as.double(5.56)

tenders$stall[2362] <- "01-35"
tenders$area[2362] <- as.double(6.53)

tenders$area[2390] <- as.double(5.28)

tenders$stall[2445] <- "01-90"
tenders$area[2445] <- as.double(5.28)

tenders$stall[2467] <- "01-56"
tenders$area[2467] <- as.double(5.88)

tenders$stall[2468] <- "01-16"
tenders$area[2468] <- as.double(5.88)

tenders$area[2517] <- as.double(5.76)

tenders$stall[2557] <- "01-39"
tenders$area[2557] <- as.double(6.24)

tenders$stall[2568] <- "01-20"
tenders$area[2568] <- as.double(6.24)

tenders$stall[2569] <- "01-21"
tenders$area[2569] <- as.double(6.24)

tenders$area[2590] <- as.double(7.00)

tenders$stall[2670] <- "02-33"
tenders$area[2670] <- as.double(6.95)

tenders$stall[2676] <- "01-37"
tenders$area[2676] <- as.double(6.95)

tenders$area[2743] <- as.double(5.28)

#adding PriceM2
tenders <- tenders %>%
  mutate (priceM2 = bidNum / area)

Getting rid of row 1749 (the split between ‘locked-up’ and ‘market’ stalls) and setting type for the rows after 1749 to ‘market’

tenders<-tenders%>%
  filter(row_number()!=1749) 

for(i in 1749:2779){
  tenders$type[i]="market"
}

Here we can see that some of the centre names are recorded as multiple entries. For example, Blk 22 Toa Payoh Lorong 7 has been recorded as multiple names such as Blk 22 Lorong 7 Toa Payoh, so we want to standardise them.

table(tenders$centre)

## 
##            AMOY STREET FOOD CENTRE                BEO CRESCENT MARKET 
##                                 62                                 22 
##                 BERSEH FOOD CENTRE                  BLK 1 JALAN KUKOH 
##                                103                                  3 
##           BLK 105 HOUGANG AVENUE 1      BLK 11 TELOK BLANGAH CRESCENT 
##                                 15                                 23 
##          BLK 112 JALAN BUKIT MERAH           BLK 115 BUKIT MERAH VIEW 
##                                 23                                 32 
##          BLK 117 ALJUNIED AVENUE 2         BLK 120 BUKIT MERAH LANE 1 
##                                131                                 20 
##         BLK 127 TOA PAYOH LORONG 1                   BLK 13 HAIG ROAD 
##                                 32                                  9 
##         BLK 137 TAMPINES STREET 11                   BLK 14 HAIG ROAD 
##                                 17                                  2 
##              BLK 159 MEI CHIN ROAD            BLK 16 BEDOK SOUTH ROAD 
##                                 49                                 40 
##  BLK 16 BEDOK SOUTH ROAD S(460016)        BLK 160 ANG MO KIO AVENUE 4 
##                                  1                                 12 
##        BLK 162 ANG MO KIO AVENUE 4        BLK 17 UPPER BOON KENG ROAD 
##                                  1                                 47 
##               BLK 20 GHIM MOH ROAD          BLK 209 HOUGANG STREET 21 
##                                 24                                  3 
##              BLK 21 MARSILING LANE         BLK 210 TOA PAYOH LORONG 8 
##                                 10                                 22 
##          BLK 210TOA PAYOH LORONG 8         BLK 216 BEDOK NORTH STREET 
##                                  1                                  1 
##       BLK 216 BEDOK NORTH STREET 1          BLK 22 LORONG 7 TOA PAYOH 
##                                 24                                  1 
## BLK 22 LORONG 7 TOA PAYOH LORONG 7          BLK 22 TOA PAYOH LORONG 7 
##                                  1                                 29 
##                  BLK 221A BOON LAY            BLK 221A BOON LAY PLACE 
##                                  1                                  8 
##         BLK 226D ANG MO KIO AVENUE       BLK 226D ANG MO KIO AVENUE 1 
##                                  1                                 40 
##        BLK 226D ANG MO KIO AVENUE1      BLK 226H ANG MO KIO STREET 22 
##                                  1                                  2 
##              BLK 22A HAVELOCK ROAD              BLK 22B HAVELOCK ROAD 
##                                  1                                  4 
##      BLK 254 JURONG EAST STREET 24               BLK 270 QUEEN STREET 
##                                  6                                 27 
##              BLK 29 BENDEMEER ROAD    BLK 29 BENDEMEER ROAD S(330029) 
##                                 27                                  1 
##          BLK 3 CHANGI VILLAGE ROAD             BLK 32 NEW MARKET ROAD 
##                                 19                                  6 
##                BLK 320 SHUNFU ROAD               BLK 335 SMITH STREET 
##                                 19                                248 
##                BLK 335SMITH STREET        BLK 341 ANG MO KIO AVENUE 1 
##                                  1                                 25 
##       BLK 347 JURONG EAST AVENUE 1          BLK 353 CLEMENTI AVENUE 2 
##                                 15                                 28 
##          BLK 36 TELOK BLANGAH RISE         BLK 37A TEBAN GARDENS ROAD 
##                                 23                                  5 
##     BLK 38A BEO CRESCENT S(169982)          BLK 409 ANG MO KIO AVENUE 
##                                  1                                  2 
##       BLK 409 ANG MO KIO AVENUE 10             BLK 41A CAMBRIDGE ROAD 
##                                 18                                 21 
##               BLK 44 HOLLAND DRIVE          BLK 448 CLEMENTI AVENUE 3 
##                                 46                                  4 
##          BLK 448 CLEMENTI AVENUE 4      BLK 453A ANG MO KIO AVENUE 10 
##                                  1                                  9 
##                  BLK 49 SIMS PLACE              BLK 4A EUNOS CRESCENT 
##                                 17                                 21 
##                  BLK 4A JALAN BATU           BLK 502 WEST COAST DRIVE 
##                                 38                                 18 
##           BLK 503 WEST COAST DRIVE             BLK 50A MARINE TERRACE 
##                                 19                                  8 
##            BLK 51 OLD AIRPORT ROAD       BLK 511 BEDOK NORTH STREET 3 
##                                 63                                  1 
##       BLK 527 ANG MO KIO AVENUE 10        BLK 531A UPPER CROSS STREET 
##                                 20                                 59 
##       BLK 538 BEDOK NORTH STREET 3       BLK 58 NEW UPPER CHANGI ROAD 
##                                 12                                  9 
##            BLK 6 JALAN BUKIT MERAH          BLK 6 TANJONG PAGAR PLAZA 
##                                 34                                 17 
##        BLK 628 ANG MO KIO AVENUE 4       BLK 630 BEDOK RESERVOIR ROAD 
##                                 30                                 19 
##               BLK 665 BUFFALO ROAD                 BLK 7 EMPRESS ROAD 
##                                 50                                 13 
##          BLK 724 ANG MO KIO AVENUE        BLK 724 ANG MO KIO AVENUE 6 
##                                  1                                 37 
##     BLK 726 CLEMENTI WEST STREET 2          BLK 74 TOA PAYOH LORONG 4 
##                                 14                                 53 
##          BLK 75 TOA PAYOH LORONG 5                BLK 79 CIRCUIT ROAD 
##                                  1                                 20 
##                BLK 79 REDHILL LANE         BLK 79 TELOK BLANGAH DRIVE 
##                                 20                                  2 
##               BLK 79A CIRCUIT ROAD                BLK 80 CIRCUIT ROAD 
##                                  8                                 14 
##         BLK 82 TELOK BLANGAH DRIVE        BLK 85 BEDOK NORTH STREET 4 
##                                 17                                 32 
##                BLK 85 REDHILL LANE                BLK 89 CIRCUIT ROAD 
##                                  8                                 20 
##               BLK 90 WHAMPOA DRIVE               BLK 91 WHAMPOA DRIVE 
##                                 11                                  2 
##               BLK 92 WHAMPOA DRIVE          BLK 93 TOA PAYOH LORONG 4 
##                                 21                                 10 
##                 BUKIT TIMAH MARKET       COMMONWEALTH CRESCENT MARKET 
##                                 24                                 77 
##                 DUNMAN FOOD CENTRE     EAST COAST LAGOON FOOD VILLAGE 
##                                 32                                  3 
##               GEYLANG SERAI MARKET            GOLDEN MILE FOOD CENTRE 
##                                 14                                 93 
##                HOLLAND ROAD MARKET             HOLLAND VILLAGE MARKET 
##                                  4                                 33 
##              KALLANG ESTATE MARKET          MARKET STREET FOOD CENTRE 
##                                 24                                 17 
##                MAXWELL FOOD CENTRE                 NEWTON FOOD CENTRE 
##                                 47                                 46 
##           NORTH BRIDGE ROAD MARKET            SERANGOON GARDEN MARKET 
##                                 59                                 19 
##  TAMAN JURONG MARKET & FOOD CENTRE                TANGLIN HALT MARKET 
##                                118                                 20 
##                 TIONG BAHRU MARKET         ZION RIVERSIDE FOOD CENTRE 
##                                 37                                  4

The affected centres are:
1. BLK 16 BEDOK SOUTH ROAD S(460016) (BLK 16 BEDOK SOUTH ROAD)
2. BLK 210TOA PAYOH LORONG 8 (BLK 210 TOA PAYOH LORONG 8)
3. BLK 216 BEDOK NORTH STREET (BLK 216 BEDOK NORTH STREET 1)
4. BLK 22 LORONG 7 TOA PAYOH (BLK 22 TOA PAYOH LORONG 7)
5. BLK 22 LORONG 7 TOA PAYOH LORONG 7 (BLK 22 TOA PAYOH LORONG 7)
6. BLK 221A BOON LAY (BLK 221A BOON LAY PLACE)
7. BLK 226D ANG MO KIO AVENUE (BLK 226D ANG MO KIO AVENUE 1)
8. BLK 226D ANG MO KIO AVENUE1 (BLK 226D ANG MO KIO AVENUE 1)
9. BLK 29 BENDEMEER ROAD S(330029) (BLK 29 BENDEMEER ROAD)
10. BLK 335SMITH STREET (BLK 335 SMITH STREET)
11. BLK 38A BEO CRESCENT S(169982) (BLK 38A BEO CRESCENT)
12. BLK 448 CLEMENTI AVENUE 4 (BLK 448 CLEMENTI AVENUE 3)
13. BLK 724 ANG MO KIO AVENUE (BLK 724 ANG MO KIO AVENUE 6)
14. BLK 221A BOON LAY (BLK 221A BOON LAY PLACE)
15. BLK 409 ANG MO KIO AVENUE (BLK 409 ANG MO KIO AVENUE 10)
Here we will fix up these centre names

var <- which(tenders$centre == "BLK 16 BEDOK SOUTH ROAD")
var2 <- which(tenders$centre == "BLK 16 BEDOK SOUTH ROAD S(460016)")
tenders$centre[var2] <-temp$centre[var[1]] #BLK 16 BEDOK SOUTH ROAD

var <- which(tenders$centre == "BLK 210 TOA PAYOH LORONG 8")
var2 <- which(tenders$centre == "BLK 210TOA PAYOH LORONG 8")
tenders$centre[var2] <-temp$centre[var[1]] #BLK 210 TOA PAYOH LORONG 8

var <- which(tenders$centre == "BLK 216 BEDOK NORTH STREET 1")
var2 <- which(tenders$centre == "BLK 216 BEDOK NORTH STREET")
tenders$centre[var2] <-temp$centre[var[1]] #BLK 216 BEDOK NORTH STREET 1

var <- which(tenders$centre == "BLK 22 TOA PAYOH LORONG 7")
var2 <- which(tenders$centre == "BLK 22 LORONG 7 TOA PAYOH")
tenders$centre[var2] <-temp$centre[var[1]] #BLK 22 TOA PAYOH LORONG 7

var2 <- which(tenders$centre == "BLK 22 LORONG 7 TOA PAYOH LORONG 7")
tenders$centre[var2] <-temp$centre[var[1]] #BLK 22 TOA PAYOH LORONG 7

var <- which(tenders$centre == "BLK 221A BOON LAY PLACE")
var2 <- which(tenders$centre == "BLK 221A BOON LAY")
tenders$centre[var2] <-temp$centre[var[1]] #BLK 221A BOON LAY PLACE

var <- which(tenders$centre == "BLK 226D ANG MO KIO AVENUE 1")
var2 <- which(tenders$centre == "BLK 226D ANG MO KIO AVENUE")
tenders$centre[var2] <-temp$centre[var[1]] #BLK 226D ANG MO KIO AVENUE 1

var2 <- which(tenders$centre == "BLK 226D ANG MO KIO AVENUE1")
tenders$centre[var2] <-temp$centre[var[1]] #BLK 226D ANG MO KIO AVENUE 1

var <- which(tenders$centre == "BLK 29 BENDEMEER ROAD")
var2 <- which(tenders$centre == "BLK 29 BENDEMEER ROAD S(330029)")
tenders$centre[var2] <-temp$centre[var[1]] #BLK 29 BENDEMEER ROAD

var <- which(tenders$centre == "BLK 335 SMITH STREET")
var2 <- which(tenders$centre == "BLK 335SMITH STREET")
tenders$centre[var2] <-temp$centre[var[1]] #BLK 335 SMITH STREET

var2 <- which(tenders$centre == "BLK 38A BEO CRESCENT S(169982)")
tenders$centre[var2] <-"BLK 38A BEO CRESCENT" #BLK 38A BEO CRESCENT

var <- which(tenders$centre == "BLK 448 CLEMENTI AVENUE 3")
var2 <- which(tenders$centre == "BLK 448 CLEMENTI AVENUE 4")
tenders$centre[var2] <-temp$centre[var[1]] #BLK 448 CLEMENTI AVENUE 3

var <- which(tenders$centre == "BLK 724 ANG MO KIO AVENUE 6")
var2 <- which(tenders$centre == "BLK 724 ANG MO KIO AVENUE")
tenders$centre[var2] <-temp$centre[var[1]] #BLK 724 ANG MO KIO AVENUE 6

var <- which(tenders$centre == "BLK 221A BOON LAY PLACE")
var2 <- which(tenders$centre == "BLK 221A BOON LAY")
tenders$centre[var2] <-tenders$centre[var[1]] #BLK 221A BOON LAY PLACE

var <- which(tenders$centre == "BLK 409 ANG MO KIO AVENUE 10")
var2 <- which(tenders$centre == "BLK 409 ANG MO KIO AVENUE")
tenders$centre[var2] <-tenders$centre[var[1]] #BLK 409 ANG MO KIO AVENUE 10

Finally, we store the cleaned up tenders into a csv

write.csv(tenders,file="cleaned_tenders.csv",row.names=F) #export as CSV

Lab 9 Prep

Justinian Siah

29 March 2018

Preparation of data

Cleaning up dataset