Pandas SQL Query: Display the name, salary and department number for those employees whose first name does not contain the letter 'M'
Write a Pandas program to display the first, last name, salary and department number for those employees whose first name does not contain the letter 'M'.
DEPARTMENTS.csv
EMPLOYEES.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("Last name First name Salary Department ID")
result = employees[employees['first_name'].str.find('M')==-1]
for index, row in result.iterrows():
print(row['last_name'].ljust(15),row['first_name'].ljust(15),str(row['salary']).ljust(9),row['department_id'])
Sample Output:
Last name First name Salary Department ID King Steven 24000 90.0 Kochhar Neena 17000 90.0 De Haan Lex 17000 90.0 Hunold Alexander 9000 60.0 Ernst Bruce 6000 60.0 Austin David 4800 60.0 Pataballa Valli 4800 60.0 Lorentz Diana 4200 60.0 Greenberg Nancy 12000 100.0 Faviet Daniel 9000 100.0 Chen John 8200 100.0 Sciarra Ismael 7700 100.0 Popp Luis 6900 100.0 Raphaely Den 11000 30.0 Khoo Alexander 3100 30.0 Baida Shelli 2900 30.0 Tobias Sigal 2800 30.0 Himuro Guy 2600 30.0 Colmenares Karen 2500 30.0 Fripp Adam 8200 50.0 Kaufling Payam 7900 50.0 Vollman Shanta 6500 50.0 Mourgos Kevin 5800 50.0 Nayer Julia 3200 50.0 Mikkilineni Irene 2700 50.0 Landry James 2400 50.0 Markle Steven 2200 50.0 Bissot Laura 3300 50.0 Marlow James 2500 50.0 Olson TJ 2100 50.0 Mallin Jason 3300 50.0 Gee Ki 2400 50.0 Philtanker Hazel 2200 50.0 Ladwig Renske 3600 50.0 Stiles Stephen 3200 50.0 Seo John 2700 50.0 Patel Joshua 2500 50.0 Rajs Trenna 3500 50.0 Davies Curtis 3100 50.0 Matos Randall 2600 50.0 Vargas Peter 2500 50.0 Russell John 14000 80.0 Partners Karen 13500 80.0 Errazuriz Alberto 12000 80.0 Cambrault Gerald 11000 80.0 Zlotkey Eleni 10500 80.0 Tucker Peter 10000 80.0 Bernstein David 9500 80.0 Hall Peter 9000 80.0 Olsen Christopher 8000 80.0 Cambrault Nanette 7500 80.0 Tuvault Oliver 7000 80.0 King Janette 10000 80.0 Sully Patrick 9500 80.0 McEwen Allan 9000 80.0 Smith Lindsey 8000 80.0 Doran Louise 7500 80.0 Sewall Sarath 7000 80.0 Vishney Clara 10500 80.0 Greene Danielle 9500 80.0 Lee David 6800 80.0 Ande Sundar 6400 80.0 Banda Amit 6200 80.0 Ozer Lisa 11500 80.0 Bloom Harrison 10000 80.0 Fox Tayler 9600 80.0 Smith William 7400 80.0 Bates Elizabeth 7300 80.0 Kumar Sundita 6100 80.0 Abel Ellen 11000 80.0 Hutton Alyssa 8800 80.0 Taylor Jonathon 8600 80.0 Livingston Jack 8400 80.0 Grant Kimberely 7000 nan Johnson Charles 6200 80.0 Taylor Winston 3200 50.0 Fleaur Jean 3100 50.0 Geoni Girard 2800 50.0 Sarchand Nandita 4200 50.0 Bull Alexis 4100 50.0 Dellinger Julia 3400 50.0 Cabrio Anthony 3000 50.0 Chung Kelly 3800 50.0 Dilly Jennifer 3600 50.0 Gates Timothy 2900 50.0 Perkins Randall 2500 50.0 Bell Sarah 4000 50.0 Everett Britney 3900 50.0 McCain Samuel 3200 50.0 Jones Vance 2800 50.0 Walsh Alana 3100 50.0 Feeney Kevin 3000 50.0 OConnell Donald 2600 50.0 Grant Douglas 2600 50.0 Whalen Jennifer 4400 10.0 Fay Pat 6000 20.0 Mavris Susan 6500 40.0 Baer Hermann 10000 70.0 Higgins Shelley 12000 110.0 Gietz William 8300 110.0
Equivalent SQL Syntax:
SELECT first_name, last_name, salary, department_id FROM employees WHERE first_name NOT LIKE '%M%';
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 display the first, last name, salary and department number for those employees whose first name starts with the letter ‘S’.
Next: Write a Pandas program to display the first name, last name, salary and department number in ascending order by department number.
What is the difficulty level of this exercise?
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics