Load data

  1. Combine data on multiple worksheets into 1 worksheet
#install.packages("rio")
#library(rio)
#data <- import_list("/Users/lytran/Desktop/data.xlsx")
#data <- rbindlist(data)
  1. Read big file.csv
#install.packages("data.table")
#library(data.table)
#data <- fread ("/Users/lytran/Desktop/data.csv", header = T, sep = ',')
  1. Read file.xlsx
#install.packages("gdata")
#library(gdata)
#data <- read.xls("/Users/lytran/Desktop/data.xlsx", sheet = 1, header = TRUE)

Duplicates

  1. Get non-duplicates of data (all attributes )
#data <- data[!duplicated(data),]
  1. Get non-duplicates of data (based on 3 attributes )
#data <- data[!duplicated(data[,c("ID", "First_Name", "Last_Name")]),]
  1. Get duplicates of test_highdata (based on 3 attributes )
#data <- data[duplicated(data[,c("ID", "First_Name", "Last_Name")]),]

Missing values

  1. Show missing values
# data[rowSums(is.na(data))>0,] 
# data[is.na(data$Month),] 
#install.packages("DataExplorer")
#library(DataExplorer)
#plot_missing(data)  #plot missing values
  1. Replace missing values
#data$total[is.na(data$total)] <- mean(data$total, na.rm = T)

Date

Change str(Month) into date type to arrange Month in order

#str(data$Month) 
#install.packages("zoo")
#library(zoo)
#data$Month <- as.yearmon(data$Month, "%b-%Y")

Filter

#Filter rows in data that NPI = NA and Contactid != Unknown
#na_NPI <- data %>% filter (is.na(NPI), Contactid != 'Unknown')

#Filters rows in data1 to satisfy condition: they have the same ID in data2
#data1 %>% filter (ID %in% data2$ID))

#Filters rows in data1 to satisfy condition: they don't have the same ID in data2
#data %>% filter (!ID %in% data2$ID))

Spread data

#library(tidyr)
#library(dplyr)
#data <- data %>% group_by(ID, Month) %>% summarise(total = sum(order)))
#data <- data %>% spread(Month,total))

Replace value and count unique value

#data$Last_Name[data$Last_Name == 'ly'] <-'tran'
#data$First_Name[data$ID == 1282684] <- "Ly"
#nrow(unique(data[, c("ID", "Phone_number")]))
#length(unique(data[,c("NPI")]))

Join

#dat1 %>% left_join(dat2, by = c('ID_dat1' = 'ID_dat2')))
#dat1 %>% inner_join(dat2, by = c('ID_dat1' = 'ID_dat2')))