collectOracle: Collect data from country segment predifine Rdata file, use...

In dbescond/Ariane: Ariane package internal functions for ILO Statistics department

Description

Collect data from country segment predifine Rdata file, use parallel approach

Usage

collectOracle(Query = NULL, Country = NULL, Internal = FALSE,
  pathfile = NULL)

Arguments

Query,	default NULL, could received dplyr query, see example.
Internal,	default FALSE, if TRUE collect usefull admin variable from Oracle.
Path,	set the string character for the path where data are available in rds files.

Author(s)

ILO bescond

Examples

################################## use to fetch data and metadata related

# Collect all ILOSTAT data
X <- collectOracle()

# Collect all ILOSTAT data with other meta info ( like Last_Status, Load_Mode, Qta_Check_Status, Qta_Check_User, Qta_Channel)
X <- collectOracle(Internal = FALSE)

##############
# Collect ILOSTAT data with filter, ie. select Annual main collection and short term main collection (excluded A from ST) 

X <- collectOracle(	Query = 
						"filter(Collection_Code %in% c('YI') ,
						!(Collection_Code %in% 'STI' & Freq_Code %in% 'A'))"
	)


##############
# Collect ILOSTAT data with filter and mutate and select

X <- collectOracle(	Query = 
						"filter(Collection_Code %in% c('STI')) %>%  #  	filter STI
			 			mutate(Collection_Code = 'Main') %>%		# 	set Collection_Code to 'Main'
			 			select(Country_Code, Indicator_Code) %>% 	#	Select only country and indicator
						distinct()"									# 	take unique combinaison
	)


##############
# Collect ILOSTAT data with minimum of columns need, delete some columns

X <- collectOracle(Query = 	
						"select(-Sex_Version_Code, -Classif1_Version_Code, -Classif2_Version_Code, -Classif3_Version_Code, -Classif4_Version_Code, -Classif5_Version_Code)"
	)


##############
# Collect ILOSTAT data with country 'AFG' only
# then prepare csv download format in English

X <- collectOracle(Query = "filter(Country_Code %in% 'AFG')")
X <- PrepareDorwnload(X, "en")
write.csv(X, "./my_file.csv", na = "")

or in csv upload format
X <- PrepareUpload(X)
write.csv(X, "my_file.csv", na = "", row.names = FALSE)
write.csv(X, gzfile("my_file.csv.gz"), na = "") # save in compress gz format
	
##############
## Advance summary table
# get count of statistics by Qta_Channel

X <- collectOracle("select(Qta_Channel) %>% group_by(Qta_Channel) %>% tally()", Internal=TRUE) # get summary by country
X <- X %>% group_by(Qta_Channel) %>% tally() %>% ungroup # get summary for all

# first plot
X %>% arrange(Qta_Channel)  %>% mutate(n = n/sum(n) * 100)%>%
ggplot() +   geom_bar(mapping = aes(x = Qta_Channel, fill = Qta_Channel, y = n), stat = "identity")  +   coord_polar()

# idem by user / channel

X <- collectOracle("select(Qta_Channel, Qta_Check_User) %>% group_by(Qta_Channel, Qta_Check_User) %>% tally()", Internal=TRUE) # get summary by country
X <- X %>% group_by(Qta_Channel, Qta_Check_User) %>% tally() %>% ungroup # get summary for all
spread(X, Qta_Channel,n) # cross tabulation

# then plot result
require(ggplot2)
X %>% arrange(Qta_Channel)  %>% mutate(n = n/sum(n) * 100) %>%
ggplot() +   geom_bar(mapping = aes(x = Qta_Check_User, fill = Qta_Channel, y = n), stat = "identity")

# plot with facet 
X %>% arrange(Qta_Check_User,Qta_Channel)  %>% mutate(Percent = n/sum(n) * 100)%>%
ggplot() +   geom_bar(mapping = aes(x = Qta_Channel, fill = Qta_Channel, y = Percent), stat = "identity")  +   coord_polar() + 
facet_wrap( ~ Qta_Check_User)

# View table on html page for small table only, even if fix(X)
X %>% mutate_each(funs(factor), -n) %>% DT

dbescond/Ariane documentation built on Dec. 10, 2019, 10:01 a.m.