Pandas SQL Query: Display the details of jobs in descending sequence on job title

Last update on May 28 2022 13:38:00 (UTC/GMT +8 hours)

Pandas HR database Queries: Exercise-23 with Solution

Write a Pandas program to display the details of jobs in descending sequence on job title.

JOBS.csv

Sample Solution :

Python Code :

import pandas as pd
employees = pd.read_csv(r"EMPLOYEES.csv")
departments = pd.read_csv(r"DEPARTMENTS.csv")
job_history = pd.read_csv(r"JOB_HISTORY.csv")
jobs = pd.read_csv(r"JOBS.csv")
countries = pd.read_csv(r"COUNTRIES.csv")
regions = pd.read_csv(r"REGIONS.csv")
locations = pd.read_csv(r"LOCATIONS.csv")
print("job_id      Job ID                                min_salary  max_salary")
result = jobs.sort_values('job_title')
for index, row in result.iterrows():
    print(row['job_id'].ljust(15),row['job_title'].ljust(35),str(row['min_salary']).ljust(9),row['max_salary'])

Sample Output:

job_id      Job ID                                min_salary  max_salary
FI_ACCOUNT      Accountant                          4200      9000
AC_MGR          Accounting Manager                  8200      16000
AD_ASST         Administration Assistant            3000      6000
AD_VP           Administration Vice President       15000     30000
FI_MGR          Finance Manager                     8200      16000
HR_REP          Human Resources Representative      4000      9000
MK_MAN          Marketing Manager                   9000      15000
MK_REP          Marketing Representative            4000      9000
AD_PRES         President                           20000     40000
IT_PROG         Programmer                          4000      10000
AC_ACCOUNT      Public Accountant                   4200      9000
PR_REP          Public Relations Representative     4500      10500
PU_CLERK        Purchasing Clerk                    2500      5500
PU_MAN          Purchasing Manager                  8000      15000
SA_MAN          Sales Manager                       10000     20000
SA_REP          Sales Representative                6000      12000
SH_CLERK        Shipping Clerk                      2500      5500
ST_CLERK        Stock Clerk                         2000      5000
ST_MAN          Stock Manager                       5500      8500

Click to view the table contain:

Python Code Editor:

Structure of HR database :

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a Pandas program to calculate minimum, maximum and mean salary from employees file.
Next: Write a Pandas program to display the first and last name and date of joining of the employees who is either Sales Representative or Sales Man.

Python: Tips of the Day

Find current directory and file's directory:

To get the full path to the directory a Python file is contained in, write this in that file:

import os dir_path = os.path.dirname(os.path.realpath(__file__))

(Note that the incantation above won't work if you've already used os.chdir() to change your current working directory, since the value of the __file__ constant is relative to the current working directory and is not changed by an os.chdir() call.)

To get the current working directory use

import os cwd = os.getcwd()

Documentation references for the modules, constants and functions used above:

The os and os.path modules.

The __file__ constant

os.path.realpath(path) (returns "the canonical path of the specified filename, eliminating any symbolic links encountered in the path")

os.path.dirname(path) (returns "the directory name of pathname path")

os.getcwd() (returns "a string representing the current working directory")

os.chdir(path) ("change the current working directory to path")

Ref: https://bit.ly/3fy0R6m