Magento Question posted in
The official documentation can be found here.

R – Document Term Matrix with comma separated text column entries – Magento

I´ve got a data frame with a column consisting of Strings (project_skills) which denotes the skills a certain job (job_id) affords. I want to split this string for every job to get an vector of the skills a job affords and then create a Document Term Matrix to denote which skills (out of every possible skill) a certain job affords.

I´ve got the following data frame:

job_id           project_skills
107182           CSS,HTML,Joomla,PHP
108169           XTCommerce,Magento,Prestashop,VirtueMart,osCommerce
112969           Google Search Console,Google Analytics,Google Webmaster Central,C++,Java,C#
114660           Marketing,Email Marketing
118686           PHP

The result should look anything like this (which is basically a Document Term Matrix with comma separated phrases:

        project_skills
job_id  CSS   HTML   PHP   Google Search Console   Google Analytics   Java ...
107182  1     0       0 ...         
108169  0     0       0     0                       0         
112969  0     0       0     1                       1 ...         
114660  0     0       0 ...            
118686  0     0       1 ...

I´ve tried the following:

df <- data.frame(job_id = c(107182, 108169, 112969, 114660, 118686), project_skills = c("CSS,HTML,Joomla,PHP", "XTCommerce,Magento,Prestashop,VirtueMart,osCommerce", "Google Search Console,Google Analytics,Google Webmaster Central,C++,Java,C#", "Marketing,Email Marketing", "PHP"))

corpus <- Corpus(VectorSource(df$project_skills))
corpus <- tm_map(corpus, function(x) {
PlainTextDocument(
    strsplit(x,"\,")[[1]], 
    id=ID(x)
)
})
inspect(corpus)
dtm <- DocumentTermMatrix(corpus)
as.matrix(dtm)

But unfortunately this splits all words and not at the commas (e.g. Google Search Console should be treated as one Term in the DTM).

Tags:

2

Answers


  1. 0

    tm (or some other text mining packages) split on words (spaces) and if you don’t check tend to remove punctuation like + and #. The simplest option is just to use strsplit. I show an option below using tidyr and dplyr. First group by job_id, next split the columns. This will create a nesting, which when unnested creates a long data.frame. Here I add the value 1 for every entry which acts like a 1 in a document term matrix. Then spread into a wide format to get your expected output. If you look at the resulting structure the column names are what you expect, not showing the tilde (~). 

    library(tidyr)
library(dplyr)

outcome <- df1 %>%
  group_by(job_id) %>% 
  mutate(project_skills = strsplit(project_skills, ",")) %>% 
  unnest() %>% 
  mutate(value = 1) %>% # add 1 for every value
  spread(key = project_skills, value = value) # use fill = 0 if you don't want NA's

head(outcome)
# A tibble: 5 x 18
# Groups:   job_id [5]
  job_id  `C#` `C++`   CSS `Email Marketin~ `Google Analyti~ `Google Search ~ `Google Webmast~  HTML  Java Joomla Magento Marketing
   <int> <dbl> <dbl> <dbl>            <dbl>            <dbl>            <dbl>            <dbl> <dbl> <dbl>  <dbl>   <dbl>     <dbl>
1 107182    NA    NA     1               NA               NA               NA               NA     1    NA      1      NA        NA
2 108169    NA    NA    NA               NA               NA               NA               NA    NA    NA     NA       1        NA
3 112969     1     1    NA               NA                1                1                1    NA     1     NA      NA        NA
4 114660    NA    NA    NA                1               NA               NA               NA    NA    NA     NA      NA         1
5 118686    NA    NA    NA               NA               NA               NA               NA    NA    NA     NA      NA        NA
# ... with 5 more variables: osCommerce <dbl>, PHP <dbl>, Prestashop <dbl>, VirtueMart <dbl>, XTCommerce <dbl>
    Login or Signup to reply.
  2. 0

    There are many solutions to this but strsplit is your friend. That’s exactly what is done in the following code:

    library(udpipe)
df <- data.frame(job_id = c(107182, 108169, 112969, 114660, 118686), project_skills = c("CSS,HTML,Joomla,PHP", "XTCommerce,Magento,Prestashop,VirtueMart,osCommerce", "Google Search Console,Google Analytics,Google Webmaster Central,C++,Java,C#", "Marketing,Email Marketing", "PHP"), 
                 stringsAsFactors = FALSE)
dtm <- document_term_frequencies(x = df$project_skills, document = df$job_id, split = ",")
dtm <- document_term_matrix(dtm)
colnames(dtm)
 [1] "C#"                       "C++"                      "CSS"                      "Email Marketing"         
 [5] "Google Analytics"         "Google Search Console"    "Google Webmaster Central" "HTML"                    
 [9] "Java"                     "Joomla"                   "Magento"                  "Marketing"               
[13] "osCommerce"               "PHP"                      "Prestashop"               "VirtueMart"              
[17] "XTCommerce"              
rownames(dtm)
[1] "107182" "108169" "112969" "114660" "118686"
dim(dtm)
[1]  5 17
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search