SELECT DISTINCT jobTitle
FROM employees
ORDER BY
CASE jobTitle
WHEN 'President' THEN 1
WHEN 'VP Marketing' THEN 2
WHEN 'VP Sales' THEN 3
ELSE 100
END;
EVALUATE
VAR JobRanking =
ADDCOLUMNS (
DISTINCT ( employees[jobTitle] ),
"Rank",
SWITCH (
employees[jobTitle],
"President", 1,
"VP Marketing", 2,
"VP Sales", 3,
100
)
)
RETURN
SUMMARIZECOLUMNS (
JobRanking[jobTitle],
"Rank", JobRanking[Rank]
)
ORDER BY [Rank]
import pandas as pd
# Sample DataFrame
df = pd.DataFrame({
'jobTitle': [
'President', 'VP Marketing', 'VP Sales',
'Manager', 'Engineer', 'Clerk', 'VP Sales'
]
})
# Create a mapping for custom ranking
ranking = {
'President': 1,
'VP Marketing': 2,
'VP Sales': 3
}
# Assign the custom rank to each jobTitle, default to 100 if not in the ranking
df['rank'] = df['jobTitle'].map(ranking).fillna(100).astype(int)
# Drop duplicates, sort by rank, and select the jobTitle column
result = df[['jobTitle']].drop_duplicates().sort_values(by='rank')
# Convert to a list of lists (optional, for comparison purposes)
result_list = result.values.tolist()
print(result_list)
| President |
| VP Marketing |
| VP Sales |
| Manager |
| Engineer |
| Clerk |
ADDCOLUMNS ( DISTINCT ( employees[jobTitle] ) ... ): Adds a column to the distinct job titles with the custom rank. SWITCH ( employees[jobTitle], ... ): Assigns the rank based on the job title. SUMMARIZECOLUMNS: Returns the distinct job titles and their ranks. ORDER BY [Rank]: Orders the job titles based on the rank.
Leave a Comment