search

ICPI / tableau_training

June 2018 Tableau Training using ICPI Fact View Dataset
4 stars 0 forks source link

Code for reshaping dataset #8

Closed shapaklyak closed 6 years ago

shapaklyak commented 6 years ago

my attempt...with some help from @achafetz @kschlenker @noahbartlett pls take a look, thanks!

#import training dataset directly into R
df <- readr::read_tsv("https://raw.githubusercontent.com/ICPI/TrainingDataset/master/Output/ICPI_MER_Structured_TRAINING_Dataset_PSNU_IM_FY17-18_20180515_v1_1.txt")
View(df)

#reshape training dataset from wide to long, putting all results/targets into one column 
gather2.df <- gather(df, key= "period", "value", FY2017_TARGETS, FY2017Q1, FY2017Q2, FY2017Q3, FY2017Q4, FY2017APR, FY2018_TARGETS, FY2018Q1, FY2018Q2, na.rm=TRUE)

#Create separate column for labeling targets and results
#testing it out before applying to the dataset
gather2.df %>% mutate(Valuetype=ifelse(str_detect(period, "TARGETS"),"Targets", "Results"))
# apply to dataset
gather2.df <- gather2.df %>% mutate(Valuetype=ifelse(str_detect(period, "TARGETS"),"Targets", "Results"))

#Export dataset to csv or txt or xls for reading into Tableau
  write.csv(gather2.df,"trainingdataset.csv", row.names=FALSE)
shapaklyak commented 6 years ago

@kschlenker @noahbartlett please take a look when you have a chance. thanks!

shapaklyak commented 6 years ago

@bbaack here is the dataset structured using the above code. can refresh this with more comprehensive training dataset when available. (fyi @abrahamagedewICPI ) trainingdataset.xlsx

shapaklyak commented 6 years ago

Updated

#To reshape MER Structured Dataset from wide to long, using Site x IM training dataset
#import training dataset directly into R; change file name and update working directory as needed
df <- readr::read_tsv("ICPI_MER_Structured_Dataset_SITE_IM_Training_20180614.txt")
View(df)

#reshape training dataset from wide to long, putting all results/targets into one column 
library(tidyr)
gather2.df <- gather(df, key= "period", "value", FY2017_TARGETS, FY2017Q1, FY2017Q2, FY2017Q3, FY2017Q4, FY2017APR, FY2018_TARGETS, FY2018Q1, FY2018Q2, na.rm=TRUE)

#Create separate column for labeling targets and results
#testing it out before applying to the dataset
library(plyr)
library(stringr)
gather2.df %>% mutate(Valuetype=ifelse(str_detect(period, "TARGETS"),"Targets", "Results"))
# apply to dataset
gather2.df <- gather2.df %>% mutate(Valuetype=ifelse(str_detect(period, "TARGETS"),"Targets", "Results"))

View(gather2.df)
#Export dataset to csv or txt or xls for reading into Tableau
write.csv(gather2.df,"trainingdatasetsiteim.csv", row.names=FALSE)
shapaklyak commented 6 years ago

Code can be found here: https://github.com/ICPI/tableau_training/blob/master/Training%20materials/ReshapeCode.r

kschlenker commented 6 years ago

@shapaklyak Here is Noah's code to take PSNU x IM data long in R. This has been updated for Q2.

I haven't finished updating the code yet for the new data structure, but back in Q4 I took his PSNU x IM long code and then modified it a bit for my own purposes. I took it down to site level, left time periods long, and moved indicator/disaggs wide (specifically MCAD related to the clinical cascade). It's located here: https://github.com/ICPI/tableau_training/blob/master/Training%20materials/Site%20x%20IM%20Tableau%20Data_semi_wide%20-%20Finding%20Men%20Tool%20FY17%20Q4.R

shapaklyak commented 6 years ago

thanks @kschlenker ! i've moved this code to the Additional Resources Folder