library(RSQLite)
library(DBI)db<-DBI::dbConnect(RSQLite::SQLite(), "C:/Users/Alam/Downloads/chinook.db")
db## <SQLiteConnection>
## Path: C:\Users\Alam\Downloads\chinook.db
## Extensions: TRUEclass(db)## [1] "SQLiteConnection"
## attr(,"package")
## [1] "RSQLite"dbListTables(db)## [1] "albums" "artists" "customers" "employees"
## [5] "genres" "invoice_items" "invoices" "media_types"
## [9] "playlist_track" "playlists" "sqlite_sequence" "sqlite_stat1"
## [13] "tracks"SELECT
InvoiceId, TrackId, UnitPrice
FROM
INVOICE_ITEMS;| InvoiceId | TrackId | UnitPrice |
|---|---|---|
| 1 | 2 | 0.99 |
| 1 | 4 | 0.99 |
| 2 | 6 | 0.99 |
| 2 | 8 | 0.99 |
| 2 | 10 | 0.99 |
| 2 | 12 | 0.99 |
| 3 | 16 | 0.99 |
| 3 | 20 | 0.99 |
| 3 | 24 | 0.99 |
| 3 | 28 | 0.99 |
library(tidyverse)## Warning in as.POSIXlt.POSIXct(Sys.time()): unable to identify current timezone 'A':
## please set environment variable 'TZ'## -- Attaching packages --------------------------------------- tidyverse 1.3.1 --## v ggplot2 3.3.5 v purrr 0.3.4
## v tibble 3.1.6 v dplyr 1.0.7
## v tidyr 1.1.4 v stringr 1.4.0
## v readr 2.1.1 v forcats 0.5.1## -- Conflicts ------------------------------------------ tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag() masks stats::lag()class(db)## [1] "SQLiteConnection"
## attr(,"package")
## [1] "RSQLite"dbListTables(db)## [1] "albums" "artists" "customers" "employees"
## [5] "genres" "invoice_items" "invoices" "media_types"
## [9] "playlist_track" "playlists" "sqlite_sequence" "sqlite_stat1"
## [13] "tracks"dplyr::tbl(db,"albums")## # Source: table<albums> [?? x 3]
## # Database: sqlite 3.37.0 [C:\Users\Alam\Downloads\chinook.db]
## AlbumId Title ArtistId
## <int> <chr> <int>
## 1 1 For Those About To Rock We Salute You 1
## 2 2 Balls to the Wall 2
## 3 3 Restless and Wild 2
## 4 4 Let There Be Rock 1
## 5 5 Big Ones 3
## 6 6 Jagged Little Pill 4
## 7 7 Facelift 5
## 8 8 Warner 25 Anos 6
## 9 9 Plays Metallica By Four Cellos 7
## 10 10 Audioslave 8
## # ... with more rowsemployees <- tbl(db, "employees")
class(employees)## [1] "tbl_SQLiteConnection" "tbl_dbi" "tbl_sql"
## [4] "tbl_lazy" "tbl"employees## # Source: table<employees> [?? x 15]
## # Database: sqlite 3.37.0 [C:\Users\Alam\Downloads\chinook.db]
## EmployeeId LastName FirstName Title ReportsTo BirthDate HireDate Address City
## <int> <chr> <chr> <chr> <int> <chr> <chr> <chr> <chr>
## 1 1 Adams Andrew Gene~ NA 1962-02-~ 2002-08~ 11120 ~ Edmo~
## 2 2 Edwards Nancy Sale~ 1 1958-12-~ 2002-05~ 825 8 ~ Calg~
## 3 3 Peacock Jane Sale~ 2 1973-08-~ 2002-04~ 1111 6~ Calg~
## 4 4 Park Margaret Sale~ 2 1947-09-~ 2003-05~ 683 10~ Calg~
## 5 5 Johnson Steve Sale~ 2 1965-03-~ 2003-10~ 7727B ~ Calg~
## 6 6 Mitchell Michael IT M~ 1 1973-07-~ 2003-10~ 5827 B~ Calg~
## 7 7 King Robert IT S~ 6 1970-05-~ 2004-01~ 590 Co~ Leth~
## 8 8 Callahan Laura IT S~ 6 1968-01-~ 2004-03~ 923 7 ~ Leth~
## # ... with 6 more variables: State <chr>, Country <chr>, PostalCode <chr>,
## # Phone <chr>, Fax <chr>, Email <chr>query <- employees %>%
mutate(Name=FirstName+LastName) %>% select(-FirstName, -LastName)
query## # Source: lazy query [?? x 14]
## # Database: sqlite 3.37.0 [C:\Users\Alam\Downloads\chinook.db]
## EmployeeId Title ReportsTo BirthDate HireDate Address City State Country
## <int> <chr> <int> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 1 Genera~ NA 1962-02-1~ 2002-08-~ 11120 J~ Edmo~ AB Canada
## 2 2 Sales ~ 1 1958-12-0~ 2002-05-~ 825 8 A~ Calg~ AB Canada
## 3 3 Sales ~ 2 1973-08-2~ 2002-04-~ 1111 6 ~ Calg~ AB Canada
## 4 4 Sales ~ 2 1947-09-1~ 2003-05-~ 683 10 ~ Calg~ AB Canada
## 5 5 Sales ~ 2 1965-03-0~ 2003-10-~ 7727B 4~ Calg~ AB Canada
## 6 6 IT Man~ 1 1973-07-0~ 2003-10-~ 5827 Bo~ Calg~ AB Canada
## 7 7 IT Sta~ 6 1970-05-2~ 2004-01-~ 590 Col~ Leth~ AB Canada
## 8 8 IT Sta~ 6 1968-01-0~ 2004-03-~ 923 7 S~ Leth~ AB Canada
## # ... with 5 more variables: PostalCode <chr>, Phone <chr>, Fax <chr>,
## # Email <chr>, Name <int>show_query(query)## <SQL>
## SELECT `EmployeeId`, `Title`, `ReportsTo`, `BirthDate`, `HireDate`, `Address`, `City`, `State`, `Country`, `PostalCode`, `Phone`, `Fax`, `Email`, `FirstName` + `LastName` AS `Name`
## FROM `employees`##Data Wrangling##
library(datasets)
beaver<-beaver1beaver %>% select(time,temp)## time temp
## 1 840 36.33
## 2 850 36.34
## 3 900 36.35
## 4 910 36.42
## 5 920 36.55
## 6 930 36.69
## 7 940 36.71
## 8 950 36.75
## 9 1000 36.81
## 10 1010 36.88
## 11 1020 36.89
## 12 1030 36.91
## 13 1040 36.85
## 14 1050 36.89
## 15 1100 36.89
## 16 1110 36.67
## 17 1120 36.50
## 18 1130 36.74
## 19 1140 36.77
## 20 1150 36.76
## 21 1200 36.78
## 22 1210 36.82
## 23 1220 36.89
## 24 1230 36.99
## 25 1240 36.92
## 26 1250 36.99
## 27 1300 36.89
## 28 1310 36.94
## 29 1320 36.92
## 30 1330 36.97
## 31 1340 36.91
## 32 1350 36.79
## 33 1400 36.77
## 34 1410 36.69
## 35 1420 36.62
## 36 1430 36.54
## 37 1440 36.55
## 38 1450 36.67
## 39 1500 36.69
## 40 1510 36.62
## 41 1520 36.64
## 42 1530 36.59
## 43 1540 36.65
## 44 1550 36.75
## 45 1600 36.80
## 46 1610 36.81
## 47 1620 36.87
## 48 1630 36.87
## 49 1640 36.89
## 50 1650 36.94
## 51 1700 36.98
## 52 1710 36.95
## 53 1720 37.00
## 54 1730 37.07
## 55 1740 37.05
## 56 1750 37.00
## 57 1800 36.95
## 58 1810 37.00
## 59 1820 36.94
## 60 1830 36.88
## 61 1840 36.93
## 62 1850 36.98
## 63 1900 36.97
## 64 1910 36.85
## 65 1920 36.92
## 66 1930 36.99
## 67 1940 37.01
## 68 1950 37.10
## 69 2000 37.09
## 70 2010 37.02
## 71 2020 36.96
## 72 2030 36.84
## 73 2040 36.87
## 74 2050 36.85
## 75 2100 36.85
## 76 2110 36.87
## 77 2120 36.89
## 78 2130 36.86
## 79 2140 36.91
## 80 2150 37.53
## 81 2200 37.23
## 82 2210 37.20
## 83 2230 37.25
## 84 2240 37.20
## 85 2250 37.21
## 86 2300 37.24
## 87 2310 37.10
## 88 2320 37.20
## 89 2330 37.18
## 90 2340 36.93
## 91 2350 36.83
## 92 0 36.93
## 93 10 36.83
## 94 20 36.80
## 95 30 36.75
## 96 40 36.71
## 97 50 36.73
## 98 100 36.75
## 99 110 36.72
## 100 120 36.76
## 101 130 36.70
## 102 140 36.82
## 103 150 36.88
## 104 200 36.94
## 105 210 36.79
## 106 220 36.78
## 107 230 36.80
## 108 240 36.82
## 109 250 36.84
## 110 300 36.86
## 111 310 36.88
## 112 320 36.93
## 113 330 36.97
## 114 340 37.15beaver %>% arrange(temp)## day time temp activ
## 1 346 840 36.33 0
## 2 346 850 36.34 0
## 3 346 900 36.35 0
## 4 346 910 36.42 0
## 5 346 1120 36.50 0
## 6 346 1430 36.54 0
## 7 346 920 36.55 0
## 8 346 1440 36.55 0
## 9 346 1530 36.59 0
## 10 346 1420 36.62 0
## 11 346 1510 36.62 0
## 12 346 1520 36.64 0
## 13 346 1540 36.65 0
## 14 346 1110 36.67 0
## 15 346 1450 36.67 0
## 16 346 930 36.69 0
## 17 346 1410 36.69 0
## 18 346 1500 36.69 0
## 19 347 130 36.70 0
## 20 346 940 36.71 0
## 21 347 40 36.71 0
## 22 347 110 36.72 0
## 23 347 50 36.73 0
## 24 346 1130 36.74 0
## 25 346 950 36.75 0
## 26 346 1550 36.75 0
## 27 347 30 36.75 0
## 28 347 100 36.75 0
## 29 346 1150 36.76 0
## 30 347 120 36.76 0
## 31 346 1140 36.77 0
## 32 346 1400 36.77 0
## 33 346 1200 36.78 0
## 34 347 220 36.78 0
## 35 346 1350 36.79 0
## 36 347 210 36.79 0
## 37 346 1600 36.80 0
## 38 347 20 36.80 0
## 39 347 230 36.80 0
## 40 346 1000 36.81 0
## 41 346 1610 36.81 0
## 42 346 1210 36.82 0
## 43 347 140 36.82 0
## 44 347 240 36.82 0
## 45 346 2350 36.83 0
## 46 347 10 36.83 0
## 47 346 2030 36.84 0
## 48 347 250 36.84 0
## 49 346 1040 36.85 0
## 50 346 1910 36.85 0
## 51 346 2050 36.85 0
## 52 346 2100 36.85 0
## 53 346 2130 36.86 0
## 54 347 300 36.86 0
## 55 346 1620 36.87 0
## 56 346 1630 36.87 0
## 57 346 2040 36.87 0
## 58 346 2110 36.87 0
## 59 346 1010 36.88 0
## 60 346 1830 36.88 0
## 61 347 150 36.88 0
## 62 347 310 36.88 0
## 63 346 1020 36.89 0
## 64 346 1050 36.89 0
## 65 346 1100 36.89 0
## 66 346 1220 36.89 0
## 67 346 1300 36.89 0
## 68 346 1640 36.89 0
## 69 346 2120 36.89 0
## 70 346 1030 36.91 0
## 71 346 1340 36.91 0
## 72 346 2140 36.91 0
## 73 346 1240 36.92 0
## 74 346 1320 36.92 0
## 75 346 1920 36.92 0
## 76 346 1840 36.93 0
## 77 346 2340 36.93 0
## 78 347 0 36.93 0
## 79 347 320 36.93 0
## 80 346 1310 36.94 0
## 81 346 1650 36.94 0
## 82 346 1820 36.94 0
## 83 347 200 36.94 0
## 84 346 1710 36.95 0
## 85 346 1800 36.95 0
## 86 346 2020 36.96 0
## 87 346 1330 36.97 0
## 88 346 1900 36.97 0
## 89 347 330 36.97 0
## 90 346 1700 36.98 0
## 91 346 1850 36.98 0
## 92 346 1230 36.99 0
## 93 346 1250 36.99 0
## 94 346 1930 36.99 0
## 95 346 1720 37.00 0
## 96 346 1750 37.00 0
## 97 346 1810 37.00 0
## 98 346 1940 37.01 0
## 99 346 2010 37.02 0
## 100 346 1740 37.05 0
## 101 346 1730 37.07 1
## 102 346 2000 37.09 0
## 103 346 1950 37.10 1
## 104 346 2310 37.10 0
## 105 347 340 37.15 1
## 106 346 2330 37.18 0
## 107 346 2210 37.20 0
## 108 346 2240 37.20 0
## 109 346 2320 37.20 0
## 110 346 2250 37.21 0
## 111 346 2200 37.23 0
## 112 346 2300 37.24 1
## 113 346 2230 37.25 1
## 114 346 2150 37.53 1beaver %>% filter (time>1000)## day time temp activ
## 1 346 1010 36.88 0
## 2 346 1020 36.89 0
## 3 346 1030 36.91 0
## 4 346 1040 36.85 0
## 5 346 1050 36.89 0
## 6 346 1100 36.89 0
## 7 346 1110 36.67 0
## 8 346 1120 36.50 0
## 9 346 1130 36.74 0
## 10 346 1140 36.77 0
## 11 346 1150 36.76 0
## 12 346 1200 36.78 0
## 13 346 1210 36.82 0
## 14 346 1220 36.89 0
## 15 346 1230 36.99 0
## 16 346 1240 36.92 0
## 17 346 1250 36.99 0
## 18 346 1300 36.89 0
## 19 346 1310 36.94 0
## 20 346 1320 36.92 0
## 21 346 1330 36.97 0
## 22 346 1340 36.91 0
## 23 346 1350 36.79 0
## 24 346 1400 36.77 0
## 25 346 1410 36.69 0
## 26 346 1420 36.62 0
## 27 346 1430 36.54 0
## 28 346 1440 36.55 0
## 29 346 1450 36.67 0
## 30 346 1500 36.69 0
## 31 346 1510 36.62 0
## 32 346 1520 36.64 0
## 33 346 1530 36.59 0
## 34 346 1540 36.65 0
## 35 346 1550 36.75 0
## 36 346 1600 36.80 0
## 37 346 1610 36.81 0
## 38 346 1620 36.87 0
## 39 346 1630 36.87 0
## 40 346 1640 36.89 0
## 41 346 1650 36.94 0
## 42 346 1700 36.98 0
## 43 346 1710 36.95 0
## 44 346 1720 37.00 0
## 45 346 1730 37.07 1
## 46 346 1740 37.05 0
## 47 346 1750 37.00 0
## 48 346 1800 36.95 0
## 49 346 1810 37.00 0
## 50 346 1820 36.94 0
## 51 346 1830 36.88 0
## 52 346 1840 36.93 0
## 53 346 1850 36.98 0
## 54 346 1900 36.97 0
## 55 346 1910 36.85 0
## 56 346 1920 36.92 0
## 57 346 1930 36.99 0
## 58 346 1940 37.01 0
## 59 346 1950 37.10 1
## 60 346 2000 37.09 0
## 61 346 2010 37.02 0
## 62 346 2020 36.96 0
## 63 346 2030 36.84 0
## 64 346 2040 36.87 0
## 65 346 2050 36.85 0
## 66 346 2100 36.85 0
## 67 346 2110 36.87 0
## 68 346 2120 36.89 0
## 69 346 2130 36.86 0
## 70 346 2140 36.91 0
## 71 346 2150 37.53 1
## 72 346 2200 37.23 0
## 73 346 2210 37.20 0
## 74 346 2230 37.25 1
## 75 346 2240 37.20 0
## 76 346 2250 37.21 0
## 77 346 2300 37.24 1
## 78 346 2310 37.10 0
## 79 346 2320 37.20 0
## 80 346 2330 37.18 0
## 81 346 2340 36.93 0
## 82 346 2350 36.83 0beaver %>% mutate(daytemp = temp-1)## day time temp activ daytemp
## 1 346 840 36.33 0 35.33
## 2 346 850 36.34 0 35.34
## 3 346 900 36.35 0 35.35
## 4 346 910 36.42 0 35.42
## 5 346 920 36.55 0 35.55
## 6 346 930 36.69 0 35.69
## 7 346 940 36.71 0 35.71
## 8 346 950 36.75 0 35.75
## 9 346 1000 36.81 0 35.81
## 10 346 1010 36.88 0 35.88
## 11 346 1020 36.89 0 35.89
## 12 346 1030 36.91 0 35.91
## 13 346 1040 36.85 0 35.85
## 14 346 1050 36.89 0 35.89
## 15 346 1100 36.89 0 35.89
## 16 346 1110 36.67 0 35.67
## 17 346 1120 36.50 0 35.50
## 18 346 1130 36.74 0 35.74
## 19 346 1140 36.77 0 35.77
## 20 346 1150 36.76 0 35.76
## 21 346 1200 36.78 0 35.78
## 22 346 1210 36.82 0 35.82
## 23 346 1220 36.89 0 35.89
## 24 346 1230 36.99 0 35.99
## 25 346 1240 36.92 0 35.92
## 26 346 1250 36.99 0 35.99
## 27 346 1300 36.89 0 35.89
## 28 346 1310 36.94 0 35.94
## 29 346 1320 36.92 0 35.92
## 30 346 1330 36.97 0 35.97
## 31 346 1340 36.91 0 35.91
## 32 346 1350 36.79 0 35.79
## 33 346 1400 36.77 0 35.77
## 34 346 1410 36.69 0 35.69
## 35 346 1420 36.62 0 35.62
## 36 346 1430 36.54 0 35.54
## 37 346 1440 36.55 0 35.55
## 38 346 1450 36.67 0 35.67
## 39 346 1500 36.69 0 35.69
## 40 346 1510 36.62 0 35.62
## 41 346 1520 36.64 0 35.64
## 42 346 1530 36.59 0 35.59
## 43 346 1540 36.65 0 35.65
## 44 346 1550 36.75 0 35.75
## 45 346 1600 36.80 0 35.80
## 46 346 1610 36.81 0 35.81
## 47 346 1620 36.87 0 35.87
## 48 346 1630 36.87 0 35.87
## 49 346 1640 36.89 0 35.89
## 50 346 1650 36.94 0 35.94
## 51 346 1700 36.98 0 35.98
## 52 346 1710 36.95 0 35.95
## 53 346 1720 37.00 0 36.00
## 54 346 1730 37.07 1 36.07
## 55 346 1740 37.05 0 36.05
## 56 346 1750 37.00 0 36.00
## 57 346 1800 36.95 0 35.95
## 58 346 1810 37.00 0 36.00
## 59 346 1820 36.94 0 35.94
## 60 346 1830 36.88 0 35.88
## 61 346 1840 36.93 0 35.93
## 62 346 1850 36.98 0 35.98
## 63 346 1900 36.97 0 35.97
## 64 346 1910 36.85 0 35.85
## 65 346 1920 36.92 0 35.92
## 66 346 1930 36.99 0 35.99
## 67 346 1940 37.01 0 36.01
## 68 346 1950 37.10 1 36.10
## 69 346 2000 37.09 0 36.09
## 70 346 2010 37.02 0 36.02
## 71 346 2020 36.96 0 35.96
## 72 346 2030 36.84 0 35.84
## 73 346 2040 36.87 0 35.87
## 74 346 2050 36.85 0 35.85
## 75 346 2100 36.85 0 35.85
## 76 346 2110 36.87 0 35.87
## 77 346 2120 36.89 0 35.89
## 78 346 2130 36.86 0 35.86
## 79 346 2140 36.91 0 35.91
## 80 346 2150 37.53 1 36.53
## 81 346 2200 37.23 0 36.23
## 82 346 2210 37.20 0 36.20
## 83 346 2230 37.25 1 36.25
## 84 346 2240 37.20 0 36.20
## 85 346 2250 37.21 0 36.21
## 86 346 2300 37.24 1 36.24
## 87 346 2310 37.10 0 36.10
## 88 346 2320 37.20 0 36.20
## 89 346 2330 37.18 0 36.18
## 90 346 2340 36.93 0 35.93
## 91 346 2350 36.83 0 35.83
## 92 347 0 36.93 0 35.93
## 93 347 10 36.83 0 35.83
## 94 347 20 36.80 0 35.80
## 95 347 30 36.75 0 35.75
## 96 347 40 36.71 0 35.71
## 97 347 50 36.73 0 35.73
## 98 347 100 36.75 0 35.75
## 99 347 110 36.72 0 35.72
## 100 347 120 36.76 0 35.76
## 101 347 130 36.70 0 35.70
## 102 347 140 36.82 0 35.82
## 103 347 150 36.88 0 35.88
## 104 347 200 36.94 0 35.94
## 105 347 210 36.79 0 35.79
## 106 347 220 36.78 0 35.78
## 107 347 230 36.80 0 35.80
## 108 347 240 36.82 0 35.82
## 109 347 250 36.84 0 35.84
## 110 347 300 36.86 0 35.86
## 111 347 310 36.88 0 35.88
## 112 347 320 36.93 0 35.93
## 113 347 330 36.97 0 35.97
## 114 347 340 37.15 1 36.15beaver %>% summarise(avgtemp = sum(temp)/114)## avgtemp
## 1 36.86219##semua secara bersamaan
beaver %>% select(time,temp) %>%
arrange(temp) %>%
filter (time>1000) %>%
mutate(daytemp = temp-1) %>%
summarise(avgtemp = sum(temp)/114)## avgtemp
## 1 26.54561