external-review-counts.R

library('RPostgreSQL')
library('dplyr')
library('lubridate')
library('tidyr')

# Get external reviews with UTC timestamps from the DB
# \copy (select distinct o.observation_id, o.collection_date, o.vendor_id, v.name, reviewed_at_utc, reviewed_by from ops.review r   join analysis.observation o on r.observation_id=o.observation_id   join analysis.vendor v on o.vendor_id=v.vendor_id where reviewed_at_utc >= '2017-05-01' order by reviewed_at_utc) TO '/Users/cbare/Desktop/external-reviews.tsv' WITH NULL AS '';
# df <- read.table('/Users/cbare/Desktop/external-reviews.tsv', stringsAsFactors=F)

# get data via a query
env.vars <- Sys.getenv(c('DB_USER', 'DB_PW', 'DB_HOST'))
con <- dbConnect(PostgreSQL(),
    user=env.vars['DB_USER'],
    password=env.vars['DB_PW'],
    host=env.vars['DB_HOST'],
    dbname="arivale")
q <- paste(
    "select distinct o.observation_id, o.collection_date,",
    "        o.vendor_id, ",
    "        v.name, r.reviewed_at_utc, r.reviewed_by,",
    "        co.checked_out_at, co.checked_in_at",
    "from ops.review r",
    "    join analysis.observation o on r.observation_id=o.observation_id",
    "    join analysis.vendor v on o.vendor_id=v.vendor_id",
    "    left join ops.observation_checkout co on r.observation_id=co.observation_id",
    "where reviewed_at_utc >= '2017-05-01'",
    "  and (co.checked_out_role is null",
    "       or co.checked_out_role='External')",
    "order by reviewed_at_utc")
rs <- dbSendQuery(con, q)
df <- fetch(rs, n=-1)
dbDisconnect(con)

# convert timestamps to pacific time
df <- df %>% mutate( reviewed_at_pt=with_tz(as_datetime(reviewed_at_utc), 'America/Los_angeles') )

# trim rows prior to may 1st pacific time
df <- df[ df$reviewed_at_pt >= as_datetime('2017-05-01 00:00:00 -0800'), ]

# how long was the review checked out?
df <- df %>% mutate( checked_out_seconds=as.duration(checked_out_at %--% checked_in_at)/dseconds(1) )

write.table(df, '/Users/cbare/Desktop/external-reviews-since-may-2017.tsv', sep='\t', row.names=F)

# we're going to group by date
df <- df %>% mutate( p_date=as_date(pt) )

# counts will count reviews performed by each physician on each date for each vendor
counts <- df %>% group_by( vendor, p_date, reviewed_by ) %>% summarise( count=n() ) %>% spread(vendor, count, fill=0)  %>% arrange(reviewed_by, p_date)

write.table(counts, '/Users/cbare/Desktop/external-review-counts.tsv', sep='\t', row.names=F )