I recently came across a qualitative study that used interviews with NHS Covid staff to explore the impact of working in COVID during a pandemic on their experience, ability to work effectively together and the impact of social dynamics (e.g. cohesion, social support) on teamwork and mental health.
What particularly caught my attention was usage of network graph modeling for capturing overarching thematic clusters based on the co-occurrences of themes in the thematically coded interview transcripts.
It inspired me to try to apply this approach to the open-ended comments from the employee survey after identifying the topics present in each comment using the LLM. IMO, this can help with better interpretation of the survey results by providing a broader context of the topics identified and thus help to more wisely select appropriate follow-up actions.
Let’s check out how this works on sample comments taken from Glassdoor. You can download the data from Kaggle here. To preserve the anonymity of the company whose reviews I will be analyzing, I will not show all the filters used and will upload the pre-prepared data directly. This data consists of the cons reported by previous or current employees of one specific company at one specific location during the first half of 2021. Our input data looks as follows.
# uploading data
import pandas as pd
df = pd.read_csv('company_cons.csv')
library(reticulate)
library(DT)
DT::datatable(
py$df,
rownames = FALSE,
options = list(pageLength = 5)
)
Showing 20 of 562 rows and all 2 columns. View the full interactive table in the original post.
| id | cons |
|---|---|
| 0 | Poor Compensation and bonus null |
| 1 | A lot of pressure in the job |
| 2 | progression is too set in stone |
| 3 | Poor work life balance and pressures |
| 4 | Limit on bonus, not so great pension benefits as compared to other companies |
| 5 | Progression can be quite strictly regimented |
| 6 | The pay is relatively low |
| 7 | Gruelling path to progression Long hours and high expectations |
| 8 | Salary relative to peers, less community |
| 9 | Lowest pay on the street |
| 10 | clients can be very rude at times |
| 11 | Nothing of significance to point out. |
| 12 | No comment here - not applicable |
| 13 | Some projects can be very intense with long hours |
| 14 | It is hard work and might feel like a lot of you are not used to it or not fully engaged |
| 15 | Lower salaries Harder to make director/partner than before |
| 16 | Pay could be higher bonus is low |
| 17 | - poor salary - long unpaid overtime - people are fake |
| 18 | No boundaries, impossible expectations on client delivery and practice development |
| 19 | No work life balance Pay not commensurate with workload |
Now let’s extract individual pain points from employee feedback using LLM, specifically Mixtral-8x7B-Instruct-v0.1 from Mistral AI.
from huggingface_hub import login
login(token="your_token", add_to_git_credential=True)
from transformers import AutoTokenizer, pipeline, AutoModelForCausalLM
import torch
import accelerate
import bitsandbytes
import re
import ast
import numpy as np
# specifying the llm model
model_id = 'mistralai/Mixtral-8x7B-Instruct-v0.1'
tokenizer = AutoTokenizer.from_pretrained(model_id)
model = AutoModelForCausalLM.from_pretrained(
model_id,
device_map="auto",
load_in_4bit=True,
torch_dtype=torch.float32
)
generator = pipeline(
task='text-generation',
model=model,
tokenizer=tokenizer,
max_new_tokens=2048,
repetition_penalty=1.1,
device_map="auto",
torch_dtype=torch.float32,
do_sample=True,
temperature=0.001
)
We will loop over individual comments and extract pain points using the following prompt.
# looping over individual comments
responses = []
for row in range(0, df.shape[0]):
comment = df.loc[row, 'cons']
prompt = f'''[INST]
You are a first-class expert in analyzing feedback provided by employees.
Your task is to identify main pain points (negative feedback) in the comment.
Be short and precise: use short phrases or sentences only to record main pain points, i.e. what employee does not like or complaint about.
Use only those phrases or sentences that closely relate to the employee's feedback in the comment. Do not include points that indirectly or remotely relate to the employee's feedback.
Always return a json file with the following key-value pair: pain points - a string of short sentences or phrases separated by commas describing what employee does not like or complaint about. Don't use brackets or parentheses in the value part.
If there is no pain point mentioned in the comment or if you do not know which value to assign, always return an empty string with the key, i.e. {{"pain points": ""}}.
As an example, the following comment "Poor decision-making. People who make decisions don't know much about the topic, and usually they don't consult experts. However, the workload is appropriate. I would like to see a more professional approach." will return the following json file: {{"pain points": "poor decision-making, missing expertise in decision-making"}}; the following comment "My direct manager was completely incompetent." will return the following json file: {{"pain points": "incompetent direct manager"}}.
Return a json file as instructed. Always include the name of the key and its corresponding value, even if it is an empty string! Don't use brackets or parentheses in the value part.
Avoid duplications or similar phrases or sentences. Don't provide any alternative solutions. Don't make any comments or notes on the output, just provide me the json file in the format I asked for, i.e. {{"pain points": "a string of short sentences or phrases separated by commas describing what employee does not like or complaint about"}}!!!
Here is a comment to analyze: {comment}
[/INST]'''
response = generator(prompt)
extracted_content = response[0]["generated_text"].split("[/INST]")[-1].strip()
extracted_dict = re.search(r"\{.*\}", extracted_content, re.DOTALL).group()
cleaned_dict = re.sub(r'[\x00-\x1F\x7F-\x9F]', '', extracted_dict)
proper_dict = ast.literal_eval(cleaned_dict)
current_key = list(proper_dict.keys())[0]
proper_dict['pain points'] = proper_dict.pop(current_key)
responses.append(proper_dict)
# enriching the original dataset with identified pain points
pain_points_df = pd.DataFrame(responses)
pain_points_df['pain points'].replace('', np.nan, inplace=True)
pain_points_df = pd.concat([pain_points_df, df], axis=1)
Before the next step, we need to explode the enriched dataset on a pain point basis to get them on separate lines while preserving information about the original comments from which they were extracted.
# exploading the df by pain points
pain_points_df['pain points'] = pain_points_df['pain points'].str.split(', ')
pain_points_df_exploaded = pain_points_df.explode('pain points')
We get the following resulting table.
pain_points_df_exploaded = read.csv('pain_points_exploaded.csv')
DT::datatable(
pain_points_df_exploaded,
rownames = FALSE,
options = list(pageLength = 5)
)
Showing 20 of 1,046 rows and all 3 columns. View the full interactive table in the original post.
| id | cons | pain.points |
|---|---|---|
| 0 | Poor Compensation and bonus null | poor compensation |
| 0 | Poor Compensation and bonus null | lack of bonuses |
| 1 | A lot of pressure in the job | pressure in the job |
| 2 | progression is too set in stone | progression is too rigid |
| 2 | progression is too set in stone | lack of flexibility in progression |
| 3 | Poor work life balance and pressures | poor work life balance |
| 3 | Poor work life balance and pressures | pressures |
| 4 | Limit on bonus, not so great pension benefits as compared to other companies | limit on bonus |
| 4 | Limit on bonus, not so great pension benefits as compared to other companies | poor pension benefits |
| 5 | Progression can be quite strictly regimented | strictly regimented progression |
| 6 | The pay is relatively low | low pay |
| 7 | Gruelling path to progression Long hours and high expectations | grueling path to progression |
| 7 | Gruelling path to progression Long hours and high expectations | long hours |
| 7 | Gruelling path to progression Long hours and high expectations | high expectations |
| 8 | Salary relative to peers, less community | low salary compared to peers |
| 8 | Salary relative to peers, less community | lack of community |
| 9 | Lowest pay on the street | lowest pay |
| 10 | clients can be very rude at times | rude clients |
| 11 | Nothing of significance to point out. | |
| 12 | No comment here - not applicable |
Now we can proceed further and use the pre-prepared list of topic labels to classify all the extracted pain points.
# list of employee experience topic labels
employee_experience_topics = pd.read_csv("employee_experience_topics.csv")
employee_experience_topics['topic_description'] = employee_experience_topics['topic'] + ": " + employee_experience_topics['description']
library(tidyverse)
DT::datatable(
py$employee_experience_topics %>% dplyr::select(topic, description),
rownames = FALSE,
options = list(pageLength = 5)
)
Showing 20 of 56 rows and all 2 columns. View the full interactive table in the original post.
| topic | description |
|---|---|
| Company Brand, Prestige & Reputation | The perception and image of the company in the marketplace, including its credibility, reliability, and status. |
| Company Strategy | The company's long-term plan and vision for achieving its goals and maintaining competitive advantage. |
| Company Innovativeness | The ability of the company to develop new ideas, products, and processes to stay ahead of the competition. |
| Staffing & Recruitment | The process of attracting, selecting, and appointing suitable candidates for jobs within the company. |
| Capacity Planning | The process of determining the production capacity needed by an organization to meet changing demands for its products or services. |
| Employee Turnover | The rate at which employees leave the company and are replaced by new hires. |
| Employee Onboarding | The process of integrating new employees into the company, including orientation and training. |
| Promotion & Succession | The processes for advancing employees to higher positions and preparing for future leadership transitions. |
| Lateral Moves, Transfers & Relocations | The movement of employees within or across roles, departments, or locations to enhance skills, meet organizational needs, and support career growth. |
| Working Tools & Equipment | The physical and digital tools and resources provided to employees to perform their work effectively. |
| Relationship With Peers & Colleagues | The quality of interactions and collaborations between employees at the same organizational level. |
| Relationship With Direct Manager | The quality of the working relationship and communication between an employee and their immediate supervisor. |
| Leadership & Higher Management | The effectiveness and approachability of the company's senior leaders and their ability to inspire and guide the organization. |
| Support From Manager | The assistance and encouragement provided by a manager to their team members. |
| Management Practices | The manner in which managers lead their teams, including their decision-making and interpersonal approach. |
| HR Support | The effectiveness of the Human Resources department in addressing employee needs and issues. |
| IT Support | The responsiveness and competence of the IT department in resolving technical issues and providing technological assistance. |
| Support & Resources | The overall availability and quality of assistance and resources provided to employees to help them succeed. |
| Compensation & Benefits | The salary, bonuses, and benefits packages provided to employees in exchange for their work. |
| Recognition | The acknowledgment and appreciation of employees' contributions, achievements and value they bring to the company. |
For topic labeling, we will use the LLM again with the following prompt.
# making from topic labels one string that will be used in the prompt
response_topic_bank = employee_experience_topics['topic_description'].to_list()
pain_points_topic_list_str = "\n".join(response_topic_bank)
responses_topics = []
# looping over individual pain points
for index, row in pain_points_df_exploaded.iterrows():
text = row['pain points']
if not text or pd.isna(text):
responses_topics.append(np.nan)
else:
prompt = f'''[INST]
You are a first-class expert in analyzing feedback provided by employees.
Your task is to select from the provided list and assign one specific topic label that best captures the general meaning of short snippet of text extracted from employee's feedback during exit interview while leaving the company.
Don't make up your own topic labels and use only the topic labels from the following list: {pain_points_topic_list_str}
If you consider more options from the provided list of labels, always choose only one of them!!! Don't provide me more alternatives, I want just one solution!
If you are not sure about the meaning of the text or you don't know what label from the provided list to assign to the text, use the label 'Other', nothing more.
Assign always only one label to the text. Don't provide any additional comment, note, or explanation of your reasoning before or after the suggested label. Don't provide me more alternatives, I want just one solution!
Don't mix two different labels into one label.
You are provided with the topic labels together with their description, but put into your output only the name of the topic, not its description!
Return a json file in the format {{"topic": "suggested topic label"}}.
As an example, the following text 'lack of sufficient staff in functional positions' will return the following json file: {{"topic": "Staffing & Recruitment"}}; text 'not selected for positions' will return the following json file: {{"topic": "Career Development & Growth Opportunities"}}; text 'lack of feedback' will return the following json file: {{"topic": "Performance Management"}}; text 'recognition based solely on sales results' will return the following json file: {{"topic": "Recognition"}}; text 'perceived threat from employee' will return the following json file: {{"topic": "Psychological Safety"}}; if you are not sure about the meaning of the text or you don't know what label from the provided list to assign to the text, you will return the following json file: {{"topic": "Other"}}.
Return a json file in the required format. Assign always only one label that matches the meaning of the text best!!! Provide me only with the topic label name, not its description!!!
Don't make any comment, note, description of your reasoning, or explanation of your reasoning. Don't provide me more alternatives. If there are more options, choose only one of them - the one you think is the best option!!! Avoid using any brackets or parentheses in your output! Return just one json file.
Remember, before giving me required output, check if you are giving one proper json file I can immediately process in Python! If not, wait and redo your work so I get what I asked for!!!
Now give me the best label available in the provided list of labels for the following text: {text}
[/INST]'''
response = generator(prompt)
extracted_content = response[0]["generated_text"].split("[/INST]")[-1].strip()
extracted_dict = re.search(r"\{.*?\}", extracted_content, re.DOTALL).group()
proper_dict = ast.literal_eval(extracted_dict)
responses_topics.append(proper_dict)
# enriching dataset with pain points with selected topic labels
responses_topics_df = pd.DataFrame([x if isinstance(x, dict) else {'topic': np.nan} for x in responses_topics])
pain_points_df_exploaded['topic_label'] = responses_topics_df['topic'].values
Now we have for each pain point also a corresponding topic label.
pain_points_topics_exploaded = read.csv('pain_points_topics_exploaded.csv')
DT::datatable(
pain_points_topics_exploaded,
rownames = FALSE,
options = list(pageLength = 5)
)
Showing 20 of 1,046 rows and all 4 columns. View the full interactive table in the original post.
| id | cons | pain.points | topic_label |
|---|---|---|---|
| 0 | Poor Compensation and bonus null | poor compensation | Compensation & Benefits |
| 0 | Poor Compensation and bonus null | lack of bonuses | Compensation & Benefits |
| 1 | A lot of pressure in the job | pressure in the job | Workload, Stress & Exhaustion |
| 2 | progression is too set in stone | progression is too rigid | Promotion & Succession |
| 2 | progression is too set in stone | lack of flexibility in progression | Career Development & Growth Opportunities |
| 3 | Poor work life balance and pressures | poor work life balance | Work Life Balance |
| 3 | Poor work life balance and pressures | pressures | Workload, Stress & Exhaustion |
| 4 | Limit on bonus, not so great pension benefits as compared to other companies | limit on bonus | Compensation & Benefits |
| 4 | Limit on bonus, not so great pension benefits as compared to other companies | poor pension benefits | Compensation & Benefits |
| 5 | Progression can be quite strictly regimented | strictly regimented progression | Career Development & Growth Opportunities |
| 6 | The pay is relatively low | low pay | Compensation & Benefits |
| 7 | Gruelling path to progression Long hours and high expectations | grueling path to progression | Career Development & Growth Opportunities |
| 7 | Gruelling path to progression Long hours and high expectations | long hours | Workload, Stress & Exhaustion |
| 7 | Gruelling path to progression Long hours and high expectations | high expectations | Performance Management |
| 8 | Salary relative to peers, less community | low salary compared to peers | Compensation & Benefits |
| 8 | Salary relative to peers, less community | lack of community | Team Climate |
| 9 | Lowest pay on the street | lowest pay | Compensation & Benefits |
| 10 | clients can be very rude at times | rude clients | Relationship With Peers & Colleagues |
| 11 | Nothing of significance to point out. | ||
| 12 | No comment here - not applicable |
In the final step, we need to obtain descriptive statistics for the co-occurrence of all pairs of topics across all employee comments and in a format suitable for network analysis.
from itertools import combinations
from collections import Counter
# counter for storing co-occurrence counts
co_occurrence_counter = Counter()
# finding topic combinations within each employee comment
for _, group in pain_points_df_exploaded.groupby('id'):
topics = group['topic_label'].tolist()
for combo in combinations(sorted(topics), 2):
co_occurrence_counter[combo] += 1
# converting the counter to a df
co_occurrence_df = pd.DataFrame(
list(co_occurrence_counter.items()),
columns=['topic_pair', 'weight']
)
# splitting the tuple into separate columns
co_occurrence_df[['from', 'to']] = pd.DataFrame(co_occurrence_df['topic_pair'].tolist(), index=co_occurrence_df.index)
# dropping the original topic_pair column
co_occurrence_df = co_occurrence_df.drop(columns='topic_pair')
# removing edges between the same topic labels
co_occurrence_df = co_occurrence_df[co_occurrence_df['from']!=co_occurrence_df['to']]
co_occurrence_df.reset_index(drop=True, inplace=True)
# changing order of cols
co_occurrence_df = co_occurrence_df[['from', 'to', 'weight']]
co_occurrence_df.sort_values(by='weight', ascending=False)
And this is how the final table looks like.
pain_points_cooccurrence = read.csv('pain_points_cooccurrence.csv')
DT::datatable(
pain_points_cooccurrence,
rownames = FALSE,
options = list(pageLength = 5)
)
Showing 20 of 277 rows and all 3 columns. View the full interactive table in the original post.
| from | to | weight |
|---|---|---|
| Career Development & Growth Opportunities | Promotion & Succession | 2 |
| Work Life Balance | Workload, Stress & Exhaustion | 28 |
| Career Development & Growth Opportunities | Performance Management | 1 |
| Career Development & Growth Opportunities | Workload, Stress & Exhaustion | 5 |
| Performance Management | Workload, Stress & Exhaustion | 9 |
| Compensation & Benefits | Team Climate | 1 |
| Company Climate | Workload, Stress & Exhaustion | 4 |
| Career Development & Growth Opportunities | Compensation & Benefits | 17 |
| Compensation & Benefits | Workload, Stress & Exhaustion | 63 |
| Compensation & Benefits | Workplace Harassment | 2 |
| Workload, Stress & Exhaustion | Workplace Harassment | 3 |
| Performance Management | Unclear Expectations | 2 |
| Compensation & Benefits | Work Life Balance | 16 |
| Workload, Stress & Exhaustion | Workplace Safety | 1 |
| Organizational Processes Efficiency | Workload, Stress & Exhaustion | 12 |
| Communication | Workload, Stress & Exhaustion | 1 |
| Career Development & Growth Opportunities | Company Structure & Organization | 1 |
| Compensation & Benefits | Workplace Culture | 8 |
| Workload, Stress & Exhaustion | Workplace Culture | 4 |
| Employee Training & Development | Quality of Work | 1 |
The above table can then be used to create an undirected network object and visualize it using, for example, the d3Network package for D3 JavaScript network graphs. The resulting network graph shows the relationships between topics such that topics that are closer together and connected by stronger edges tend to appear more frequently in employee comments together. The node size indicates how often the topic occurs with other topics in employee comments (degree centrality), and the colors represent the communities (clusters) of topics as detected by the Louvain method for community detection. The graph is interactive, so you can zoom in/out and highlight specific parts of the graph you’re interested in.
library(igraph)
library(networkD3)
# creating network object
network <- graph_from_data_frame(pain_points_cooccurrence, directed=FALSE)
# computing degree centrality and community detection using Louvain method
V(network)$degree <- degree(network)
clusters <- cluster_louvain(network)
V(network)$community <- clusters$membership
# preparing data for networkD3 dataviz
networkD3_data <- igraph_to_networkD3(network)
networkD3_data$nodes$group <- V(network)$community
networkD3_data$nodes$degree <- V(network)$degree
networkD3_data$nodes$reporting_name <- V(network)$name
# specifying custom color scale
colors <- c("#1b39a6", "#b13aa0", "#72a239", "#895f22", "#d08311", "#ca4f1a", "#8764d9")
# networkD3 dataviz
networkD3::forceNetwork(
Links = networkD3_data$links,
Nodes = networkD3_data$nodes,
Source = 'source',
Target = 'target',
NodeID = 'reporting_name',
Value = "value",
Group = "group",
Nodesize = 'degree',
arrows = FALSE,
legend = FALSE,
opacity = 1,
zoom = TRUE,
fontSize = 20,
opacityNoHover = 1,
linkDistance = 60,
charge = -900,
colourScale = JS(paste0("d3.scaleOrdinal().range([\"", paste(colors, collapse = "\", \""), "\"])"))
)
Related notes
- Psychometric network analysis & employee survey data
- Mapping the People Analytics universe
- Employee feedback analysis using tools from OpenAI
- Analyzing LinkedIn connections’ jobs using LLMs and the BERTopic package
- What makes people more likely to comment on a question in an employee survey?
📄 Read the original post with full outputs on my blog.