This will be the bridge between r and excel book

  • No School
  • AA 1
  • 62

This preview shows page 22 - 26 out of 62 pages.

Load the workbook. This will be the bridge between r and excel. book <- loadWorkbook("car_dataset_excel.xlsx") str(book) ## Formal class 'workbook' [package "XLConnect"] with 2 slots ## [email protected] filename: chr "car_dataset_excel.xlsx" ## [email protected] jobj :Formal class 'jobjRef' [package "rJava"] with 2 slots ## .. .. [email protected] jobj :<externalptr> ## .. .. [email protected] jclass: chr "com/miraisolutions/xlconnect/integration/r/RWorkbookWrapper" getSheets(book) ## [1] "car_dataset_all" "car_dataset_5_10" "car_dataset_5" df <- readWorksheet(book, sheet = "car_dataset_all") head(df) ## symbolizing losses fuel_type aspiration no_of_doors body ## 1 3 NA gas std two convertible ## 2 3 NA gas std two convertible ## 3 1 NA gas std two hatchback ## 4 2 164 gas std four sedan ## 5 2 164 gas std four sedan ## 6 2 NA gas std two sedan ## wheel_base length width height curb_weight engine_type engine_size bore ## 1 88.6 168.8 64.1 48.8 2548 dohc
Image of page 22
130 3.47 ## 2 88.6 168.8 64.1 48.8 2548 dohc 130 3.47 ## 3 94.5 171.2 65.5 52.4 2823 ohcv 152 2.68 ## 4 99.8 176.6 66.2 54.3 2337 ohc 109 3.19 ## 5 99.4 176.6 66.4 54.3 2824 ohc 136 3.19 ## 6 99.8 177.3 66.3 53.1 2507 ohc 136 3.19 ## stroke compression_ratio horse_power peak_rpm city_mileage ## 1 2.68 9.0 111 5000 21 ## 2 2.68 9.0 111 5000 21 ## 3 3.47 9.0 154 5000 19 ## 4 3.40 10.0 102 5500 24 ## 5 3.40 8.0 115 5500 18 ## 6 3.40 8.5 110 5500 19 ## highway_mileage price ## 1 27 13495 ## 2 27 16500 ## 3 26 16500 ## 4 30 13950 ## 5 22 17450 ## 6 25 15250 df <- readWorksheet(book, sheet = "car_dataset_all", startRow = 3, endRow = 5, startCol = 3, endCol = 5, header = F) df ## Col1 Col2 Col3 ## 1 gas std two ## 2 gas std two ## 3 gas std four df <- readWorksheet(book, sheet = "car_dataset_all") df_filtered <- df[!is.na(df$losses), 1:5] createSheet(book, "filtered_data") writeWorksheet(book, df_filtered, "filtered_data") saveWorkbook(book, "car_dataset_excel.xlsx") book <- loadWorkbook("car_dataset_excel.xlsx") getSheets(book) ## [1] "car_dataset_all" "car_dataset_5_10" "car_dataset_5" ## [4] "filtered_data" renameSheet(book, "filtered_data", "f_data") saveWorkbook(book, "car_dataset_excel.xlsx") book <- loadWorkbook("car_dataset_excel.xlsx") getSheets(book) ## [1] "car_dataset_all" "car_dataset_5_10" "car_dataset_5" ## [4] "f_data" removeSheet(book, "f_data") saveWorkbook(book, "car_dataset_excel.xlsx") book <- loadWorkbook("car_dataset_excel.xlsx") getSheets(book)
Image of page 23
## [1] "car_dataset_all" "car_dataset_5_10" "car_dataset_5" Database #install.packages("RMySQL") library(DBI) con <- dbConnect(RMySQL::MySQL(), dbname = "tweater", host = "courses.csrrinzqubik.us-east- 1.rds.amazonaws.com", port = 3306, user = "student", password = "datacamp") dbListTables(con) ## [1] "comments" "tweats" "users" df <- dbReadTable(con, "comments") head(df) ## id tweat_id user_id message ## 1 1022 87 7 nice! ## 2 1000 77 7 great! ## 3 1011 49 5 love it ## 4 1012 87 1 awesome! thanks! ## 5 1010 88 6 yuck! ## 6 1026 77 4 not my thing! ravi <- dbGetQuery(con, "SELECT tweat_id FROM comments WHERE user_id = 1") ravi ## tweat_id ## 1 87 ## 2 49 ## 3 77 ## 4 77 useful <- dbGetQuery(con, "SELECT post FROM tweats WHERE date > '2015- 09-21'") useful ## post ## 1 open and crush avocado. add shrimps. perfect starter. ## 2 nachos. add tomato sauce, minced meat and cheese. oven for 10 mins. ## 3 just eat an apple. simply and healthy. dbDisconnect(con) ## [1] TRUE Cleaning Data
Image of page 24
Exploring Data class() – > to check if it is a dataframe dim() –> dimension of the datadrame names() –> check out the column names str() –> to find out the overall structure glimpse() –> fro dplyr library, similar to str() summary() –> summary of each column head() –> to look at first few rows tail() – > to look at bottom few rows cars_data <- read.csv("car_dataset.csv") library(dplyr) ## ## Attaching package: 'dplyr'
Image of page 25
Image of page 26

You've reached the end of your free preview.

Want to read all 62 pages?

  • Fall '19
  • Na Na Hey Hey Kiss Him Goodbye

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

Stuck? We have tutors online 24/7 who can help you get unstuck.
A+ icon
Ask Expert Tutors You can ask You can ask You can ask (will expire )
Answers in as fast as 15 minutes