Word processing in a large database (data mining)
I have a large resume database (CV) and a specific skills table grouping all of the user's skills.
within this table there is a skill_text field that describes the skill in full text.
I am looking for an algorithm / software / method to extract significant terms / phrases from this table in order to create a new table with standard skills.
Here are some examples of skills learned from the database:
- Industry and Competitive Analysis
- Business development (including in international conditions)
- Concrete structure and road design software - Microstation, Macao, AutoCAD (basic knowledge)
- Creative work (Photoshop, In-Design, Illustrator)
- checking and reporting on the progress of the campaign.
- organization and participation in events and exhibitions.
- Development: Aptana Studio, PHP, HTML, CSS, JavaScript, SQL, AJAX
- Discipline: One-to-One Marketing, Email Marketing (SEO & SEA, Display, Email, Affiliate Program) Mix Marketing, Viral Marketing, Social Network Marketing.
The output will look something like this:
- Industry and Competitive Analysis
- Business development
- Specific structure and road design software -
- Macao
- AutoCAD
- Photoshop
- B-design
- Illustrator
- organization of events
- Development
- Studio Aptana
- PHP
- Html
- CSS
- JavaScript
- SQL
- AJAX
- Mix marketing
- Viral marketing
- Social network marketing
- by email
- SEO
- Individual marketing
As you can see, skills alone leave no other presentation text.
I know this is possible with text mining technology, but how do I do it? the database is really big .. this is good because we can calculate the frequency of the text and decide if it is really a skill or just meaningless text ... The big problem is how to determine that "blablabla" is a skill?
Edit: please don't tell me to use standard stuff like tokinzer text or regex .. because the user enters skills very arbitrarily!
thank
a source to share
If I was doing this programmatically, I would:
Extract all punctuation-restricted data (or maybe only parentheses and commas) into a new table (no primary key, just a skill), so Creative work (Photoshop, In-Design, Illustrator)
it becomes
Skill
-------------
Creative work
Photoshop
In-Design
Illustrator
Then, after you continue with all CVs, ask for the most common skills (that's MySQL)
SELECT skill, COUNT(1) cnt FROM newTable GROUP BY skill ORDER BY cnt DESC;
What this contrived example might look like
Skill Cnt
---------------------
Photoshop 3293
Illustrator 2134
Creative work 932
In-Design 123
Then you pick from the best X skills that you want to capture, which should map to other skills ( Indesign
and In-design
should map to the same skill, for example) and which to discard, then script the process using a data map.
Use the data map to write a new word frequency table (this time skill_id, skill, frequency), and the second time when parsing the data is also written to the lookup table (cv_id, skill_id). Then your data will be in a state where each resume is mapped to multiple skills, and each skill is mapped to multiple CVs. You can request the most popular skills, resumes that meet certain criteria, etc.
a source to share
Many databases will do this for you through their full text search. I know that PostgreSQL full text search could easily do this with a custom dictionary.
Alternatively, you can use PHP strtok or equivalent to index your text. Once indexed, you can compare against a dictionary, or just use the entries to create a sheet for yourself. Text clouds are created in a similar way.
a source to share
This requires knowledge; otherwise, what to say "organizing events" is "skill" and "creative work" is not? But a stupid program might take the first snapshot of it by analyzing collocation statistics: see the Answers to How to extract common / meaningful phrases from a series of text entries and Phrase and Keyword Detection Algorithms from Text .
a source to share