This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# install tidyverse | |
install.packages("tidyverse") | |
library(tidyverse) | |
# step 1 -- review example dataset 'msleep' in tidyverse package | |
glimpse(msleep) | |
summary(msleep) | |
# step 2.1 -- remove all rows with missing values | |
clean_msleep <- drop_na(msleep) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- SELECT clause to select specific columns | |
-- Select all columns from customers table | |
SELECT * FROM customers; | |
-- Select firstname, lastname and address columns from customers table | |
SELECT firstname, lastname, address FROM customers; | |
-- Use AS (alias) to change the column name | |
SELECT | |
firstname AS customer_firstname, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# install package in R | |
install.packages("dplyr") | |
library(dplyr) | |
# useful functions to analyze dataframe | |
# review dataframe structure | |
glimpse(mtcars) | |
# print first six and last six rows | |
head(mtcars) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# dplyr five verbs | |
# select columns mpg, hp, wt, cyl from mtcars dataframe | |
select(mtcars, mpg, hp, wt, cyl) | |
# select column 1 to 5, 8 and 9 | |
select(1:5, 8, 9) | |
# remove column 9 and 10 | |
select(-9, -10) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- select all columns from table customers | |
SELECT * FROM customers; | |
-- select column firstname, lastname, email, phone from customers | |
SELECT | |
firstname, | |
lastname, | |
email, | |
phone | |
FROM customers; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- where clause used to filter rows | |
-- filter only customers from USA | |
SELECT * | |
FROM customers | |
WHERE country = 'USA'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- JOIN two tables: album and artist | |
SELECT * | |
FROM artists | |
JOIN albums | |
ON artists.artistid = albums.artistid; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- JOIN more than two tables | |
SELECT | |
artists.name, | |
albums.title, | |
tracks.name | |
FROM artists | |
JOIN albums | |
ON artists.artistid = albums.artistid | |
JOIN tracks | |
ON tracks.albumid = albums.albumid |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- filter customers from USA who also live in CA state | |
SELECT * | |
FROM customers | |
WHERE country = 'USA' AND state = 'CA'; | |
-- filter customers from USA or Brazil | |
SELECT * | |
FROM customers | |
WHERE country = 'USA' OR country = 'Brazil'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- count number of rows in table customers | |
SELECT COUNT(*) | |
FROM customers; |
OlderNewer