Deriving CQC data set to send to Skills for Care

This chunk loads all the packages to use

library(tidyverse) # package for data cleaning and plotting

Data Preparation

First, import the sampled and coded data set

#import the raw data file
socialcare_raw <- read_csv("data/sample_new_cleaned.csv")

Assign orders to the ordinal level variables and name the organizational form in a reader-friendly way.

#select relevant columns, rename and relabel 
socialcare <- socialcare_raw %>% 
# recode legal form types to be more readable / easier to present
  mutate(form = case_when(form_num == 1 ~ "FPO",
                          form_num == 2 ~ "NPO",
                          form_num == 3 ~ "GOV",
                          form_num == 4 ~ "CIC",
                          form_num == 5 ~ "IND",
                          TRUE ~ NA_character_)) 

# show first several rows of the data set derived 
head(socialcare)

## # A tibble: 6 × 34
##    ...1 locati…¹ Locat…² Locat…³ Care …⁴ prima…⁵ Locat…⁶ Locat…⁷ Locat…⁸ Locat…⁹
##   <dbl> <chr>    <chr>   <chr>   <chr>   <chr>   <chr>   <chr>   <chr>   <chr>  
## 1     1 1-10000… VNH4P   69 Ten… N       Commun… 69 Ten… Warmsw… Doncas… DN4 9PE
## 2     2 1-10000… VNH4P   69 Ten… N       Commun… 69 Ten… Warmsw… Doncas… DN4 9PE
## 3     3 1-10000… VNH4P   69 Ten… N       Commun… 69 Ten… Warmsw… Doncas… DN4 9PE
## 4     4 1-10000… VNH4P   69 Ten… N       Commun… 69 Ten… Warmsw… Doncas… DN4 9PE
## 5     5 1-10000… VNH4P   69 Ten… N       Commun… 69 Ten… Warmsw… Doncas… DN4 9PE
## 6     6 1-10000… VNH4P   69 Ten… N       Commun… 69 Ten… Warmsw… Doncas… DN4 9PE
## # … with 24 more variables: `Location Local Authority` <chr>, region <chr>,
## #   `Location NHS Region` <chr>, `Location ONSPD CCG Code` <chr>,
## #   `Location ONSPD CCG` <chr>, `Location Commissioning CCG Code` <lgl>,
## #   `Location Commissioning CCG Name` <lgl>,
## #   `Service / Population Group` <chr>, domain <chr>, rating <chr>,
## #   publication_date <chr>, `Report Type` <chr>, inherited <chr>, URL <chr>,
## #   `Provider ID` <chr>, location_type <chr>, form_num <dbl>, …

#select useful columns
socialcare_cqc <- socialcare %>% 
  select(index,
         location_id,  location_name = `Location Name`,
         provider_id = `Provider ID`, provider_name, 
         form, 
         cic_type,
         care_home = `Care Home`,
         primary_cat, 
         region,
         service_group = `Service / Population Group`,
         domain, rating, publication_date, inherited)

# complete column names
colnames(socialcare)

##  [1] "...1"                            "location_id"                    
##  [3] "Location ODS Code"               "Location Name"                  
##  [5] "Care Home"                       "primary_cat"                    
##  [7] "Location Street Address"         "Location Address Line 2"        
##  [9] "Location City"                   "Location Post Code"             
## [11] "Location Local Authority"        "region"                         
## [13] "Location NHS Region"             "Location ONSPD CCG Code"        
## [15] "Location ONSPD CCG"              "Location Commissioning CCG Code"
## [17] "Location Commissioning CCG Name" "Service / Population Group"     
## [19] "domain"                          "rating"                         
## [21] "publication_date"                "Report Type"                    
## [23] "inherited"                       "URL"                            
## [25] "Provider ID"                     "location_type"                  
## [27] "form_num"                        "provider_name"                  
## [29] "Brand ID"                        "Brand Name"                     
## [31] "std_name"                        "cic_type"                       
## [33] "index"                           "form"

# trimmed column names
colnames(socialcare_cqc)

##  [1] "index"            "location_id"      "location_name"    "provider_id"     
##  [5] "provider_name"    "form"             "cic_type"         "care_home"       
##  [9] "primary_cat"      "region"           "service_group"    "domain"          
## [13] "rating"           "publication_date" "inherited"

End

end of document updated on 9/23/2022