Text Analytics for Structured Data

Introduction

A demonstration to use text analytics for structured data.

Organizations are increasing their data needs for analytics. Often times it calls for data sets collected outside of the organization. The policies used to maintain data integrity within the organization fall apart when it starts to use outside data sources.

This demo takes related data sets from two different goverment agencies and uses fuzzy matching to link records.

library(tidyverse)

package ‘tidyverse’ was built under R version 3.2.5Loading tidyverse: ggplot2
Loading tidyverse: tibble
Loading tidyverse: tidyr
Loading tidyverse: readr
Loading tidyverse: purrr
Loading tidyverse: dplyr
package ‘tibble’ was built under R version 3.2.5package ‘tidyr’ was built under R version 3.2.5package ‘purrr’ was built under R version 3.2.5package ‘dplyr’ was built under R version 3.2.5Conflicts with tidy packages --------------------------------------------------
filter(): dplyr, stats
lag():    dplyr, stats

library(stringdist)

package ‘stringdist’ was built under R version 3.2.5

library(tm)

Loading required package: NLP

Attaching package: ‘NLP’

The following object is masked from ‘package:ggplot2’:

    annotate

library(stringr)
library(DT)

Read in external data files. One data set is from the Texas Education Agency and the other is from an education think tank research project.

setwd("D:\\\\challenges\\rusersgroup")

The working directory was changed to D:/challenges/rusersgroup inside a notebook chunk. The working directory will be reset when the chunk is finished running. Use the knitr root.dir option in the setup chunk to change the the working directory for notebook chunks.

tea <- read_csv("SAT_ACT_District_Data_Class_2012.csv")

1 parsing failure.
  row col   expected    actual
12997  -- 11 columns 1 columns

acgr <- read_csv("acgr.csv")

Let’s get down to the dirty work. Two data sets from data sources. Each organization has its own data standards.

For our analysis, we are going to use the data at the district level and match the data on the most common variable – District Name.

Let do some basic cleanup to standardize the common variable such as case conversion and remove punctuation.

#---------------   Case conversion to upper case   --------------------#
acgr$districtname <- toupper(acgr$leanm10)
tea$districtname <- toupper(tea$distname)
#----------------   Remove punctuation from district name   --------------#
acgr$districtname <- removePunctuation(acgr$districtname)
tea$districtname <- removePunctuation(tea$districtname)

Let’s go to the tidyverse and clean up our two data sets.

The acgr data set contains national graduation rates by school district. Since we are only interested in Texas students, we need to filter by state.

The tea data set is the Texas Education Agency’s data for percentages of students in the district who took SAT/ACT exams. It also has rows by ethnicity. For this analysis, we just want all students.

acgr_analysis <-  acgr %>%
                  filter(stnam == 'TEXAS') %>%
                  rename(grad_rate = ALL_RATE_1011) %>%
                  select(districtname,grad_rate)
tea_analysis <- tea %>%
                filter(Group == 'All Students') %>%
                select(districtname, Part_Rate)
                

We have two separate data sets, each with different number of rows. This is typical of data sets from different sources. We are going to find our matches by district name and build the matched data frame. Then we will build a non-match data frame

matched_df <- inner_join(acgr_analysis,tea_analysis,by='districtname')
non_matched_acgr <- anti_join(acgr_analysis,tea_analysis,by='districtname')
non_matched_tea <- anti_join(tea_analysis,acgr_analysis,by='districtname')
#--------------------------------------------------------------------------#
#  Display tables with data tables format                                  #
#--------------------------------------------------------------------------#
datatable(matched_df, rownames=FALSE, options = list(
  pageLength = 25, autoWidth = TRUE, searching=FALSE))

Let’s review the non-matches in each of the data sets.

datatable(non_matched_acgr, rownames=FALSE, options = list(
  pageLength = 25, autoWidth = TRUE, searching=FALSE))

datatable(non_matched_tea, rownames=FALSE, options = list(
  pageLength = 25, autoWidth = TRUE, searching=FALSE))

Now it is time to do fuzzy matching on the district names for the two data sets. We perform a one to many match scores on district name.

We will compute the similary score using the Levenshtein distance algorithm from the stringdist package.

#------------   Data frame to hold score in loop   ----------------------#
score_df <- data_frame(x=numeric(0),y=numeric(0),score=numeric(0))
for (x in 1:nrow(non_matched_acgr)) {
  
  for (y in 1: nrow(non_matched_tea))  {
    
     #-----------------------------------------------------------------#
     #  Similarity score using Levenshtein distance                    #
     #-----------------------------------------------------------------#
     score <- stringsim(non_matched_acgr$districtname[x],   non_matched_tea$districtname[y], method = 'lv')
     
     #-----------   Current data values   ---------------------------#
     term <- data_frame(x=x,y=y,score=score)
     
     #-------------  Build output data frame   ------------------#
     score_df <- bind_rows(score_df, term)
    
  }
  
}

Look at the non matches data frame and join the data by row number.

non_matched_acgr$x <- row(non_matched_acgr)
non_matched_tea$y <- row(non_matched_tea)
lv_matches <- left_join(non_matched_acgr,score_df,by='x')
lv_tea <- left_join(non_matched_tea,lv_matches, by='y')

Select the matches by filtering on similarity score. Take all score above .5. Review manually to determine the cutoff filter

tea_matches <- filter(lv_tea, score > .5)
#--------------  Order Columns  -----------------------------------#
tea_matches <- tea_matches %>%
               select(districtname.x,districtname.y,score,Part_Rate,grad_rate) 
 
datatable(tea_matches, rownames=FALSE, options = list(
  pageLength = 25, autoWidth = TRUE, searching=FALSE))

Reviewed manually and decided a decent cutoff filter would be .75

tea_cutoff <- filter(tea_matches, score > .75)
datatable(tea_cutoff, rownames=FALSE, options = list(
  pageLength = 25, autoWidth = TRUE, searching=FALSE))

Manually select matches

fuzzy_matches <- tea_cutoff %>%
                 slice(c(3:6,8,10:11))
datatable(fuzzy_matches, rownames=FALSE, options = list(
  pageLength = 25, autoWidth = TRUE, searching=FALSE))