2018-06-23 16:27:33 2018-06-23 16:27:33 R version 3.5.0 (2018-04-23) -- "Joy in Playing" 2018-06-23 16:27:33 Copyright (C) 2018 The R Foundation for Statistical Computing 2018-06-23 16:27:33 Platform: x86_64-w64-mingw32/x64 (64-bit) 2018-06-23 16:27:33 2018-06-23 16:27:33 R is free software and comes with ABSOLUTELY NO WARRANTY. 2018-06-23 16:27:33 You are welcome to redistribute it under certain conditions. 2018-06-23 16:27:33 Type 'license()' or 'licence()' for distribution details. 2018-06-23 16:27:33 2018-06-23 16:27:33 Natural language support but running in an English locale 2018-06-23 16:27:33 2018-06-23 16:27:33 R is a collaborative project with many contributors. 2018-06-23 16:27:33 Type 'contributors()' for more information and 2018-06-23 16:27:33 'citation()' on how to cite R or R packages in publications. 2018-06-23 16:27:33 2018-06-23 16:27:33 Type 'demo()' for some demos, 'help()' for on-line help, or 2018-06-23 16:27:33 'help.start()' for an HTML browser interface to help. 2018-06-23 16:27:33 Type 'q()' to quit R. 2018-06-23 16:27:33 2018-06-23 16:27:33 > setwd( Sys.getenv( "RWD" ) ) 2018-06-23 16:27:33 > 2018-06-23 16:27:33 > machine_specific_replacements <- 2018-06-23 16:27:33 + list( 2018-06-23 16:27:33 + 2018-06-23 16:27:33 + # replace the folder path on macnix 2018-06-23 16:27:33 + c( 'path.expand( \"~\" ) , \"NPPES\"' , paste0( '"' , getwd() , '"' ) ) , 2018-06-23 16:27:33 + 2018-06-23 16:27:33 + # change other things in the script to be run 2018-06-23 16:27:33 + c( "hello" , "howdy" ) 2018-06-23 16:27:33 + 2018-06-23 16:27:33 + ) 2018-06-23 16:27:33 > 2018-06-23 16:27:33 > source( lodown::syntaxtractor( "nppes" , replacements = machine_specific_replacements , setup_test = "setup" ) , echo = TRUE ) 2018-06-23 16:27:35 2018-06-23 16:27:35 > library(lodown) 2018-06-23 16:27:35 2018-06-23 16:27:35 > lodown("nppes", output_dir = file.path("F:/r-wd/nppes-1529763305")) 2018-06-23 16:27:35 building catalog for nppes 2018-06-23 16:27:35 2018-06-23 16:27:35 locally downloading nppes 2018-06-23 16:27:35 2018-06-23 16:27:35 'http://download.cms.gov/nppes/NPPES_Data_Dissemination_June_2018.zip' 2018-06-23 16:27:35 cached in 2018-06-23 16:27:35 'E:/eanthony/r-tmp/nppes/533146b275a48ed1d30a42971805c8b1.Rcache' 2018-06-23 16:27:35 copying to 2018-06-23 16:27:35 'E:\eanthony\r-tmp\nppes\RtmpGkkHrs\file2c7c2b6ffde' 2018-06-23 16:27:35 2018-06-23 16:34:57 QQ: 'CREATE TABLE npi (npi STRING, entity_type_code DOUBLE PRECISION, replacement_npi STRING, employer_identification_number__ein_ STRING, provider_organization_name__legal_business_name_ STRING, provider_last_name__legal_name_ STRING, provider_first_name STRING, provider_middle_name STRING, provider_name_prefix_text STRING, provider_name_suffix_text STRING, provider_credential_text STRING, provider_other_organization_name STRING, provider_other_organization_name_type_code DOUBLE PRECISION, provider_other_last_name STRING, provider_other_first_name STRING, provider_other_middle_name STRING, provider_other_name_prefix_text STRING, provider_other_name_suffix_text STRING, provider_other_credential_text STRING, provider_other_last_name_type_code DOUBLE PRECISION, provider_first_line_business_mailing_address STRING, provider_second_line_business_mailing_address STRING, provider_business_mailing_address_city_name STRING, provider_business_mailing_address_state_name STRING, provider_business_mailing_address_postal_code STRING, provider_business_mailing_address_country_code__if_outside_u_s__ STRING, provider_business_mailing_address_telephone_number STRING, provider_business_mailing_address_fax_number STRING, provider_first_line_business_practice_location_address STRING, provider_second_line_business_practice_location_address STRING, provider_business_practice_location_address_city_name STRING, provider_business_practice_location_address_state_name STRING, provider_business_practice_location_address_postal_code STRING, provider_business_practice_location_address_country_code__if_outside_u_s__ STRING, provider_business_practice_location_address_telephone_number STRING, provider_business_practice_location_address_fax_number STRING, provider_enumeration_date STRING, last_update_date STRING, npi_deactivation_reason_code DOUBLE PRECISION, npi_deactivation_date STRING, npi_reactivation_date STRING, provider_gender_code STRING, authorized_official_last_name STRING, authorized_official_first_name STRING, authorized_official_middle_name STRING, authorized_official_title_or_position STRING, authorized_official_telephone_number STRING, healthcare_provider_taxonomy_code_1 STRING, provider_license_number_1 STRING, provider_license_number_state_code_1 STRING, healthcare_provider_primary_taxonomy_switch_1 STRING, healthcare_provider_taxonomy_code_2 STRING, provider_license_number_2 STRING, provider_license_number_state_code_2 STRING, healthcare_provider_primary_taxonomy_switch_2 STRING, healthcare_provider_taxonomy_code_3 STRING, provider_license_number_3 STRING, provider_license_number_state_code_3 STRING, healthcare_provider_primary_taxonomy_switch_3 STRING, healthcare_provider_taxonomy_code_4 STRING, provider_license_number_4 STRING, provider_license_number_state_code_4 STRING, healthcare_provider_primary_taxonomy_switch_4 STRING, healthcare_provider_taxonomy_code_5 STRING, provider_license_number_5 STRING, provider_license_number_state_code_5 STRING, healthcare_provider_primary_taxonomy_switch_5 STRING, healthcare_provider_taxonomy_code_6 STRING, provider_license_number_6 STRING, provider_license_number_state_code_6 STRING, healthcare_provider_primary_taxonomy_switch_6 STRING, healthcare_provider_taxonomy_code_7 STRING, provider_license_number_7 STRING, provider_license_number_state_code_7 STRING, healthcare_provider_primary_taxonomy_switch_7 STRING, healthcare_provider_taxonomy_code_8 STRING, provider_license_number_8 STRING, provider_license_number_state_code_8 STRING, healthcare_provider_primary_taxonomy_switch_8 STRING, healthcare_provider_taxonomy_code_9 STRING, provider_license_number_9 STRING, provider_license_number_state_code_9 STRING, healthcare_provider_primary_taxonomy_switch_9 STRING, healthcare_provider_taxonomy_code_10 STRING, provider_license_number_10 STRING, provider_license_number_state_code_10 STRING, healthcare_provider_primary_taxonomy_switch_10 STRING, healthcare_provider_taxonomy_code_11 STRING, provider_license_number_11 STRING, provider_license_number_state_code_11 STRING, healthcare_provider_primary_taxonomy_switch_11 STRING, healthcare_provider_taxonomy_code_12 STRING, provider_license_number_12 STRING, provider_license_number_state_code_12 STRING, healthcare_provider_primary_taxonomy_switch_12 STRING, healthcare_provider_taxonomy_code_13 STRING, provider_license_number_13 STRING, provider_license_number_state_code_13 STRING, healthcare_provider_primary_taxonomy_switch_13 STRING, healthcare_provider_taxonomy_code_14 STRING, provider_license_number_14 STRING, provider_license_number_state_code_14 STRING, healthcare_provider_primary_taxonomy_switch_14 STRING, healthcare_provider_taxonomy_code_15 STRING, provider_license_number_15 STRING, provider_license_number_state_code_15 STRING, healthcare_provider_primary_taxonomy_switch_15 STRING, other_provider_identifier_1 STRING, other_provider_identifier_type_code_1 DOUBLE PRECISION, other_provider_identifier_state_1 STRING, other_provider_identifier_issuer_1 STRING, other_provider_identifier_2 STRING, other_provider_identifier_type_code_2 DOUBLE PRECISION, other_provider_identifier_state_2 STRING, other_provider_identifier_issuer_2 STRING, other_provider_identifier_3 STRING, other_provider_identifier_type_code_3 DOUBLE PRECISION, other_provider_identifier_state_3 STRING, other_provider_identifier_issuer_3 STRING, other_provider_identifier_4 STRING, other_provider_identifier_type_code_4 DOUBLE PRECISION, other_provider_identifier_state_4 STRING, other_provider_identifier_issuer_4 STRING, other_provider_identifier_5 STRING, other_provider_identifier_type_code_5 DOUBLE PRECISION, other_provider_identifier_state_5 STRING, other_provider_identifier_issuer_5 STRING, other_provider_identifier_6 STRING, other_provider_identifier_type_code_6 DOUBLE PRECISION, other_provider_identifier_state_6 STRING, other_provider_identifier_issuer_6 STRING, other_provider_identifier_7 STRING, other_provider_identifier_type_code_7 DOUBLE PRECISION, other_provider_identifier_state_7 STRING, other_provider_identifier_issuer_7 STRING, other_provider_identifier_8 STRING, other_provider_identifier_type_code_8 DOUBLE PRECISION, other_provider_identifier_state_8 STRING, other_provider_identifier_issuer_8 STRING, other_provider_identifier_9 STRING, other_provider_identifier_type_code_9 DOUBLE PRECISION, other_provider_identifier_state_9 STRING, other_provider_identifier_issuer_9 STRING, other_provider_identifier_10 STRING, other_provider_identifier_type_code_10 DOUBLE PRECISION, other_provider_identifier_state_10 STRING, other_provider_identifier_issuer_10 STRING, other_provider_identifier_11 STRING, other_provider_identifier_type_code_11 DOUBLE PRECISION, other_provider_identifier_state_11 STRING, other_provider_identifier_issuer_11 STRING, other_provider_identifier_12 STRING, other_provider_identifier_type_code_12 DOUBLE PRECISION, other_provider_identifier_state_12 STRING, other_provider_identifier_issuer_12 STRING, other_provider_identifier_13 STRING, other_provider_identifier_type_code_13 DOUBLE PRECISION, other_provider_identifier_state_13 STRING, other_provider_identifier_issuer_13 STRING, other_provider_identifier_14 STRING, other_provider_identifier_type_code_14 DOUBLE PRECISION, other_provider_identifier_state_14 STRING, other_provider_identifier_issuer_14 STRING, other_provider_identifier_15 STRING, other_provider_identifier_type_code_15 DOUBLE PRECISION, other_provider_identifier_state_15 STRING, other_provider_identifier_issuer_15 STRING, other_provider_identifier_16 STRING, other_provider_identifier_type_code_16 DOUBLE PRECISION, other_provider_identifier_state_16 STRING, other_provider_identifier_issuer_16 STRING, other_provider_identifier_17 STRING, other_provider_identifier_type_code_17 DOUBLE PRECISION, other_provider_identifier_state_17 STRING, other_provider_identifier_issuer_17 STRING, other_provider_identifier_18 STRING, other_provider_identifier_type_code_18 DOUBLE PRECISION, other_provider_identifier_state_18 STRING, other_provider_identifier_issuer_18 STRING, other_provider_identifier_19 STRING, other_provider_identifier_type_code_19 DOUBLE PRECISION, other_provider_identifier_state_19 STRING, other_provider_identifier_issuer_19 STRING, other_provider_identifier_20 STRING, other_provider_identifier_type_code_20 DOUBLE PRECISION, other_provider_identifier_state_20 STRING, other_provider_identifier_issuer_20 STRING, other_provider_identifier_21 STRING, other_provider_identifier_type_code_21 DOUBLE PRECISION, other_provider_identifier_state_21 STRING, other_provider_identifier_issuer_21 STRING, other_provider_identifier_22 STRING, other_provider_identifier_type_code_22 DOUBLE PRECISION, other_provider_identifier_state_22 STRING, other_provider_identifier_issuer_22 STRING, other_provider_identifier_23 STRING, other_provider_identifier_type_code_23 DOUBLE PRECISION, other_provider_identifier_state_23 STRING, other_provider_identifier_issuer_23 STRING, other_provider_identifier_24 STRING, other_provider_identifier_type_code_24 DOUBLE PRECISION, other_provider_identifier_state_24 STRING, other_provider_identifier_issuer_24 STRING, other_provider_identifier_25 STRING, other_provider_identifier_type_code_25 DOUBLE PRECISION, other_provider_identifier_state_25 STRING, other_provider_identifier_issuer_25 STRING, other_provider_identifier_26 STRING, other_provider_identifier_type_code_26 DOUBLE PRECISION, other_provider_identifier_state_26 STRING, other_provider_identifier_issuer_26 STRING, other_provider_identifier_27 STRING, other_provider_identifier_type_code_27 DOUBLE PRECISION, other_provider_identifier_state_27 STRING, other_provider_identifier_issuer_27 STRING, other_provider_identifier_28 STRING, other_provider_identifier_type_code_28 DOUBLE PRECISION, other_provider_identifier_state_28 STRING, other_provider_identifier_issuer_28 STRING, other_provider_identifier_29 STRING, other_provider_identifier_type_code_29 DOUBLE PRECISION, other_provider_identifier_state_29 STRING, other_provider_identifier_issuer_29 STRING, other_provider_identifier_30 STRING, other_provider_identifier_type_code_30 DOUBLE PRECISION, other_provider_identifier_state_30 STRING, other_provider_identifier_issuer_30 STRING, other_provider_identifier_31 STRING, other_provider_identifier_type_code_31 DOUBLE PRECISION, other_provider_identifier_state_31 STRING, other_provider_identifier_issuer_31 STRING, other_provider_identifier_32 STRING, other_provider_identifier_type_code_32 DOUBLE PRECISION, other_provider_identifier_state_32 STRING, other_provider_identifier_issuer_32 STRING, other_provider_identifier_33 STRING, other_provider_identifier_type_code_33 DOUBLE PRECISION, other_provider_identifier_state_33 STRING, other_provider_identifier_issuer_33 STRING, other_provider_identifier_34 STRING, other_provider_identifier_type_code_34 DOUBLE PRECISION, other_provider_identifier_state_34 STRING, other_provider_identifier_issuer_34 STRING, other_provider_identifier_35 STRING, other_provider_identifier_type_code_35 DOUBLE PRECISION, other_provider_identifier_state_35 STRING, other_provider_identifier_issuer_35 STRING, other_provider_identifier_36 STRING, other_provider_identifier_type_code_36 DOUBLE PRECISION, other_provider_identifier_state_36 STRING, other_provider_identifier_issuer_36 STRING, other_provider_identifier_37 STRING, other_provider_identifier_type_code_37 DOUBLE PRECISION, other_provider_identifier_state_37 STRING, other_provider_identifier_issuer_37 STRING, other_provider_identifier_38 STRING, other_provider_identifier_type_code_38 DOUBLE PRECISION, other_provider_identifier_state_38 STRING, other_provider_identifier_issuer_38 STRING, other_provider_identifier_39 STRING, other_provider_identifier_type_code_39 DOUBLE PRECISION, other_provider_identifier_state_39 STRING, other_provider_identifier_issuer_39 STRING, other_provider_identifier_40 STRING, other_provider_identifier_type_code_40 DOUBLE PRECISION, other_provider_identifier_state_40 STRING, other_provider_identifier_issuer_40 STRING, other_provider_identifier_41 STRING, other_provider_identifier_type_code_41 DOUBLE PRECISION, other_provider_identifier_state_41 STRING, other_provider_identifier_issuer_41 STRING, other_provider_identifier_42 STRING, other_provider_identifier_type_code_42 DOUBLE PRECISION, other_provider_identifier_state_42 STRING, other_provider_identifier_issuer_42 STRING, other_provider_identifier_43 STRING, other_provider_identifier_type_code_43 DOUBLE PRECISION, other_provider_identifier_state_43 STRING, other_provider_identifier_issuer_43 STRING, other_provider_identifier_44 STRING, other_provider_identifier_type_code_44 DOUBLE PRECISION, other_provider_identifier_state_44 STRING, other_provider_identifier_issuer_44 STRING, other_provider_identifier_45 STRING, other_provider_identifier_type_code_45 DOUBLE PRECISION, other_provider_identifier_state_45 STRING, other_provider_identifier_issuer_45 STRING, other_provider_identifier_46 STRING, other_provider_identifier_type_code_46 DOUBLE PRECISION, other_provider_identifier_state_46 STRING, other_provider_identifier_issuer_46 STRING, other_provider_identifier_47 STRING, other_provider_identifier_type_code_47 DOUBLE PRECISION, other_provider_identifier_state_47 STRING, other_provider_identifier_issuer_47 STRING, other_provider_identifier_48 STRING, other_provider_identifier_type_code_48 DOUBLE PRECISION, other_provider_identifier_state_48 STRING, other_provider_identifier_issuer_48 STRING, other_provider_identifier_49 STRING, other_provider_identifier_type_code_49 DOUBLE PRECISION, other_provider_identifier_state_49 STRING, other_provider_identifier_issuer_49 STRING, other_provider_identifier_50 STRING, other_provider_identifier_type_code_50 DOUBLE PRECISION, other_provider_identifier_state_50 STRING, other_provider_identifier_issuer_50 STRING, is_sole_proprietor STRING, is_organization_subpart STRING, parent_organization_lbn STRING, parent_organization_tin STRING, authorized_official_name_prefix_text STRING, authorized_official_name_suffix_text STRING, authorized_official_credential_text STRING, healthcare_provider_taxonomy_group_1 STRING, healthcare_provider_taxonomy_group_2 STRING, healthcare_provider_taxonomy_group_3 STRING, healthcare_provider_taxonomy_group_4 STRING, healthcare_provider_taxonomy_group_5 STRING, healthcare_provider_taxonomy_group_6 STRING, healthcare_provider_taxonomy_group_7 STRING, healthcare_provider_taxonomy_group_8 STRING, healthcare_provider_taxonomy_group_9 STRING, healthcare_provider_taxonomy_group_10 STRING, healthcare_provider_taxonomy_group_11 STRING, healthcare_provider_taxonomy_group_12 STRING, healthcare_provider_taxonomy_group_13 STRING, healthcare_provider_taxonomy_group_14 STRING, healthcare_provider_taxonomy_group_15 STRING)' 2018-06-23 16:34:58 II: Finished in 1.48s 2018-06-23 16:44:29 QQ: 'copy 5617670 offset 2 records into npi from 'E:\eanthony\r-tmp\nppes\RtmpGkkHrs\file2c7c63fd4023' using delimiters ',','\n','"' NULL as ''' 2018-06-23 17:15:28 II: Finished in 30.98s 2018-06-23 17:15:28 QQ: 'SELECT COUNT(*) FROM npi' 2018-06-23 17:15:28 II: Finished in 0.06s 2018-06-23 17:15:39 nppes local download completed 2018-06-23 17:15:39 2018-06-23 17:15:39 Warning message: 2018-06-23 17:15:39 In file.remove(unzipped_files, tf, tf2) : 2018-06-23 17:15:39 cannot remove file 'E:\eanthony\r-tmp\nppes\RtmpGkkHrs\file2c7c2b6ffde', reason 'No such file or directory' 2018-06-23 17:15:39 > setup return code=0 2018-06-23 17:15:39 2018-06-23 17:15:39 R version 3.5.0 (2018-04-23) -- "Joy in Playing" 2018-06-23 17:15:39 Copyright (C) 2018 The R Foundation for Statistical Computing 2018-06-23 17:15:39 Platform: x86_64-w64-mingw32/x64 (64-bit) 2018-06-23 17:15:39 2018-06-23 17:15:39 R is free software and comes with ABSOLUTELY NO WARRANTY. 2018-06-23 17:15:39 You are welcome to redistribute it under certain conditions. 2018-06-23 17:15:39 Type 'license()' or 'licence()' for distribution details. 2018-06-23 17:15:39 2018-06-23 17:15:39 Natural language support but running in an English locale 2018-06-23 17:15:39 2018-06-23 17:15:39 R is a collaborative project with many contributors. 2018-06-23 17:15:39 Type 'contributors()' for more information and 2018-06-23 17:15:39 'citation()' on how to cite R or R packages in publications. 2018-06-23 17:15:39 2018-06-23 17:15:39 Type 'demo()' for some demos, 'help()' for on-line help, or 2018-06-23 17:15:39 'help.start()' for an HTML browser interface to help. 2018-06-23 17:15:39 Type 'q()' to quit R. 2018-06-23 17:15:39 2018-06-23 17:15:39 > setwd( Sys.getenv( "RWD" ) ) 2018-06-23 17:15:39 > 2018-06-23 17:15:39 > machine_specific_replacements <- 2018-06-23 17:15:39 + list( 2018-06-23 17:15:39 + 2018-06-23 17:15:39 + # replace the folder path on macnix 2018-06-23 17:15:39 + c( 'path.expand( \"~\" ) , \"NPPES\"' , paste0( '"' , getwd() , '"' ) ) , 2018-06-23 17:15:39 + 2018-06-23 17:15:39 + # change other things in the script to be run 2018-06-23 17:15:39 + c( "hello" , "howdy" ) 2018-06-23 17:15:39 + 2018-06-23 17:15:39 + ) 2018-06-23 17:15:39 > 2018-06-23 17:15:39 > source( lodown::syntaxtractor( "nppes" , replacements = machine_specific_replacements , setup_test = "test" ) , echo = TRUE ) 2018-06-23 17:15:41 2018-06-23 17:15:41 > library(DBI) 2018-06-23 17:15:41 2018-06-23 17:15:41 > dbdir <- file.path("F:/r-wd/nppes-1529763305", "MonetDB") 2018-06-23 17:15:41 2018-06-23 17:15:41 > db <- dbConnect(MonetDBLite::MonetDBLite(), dbdir) 2018-06-23 17:15:48 2018-06-23 17:15:48 > dbSendQuery(db, "ALTER TABLE npi ADD COLUMN individual INTEGER") 2018-06-23 17:15:48 QQ: 'ALTER TABLE npi ADD COLUMN individual INTEGER' 2018-06-23 17:16:02 II: Finished in 14.61s 2018-06-23 17:16:02 2018-06-23 17:16:02 SQL ALTER TABLE npi ADD COLUMN individual INTEGER 2018-06-23 17:16:02 ROWS Fetched: 0 [complete] 2018-06-23 17:16:02 Changed: 0 2018-06-23 17:16:02 2018-06-23 17:16:02 > dbSendQuery(db, "UPDATE npi \n\tSET individual = \n\t\tCASE WHEN entity_type_code = 1 THEN 1 ELSE 0 END") 2018-06-23 17:16:02 QQ: 'UPDATE npi 2018-06-23 17:16:02 SET individual = 2018-06-23 17:16:02 CASE WHEN entity_type_code = 1 THEN 1 ELSE 0 END' 2018-06-23 17:16:12 II: Finished in 9.19s 2018-06-23 17:16:12 2018-06-23 17:16:12 SQL UPDATE npi 2018-06-23 17:16:12 SET individual = 2018-06-23 17:16:12 CASE WHEN entity_type_code = 1 THEN 1 ELSE 0 END 2018-06-23 17:16:12 ROWS Fetched: 0 [complete] 2018-06-23 17:16:12 Changed: 5617669 2018-06-23 17:16:12 2018-06-23 17:16:12 > dbSendQuery(db, "ALTER TABLE npi ADD COLUMN provider_enumeration_year INTEGER") 2018-06-23 17:16:12 QQ: 'ALTER TABLE npi ADD COLUMN provider_enumeration_year INTEGER' 2018-06-23 17:16:23 II: Finished in 11.5s 2018-06-23 17:16:23 2018-06-23 17:16:23 SQL ALTER TABLE npi ADD COLUMN provider_enumeration_year INTEGER 2018-06-23 17:16:23 ROWS Fetched: 0 [complete] 2018-06-23 17:16:23 Changed: 5617669 2018-06-23 17:16:23 2018-06-23 17:16:23 > dbSendQuery(db, "UPDATE npi \n\tSET provider_enumeration_year = \n\t\tCAST( SUBSTRING( provider_enumeration_date , 7 , 10 ) AS INTEGER )") 2018-06-23 17:16:23 QQ: 'UPDATE npi 2018-06-23 17:16:23 SET provider_enumeration_year = 2018-06-23 17:16:23 CAST( SUBSTRING( provider_enumeration_date , 7 , 10 ) AS INTEGER )' 2018-06-23 17:16:29 II: Finished in 5.72s 2018-06-23 17:16:29 2018-06-23 17:16:29 SQL UPDATE npi 2018-06-23 17:16:29 SET provider_enumeration_year = 2018-06-23 17:16:29 CAST( SUBSTRING( provider_enumeration_date , 7 , 10 ) AS INTEGER ) 2018-06-23 17:16:29 ROWS Fetched: 0 [complete] 2018-06-23 17:16:29 Changed: 5617669 2018-06-23 17:16:29 2018-06-23 17:16:29 > dbGetQuery(db, "SELECT COUNT(*) FROM npi") 2018-06-23 17:16:29 QQ: 'SELECT COUNT(*) FROM npi' 2018-06-23 17:16:29 II: Finished in 0.05s 2018-06-23 17:16:29 L3 2018-06-23 17:16:29 1 5617669 2018-06-23 17:16:29 2018-06-23 17:16:29 > dbGetQuery(db, "SELECT\n\t\tprovider_gender_code ,\n\t\tCOUNT(*) \n\tFROM npi\n\tGROUP BY provider_gender_code") 2018-06-23 17:16:29 QQ: 'SELECT 2018-06-23 17:16:29 provider_gender_code , 2018-06-23 17:16:29 COUNT(*) 2018-06-23 17:16:29 FROM npi 2018-06-23 17:16:29 GROUP BY provider_gender_code' 2018-06-23 17:16:29 II: Finished in 0.11s 2018-06-23 17:16:29 provider_gender_code L3 2018-06-23 17:16:29 1 M 1621361 2018-06-23 17:16:29 2 1404129 2018-06-23 17:16:29 3 F 2592179 2018-06-23 17:16:29 2018-06-23 17:16:29 > dbGetQuery(db, "SELECT AVG( provider_enumeration_year ) FROM npi") 2018-06-23 17:16:29 QQ: 'SELECT AVG( provider_enumeration_year ) FROM npi' 2018-06-23 17:16:29 II: Finished in 0.08s 2018-06-23 17:16:29 L3 2018-06-23 17:16:29 1 2010.065 2018-06-23 17:16:29 2018-06-23 17:16:29 > dbGetQuery(db, "SELECT \n\t\tprovider_gender_code , \n\t\tAVG( provider_enumeration_year ) AS mean_provider_enumeration_year\n\tFROM npi \n\tGROUP B ..." ... [TRUNCATED] 2018-06-23 17:16:29 QQ: 'SELECT 2018-06-23 17:16:29 provider_gender_code , 2018-06-23 17:16:29 AVG( provider_enumeration_year ) AS mean_provider_enumeration_year 2018-06-23 17:16:29 FROM npi 2018-06-23 17:16:29 GROUP BY provider_gender_code' 2018-06-23 17:16:29 II: Finished in 0.14s 2018-06-23 17:16:29 provider_gender_code mean_provider_enumeration_year 2018-06-23 17:16:29 1 M 2008.947 2018-06-23 17:16:29 2 2009.884 2018-06-23 17:16:29 3 F 2010.853 2018-06-23 17:16:29 2018-06-23 17:16:29 > dbSendQuery(db, "CREATE FUNCTION \n\t\tdiv_noerror(l DOUBLE, r DOUBLE) \n\tRETURNS DOUBLE \n\tEXTERNAL NAME calc.div_noerror") 2018-06-23 17:16:29 QQ: 'CREATE FUNCTION 2018-06-23 17:16:29 div_noerror(l DOUBLE, r DOUBLE) 2018-06-23 17:16:29 RETURNS DOUBLE 2018-06-23 17:16:29 EXTERNAL NAME calc.div_noerror' 2018-06-23 17:16:30 II: Finished in 0.72s 2018-06-23 17:16:30 2018-06-23 17:16:30 SQL CREATE FUNCTION 2018-06-23 17:16:30 div_noerror(l DOUBLE, r DOUBLE) 2018-06-23 17:16:30 RETURNS DOUBLE 2018-06-23 17:16:30 EXTERNAL NAME calc.div_noerror 2018-06-23 17:16:30 ROWS Fetched: 0 [complete] 2018-06-23 17:16:30 Changed: 5617669 2018-06-23 17:16:30 2018-06-23 17:16:30 > dbGetQuery(db, "SELECT \n\t\tis_sole_proprietor , \n\t\tdiv_noerror( \n\t\t\tCOUNT(*) , \n\t\t\t( SELECT COUNT(*) FROM npi ) \n\t\t) AS share_is_sol ..." ... [TRUNCATED] 2018-06-23 17:16:30 QQ: 'SELECT 2018-06-23 17:16:30 is_sole_proprietor , 2018-06-23 17:16:30 div_noerror( 2018-06-23 17:16:30 COUNT(*) , 2018-06-23 17:16:30 ( SELECT COUNT(*) FROM npi ) 2018-06-23 17:16:30 ) AS share_is_sole_proprietor 2018-06-23 17:16:30 FROM npi 2018-06-23 17:16:30 GROUP BY is_sole_proprietor' 2018-06-23 17:16:30 II: Finished in 0.14s 2018-06-23 17:16:30 is_sole_proprietor share_is_sole_proprietor 2018-06-23 17:16:30 1 X 0.02016441 2018-06-23 17:16:30 2 N 0.48076880 2018-06-23 17:16:30 3 0.24994869 2018-06-23 17:16:30 4 Y 0.24911810 2018-06-23 17:16:30 2018-06-23 17:16:30 > dbGetQuery(db, "SELECT SUM( provider_enumeration_year ) FROM npi") 2018-06-23 17:16:30 QQ: 'SELECT SUM( provider_enumeration_year ) FROM npi' 2018-06-23 17:16:30 II: Finished in 0.05s 2018-06-23 17:16:30 L3 2018-06-23 17:16:30 1 11041932744 2018-06-23 17:16:30 2018-06-23 17:16:30 > dbGetQuery(db, "SELECT \n\t\tprovider_gender_code , \n\t\tSUM( provider_enumeration_year ) AS sum_provider_enumeration_year \n\tFROM npi \n\tGROUP B ..." ... [TRUNCATED] 2018-06-23 17:16:30 QQ: 'SELECT 2018-06-23 17:16:30 provider_gender_code , 2018-06-23 17:16:30 SUM( provider_enumeration_year ) AS sum_provider_enumeration_year 2018-06-23 17:16:30 FROM npi 2018-06-23 17:16:30 GROUP BY provider_gender_code' 2018-06-23 17:16:30 II: Finished in 0.16s 2018-06-23 17:16:30 provider_gender_code sum_provider_enumeration_year 2018-06-23 17:16:30 1 M 3257227723 2018-06-23 17:16:30 2 2572213516 2018-06-23 17:16:30 3 F 5212491505 2018-06-23 17:16:30 2018-06-23 17:16:30 > dbGetQuery(db, "SELECT QUANTILE( provider_enumeration_year , 0.5 ) FROM npi") 2018-06-23 17:16:30 QQ: 'SELECT QUANTILE( provider_enumeration_year , 0.5 ) FROM npi' 2018-06-23 17:16:31 II: Finished in 0.33s 2018-06-23 17:16:31 L3 2018-06-23 17:16:31 1 2009 2018-06-23 17:16:31 2018-06-23 17:16:31 > dbGetQuery(db, "SELECT \n\t\tprovider_gender_code , \n\t\tQUANTILE( provider_enumeration_year , 0.5 ) AS median_provider_enumeration_year\n\tFROM np ..." ... [TRUNCATED] 2018-06-23 17:16:31 QQ: 'SELECT 2018-06-23 17:16:31 provider_gender_code , 2018-06-23 17:16:31 QUANTILE( provider_enumeration_year , 0.5 ) AS median_provider_enumeration_year 2018-06-23 17:16:31 FROM npi 2018-06-23 17:16:31 GROUP BY provider_gender_code' 2018-06-23 17:16:31 II: Finished in 0.59s 2018-06-23 17:16:31 provider_gender_code median_provider_enumeration_year 2018-06-23 17:16:31 1 M 2007 2018-06-23 17:16:31 2 2008 2018-06-23 17:16:31 3 F 2011 2018-06-23 17:16:31 2018-06-23 17:16:31 > dbGetQuery(db, "SELECT\n\t\tAVG( provider_enumeration_year )\n\tFROM npi\n\tWHERE provider_business_practice_location_address_state_name = 'CA'") 2018-06-23 17:16:31 QQ: 'SELECT 2018-06-23 17:16:31 AVG( provider_enumeration_year ) 2018-06-23 17:16:31 FROM npi 2018-06-23 17:16:31 WHERE provider_business_practice_location_address_state_name = 'CA'' 2018-06-23 17:16:31 II: Finished in 0.22s 2018-06-23 17:16:31 L3 2018-06-23 17:16:31 1 2010.123 2018-06-23 17:16:31 2018-06-23 17:16:31 > dbGetQuery(db, "SELECT \n\t\tVAR_SAMP( provider_enumeration_year ) , \n\t\tSTDDEV_SAMP( provider_enumeration_year ) \n\tFROM npi") 2018-06-23 17:16:31 QQ: 'SELECT 2018-06-23 17:16:31 VAR_SAMP( provider_enumeration_year ) , 2018-06-23 17:16:31 STDDEV_SAMP( provider_enumeration_year ) 2018-06-23 17:16:31 FROM npi' 2018-06-23 17:16:32 II: Finished in 0.61s 2018-06-23 17:16:32 L3 L5 2018-06-23 17:16:32 1 16.6558 4.081151 2018-06-23 17:16:32 2018-06-23 17:16:32 > dbGetQuery(db, "SELECT \n\t\tprovider_gender_code , \n\t\tVAR_SAMP( provider_enumeration_year ) AS var_provider_enumeration_year ,\n\t\tSTDDEV_SAMP( ..." ... [TRUNCATED] 2018-06-23 17:16:32 QQ: 'SELECT 2018-06-23 17:16:32 provider_gender_code , 2018-06-23 17:16:32 VAR_SAMP( provider_enumeration_year ) AS var_provider_enumeration_year , 2018-06-23 17:16:32 STDDEV_SAMP( provider_enumeration_year ) AS stddev_provider_enumeration_year 2018-06-23 17:16:32 FROM npi 2018-06-23 17:16:32 GROUP BY provider_gender_code' 2018-06-23 17:16:32 II: Finished in 0.16s 2018-06-23 17:16:32 provider_gender_code var_provider_enumeration_year 2018-06-23 17:16:32 1 M 15.33993 2018-06-23 17:16:32 2 15.26740 2018-06-23 17:16:32 3 F 16.74456 2018-06-23 17:16:32 stddev_provider_enumeration_year 2018-06-23 17:16:32 1 3.916622 2018-06-23 17:16:32 2 3.907351 2018-06-23 17:16:32 3 4.092011 2018-06-23 17:16:32 2018-06-23 17:16:32 > dbGetQuery(db, "SELECT \n\t\tCORR( CAST( individual AS DOUBLE ) , CAST( provider_enumeration_year AS DOUBLE ) )\n\tFROM npi") 2018-06-23 17:16:32 QQ: 'SELECT 2018-06-23 17:16:32 CORR( CAST( individual AS DOUBLE ) , CAST( provider_enumeration_year AS DOUBLE ) ) 2018-06-23 17:16:32 FROM npi' 2018-06-23 17:16:33 II: Finished in 0.47s 2018-06-23 17:16:33 L5 2018-06-23 17:16:33 1 19.33672 2018-06-23 17:16:33 2018-06-23 17:16:33 > dbGetQuery(db, "SELECT \n\t\tprovider_gender_code , \n\t\tCORR( CAST( individual AS DOUBLE ) , CAST( provider_enumeration_year AS DOUBLE ) )\n\tFROM ..." ... [TRUNCATED] 2018-06-23 17:16:33 QQ: 'SELECT 2018-06-23 17:16:33 provider_gender_code , 2018-06-23 17:16:33 CORR( CAST( individual AS DOUBLE ) , CAST( provider_enumeration_year AS DOUBLE ) ) 2018-06-23 17:16:33 FROM npi 2018-06-23 17:16:33 GROUP BY provider_gender_code' 2018-06-23 17:16:33 II: Finished in 0.45s 2018-06-23 17:16:33 provider_gender_code L5 2018-06-23 17:16:33 1 M NA 2018-06-23 17:16:33 2 NA 2018-06-23 17:16:33 3 F NA 2018-06-23 17:16:33 2018-06-23 17:16:33 > library(dplyr) 2018-06-23 17:16:33 2018-06-23 17:16:33 Attaching package: 'dplyr' 2018-06-23 17:16:33 2018-06-23 17:16:33 The following objects are masked from 'package:stats': 2018-06-23 17:16:33 2018-06-23 17:16:33 filter, lag 2018-06-23 17:16:33 2018-06-23 17:16:33 The following objects are masked from 'package:base': 2018-06-23 17:16:33 2018-06-23 17:16:33 intersect, setdiff, setequal, union 2018-06-23 17:16:33 2018-06-23 17:16:33 2018-06-23 17:16:33 > dplyr_db <- MonetDBLite::src_monetdblite(dbdir) 2018-06-23 17:16:33 2018-06-23 17:16:33 > nppes_tbl <- tbl(dplyr_db, "npi") 2018-06-23 17:16:33 QQ: 'SELECT * 2018-06-23 17:16:33 FROM "npi" AS "zzz1" 2018-06-23 17:16:33 WHERE (0 = 1)' 2018-06-23 17:16:33 II: Finished in 0.09s 2018-06-23 17:16:33 2018-06-23 17:16:33 > nppes_tbl %>% summarize(mean = mean(provider_enumeration_year)) 2018-06-23 17:16:33 QQ: 'SELECT AVG("provider_enumeration_year") AS "mean" 2018-06-23 17:16:33 FROM "npi" 2018-06-23 17:16:33 LIMIT 10' 2018-06-23 17:16:33 II: Finished in 0.06s 2018-06-23 17:16:34 # Source: lazy query [?? x 1] 2018-06-23 17:16:34 # Database: MonetDBEmbeddedConnection 2018-06-23 17:16:34 mean 2018-06-23 17:16:34 2018-06-23 17:16:34 1 2010. 2018-06-23 17:16:34 2018-06-23 17:16:34 > nppes_tbl %>% group_by(provider_gender_code) %>% summarize(mean = mean(provider_enumeration_year)) 2018-06-23 17:16:34 QQ: 'SELECT "provider_gender_code", AVG("provider_enumeration_year") AS "mean" 2018-06-23 17:16:34 FROM "npi" 2018-06-23 17:16:34 GROUP BY "provider_gender_code" 2018-06-23 17:16:34 LIMIT 10' 2018-06-23 17:16:34 II: Finished in 0.11s 2018-06-23 17:16:34 # Source: lazy query [?? x 2] 2018-06-23 17:16:34 # Database: MonetDBEmbeddedConnection 2018-06-23 17:16:34 provider_gender_code mean 2018-06-23 17:16:34 2018-06-23 17:16:34 1 M 2009. 2018-06-23 17:16:34 2 2010. 2018-06-23 17:16:34 3 F 2011. 2018-06-23 17:16:34 2018-06-23 17:16:34 > dbGetQuery(db, "SELECT COUNT(*) FROM npi") 2018-06-23 17:16:34 QQ: 'SELECT COUNT(*) FROM npi' 2018-06-23 17:16:34 II: Finished in 0.09s 2018-06-23 17:16:34 L3 2018-06-23 17:16:34 1 5617669 2018-06-23 17:16:34 2018-06-23 17:16:34 > dbDisconnect(db, shutdown = TRUE) 2018-06-23 17:16:34 Warning messages: 2018-06-23 17:16:34 1: Translator is missing window variants of the following aggregate functions: 2018-06-23 17:16:34 * median 2018-06-23 17:16:34 2018-06-23 17:16:34 2: Missing values are always removed in SQL. 2018-06-23 17:16:34 Use `AVG(x, na.rm = TRUE)` to silence this warning 2018-06-23 17:16:34 3: Translator is missing window variants of the following aggregate functions: 2018-06-23 17:16:34 * median 2018-06-23 17:16:34 2018-06-23 17:16:34 4: Missing values are always removed in SQL. 2018-06-23 17:16:34 Use `AVG(x, na.rm = TRUE)` to silence this warning 2018-06-23 17:16:34 > test return code=0