Tech Diary

November 21, 2018

R Barchart

if (!require(ggplot2)) install.packages('ggplot2')
library(ggplot2)

...
ggplot(
data=somedata,
aes(x=mth_end_dt, y=col3/1000000, colour=col2)
) +
geom_bar (stat="identity", width = 10.0, position = position_dodge(width = 10.0)) +

xlab("Month") +
ylab("Trend") +
scale_x_date(
date_breaks = "1 month"
)

R IN operator

somedata[somedata$col1 %in% c('A', 'B'),]

R melt data frame - convert to long format

melted_data <- melt(somedata[somedata$col1 %in% c('A', 'B'),], id="col1") # convert to long format

Before

col1	col2	col3
A	x	1
B	y	2

C z 3

After melt

col1	variable	value
A	col2	x
A	col3	1
B	col2	y
B	col3	2

R - Structure of object

> str(anobject)

R - Help with command

> ?rm

> ?ls

R - Subset of data in a data frame

asubset = somedata[somedata$mth_end_dt >="2016-01-01" & somedata$mth_end_dt <= "2016-12-31",]

Note: [row filter,column filter]

[row filter, ] means all rows matching the filter. all columns without filtering

y = asubset [, c('col5', 'col6')] means all rows but only include 2 columns namely col5 and col6

R Multiple line/Multi trend plot

if (!require(ggplot2)) install.packages('ggplot2')
library(ggplot2
...
ggplot(
data = somdata,
aes(x = mth_end_dt, y = hour, colour = dept)
) +
xlab("Month") +
ylab("Hour") +
geom_line() +
geom_point() +
geom_text(aes(label=hour), vjust=-0.5, size=3) +
scale_x_date(
date_breaks = "1 month",
# date_labels="%b-%Y"
) +
scale_colour_discrete(name ="Department")

R Line plot

if (!require(ggplot2)) install.packages('ggplot2')
library(ggplot2)

....

ggplot(
data = somedata,
aes(x = mth, y = hours)
) +
xlab("Month") +
ylab("Hours") +
geom_line() +
geom_point() +
scale_x_date(
date_breaks = "1 month",
# date_labels="%b-%Y"
)

R - Group by aggregation

x_aggr <- aggregate(x$a, by=list(mth=x$mth_end_dt), FUN=sum)

R - Convert field to Date

> a$mth_end_dt <- as.Date(a$mth_end_dt, "%m/%d/%Y")

R remove all objects

> rm(list = ls())

R List all objects

> ls()

R read csv file

comp_fte_trnd <- read.csv("~/R_work/afile.csv", header=TRUE, sep="|")

June 5, 2018

Single character wildcard _

where desc like '%-abc__f%'

Example: abcdefgh

November 17, 2017

grep regex command to extract key values from a file

\w in regex is word character (ASCII letter, digit or underscore)

> grep -o '\bNAME ="\w*"' ./file.xml

Output:

Name = "company"

Name = "phone_extension"

......

> grep -o '\bNAME ="\w*"' ./file.xml | awk '{print $2}' | grep -o '\w*'

Output:

company

phone_extension

File

-----

September 13, 2017

One way of checking if date was formatted well

Date format expected: dd-MMM-yyyy. Example: 03-MAR-2017

WHERE condition:
....
and a_date is not null
and length(a_date ) = 11
and index(a_date , '-') = 3

August 22, 2017

Covert string to valid integer

select cast(coalesce(to_number('1a2'), -1) as int)

July 19, 2017

Inner Join

Will list rows where left and right match

select
emp.id,
emp.name,
dept.name
from employee emp
inner join department dept
on emp.dept_id = dept.dept_id

Will list only those employees who have a department which is found in the dept table.

Right Join

Every rows from right table and matching records from left

select
emp.id,
emp.name,
dept.name
from employee emp
right join department dept
on emp.dept_id = dept.dept_id

Even if the department doesn't have employees, it will be listed. If an employee doesn't have a department, it will not be listed.

Left Join

Every rows from left table and matching records from right

select
emp.id,
emp.name,
dept.name
from employee emp
left join department dept
on emp.dept_id = dept.dept_id

Even if the employee doesn't have a department, he/she will be listed here.