Question

USE PYTHON

1. Create a single list that contains the following collection of data in the order provided:

   [1121, "Jackie Grainger", 22.22,

   1122, "Jignesh Thrakkar", 25.25,

   1127, "Dion Green", 28.75, False,

   24.32, 1132, "Jacob Gerber",

   "Sarah Sanderson", 23.45, 1137, True,

   "Brandon Heck", 1138, 25.84, True,

   1152, "David Toma", 22.65,

   23.75, 1157, "Charles King", False,

   "Jackie Grainger", 1121, 22.22, False,

   22.65, 1152, "David Toma"]

   
   The data above represents employee information exported from an Excel spreadsheet. Whomever typed the data in originally didn't give much care to how the information was formatted, unfortunately. Each line of data has in it employee information (the whole number), the name of the employee (the string), and the employee's hourly wage (the decimal number). Some rows have extra information in them from a column in the spreadsheet that no one can remember what its purpose was.
   
   Note that the presence of a couple of new data types in the above - "float" values (the numbers with the decimals in them) and "bool" [short for boolean] values (True and False).
   
2. Assume that the above information is a small sample of the company's data. Programmatically sort the information into a list of dictionary items. Each dictionary must be in a database-like format.
   
3. No duplicate data should make its way into the list of dictionary items.
   
4. For each value in the list multiply the current hourly wage value by 1.3 (since benefits are about 30% of a person's salary) and add a key to each dictionary item called total_hourly_rate. Under that key, store the value you just calculated.
   
5. Determine if anyone's total hourly rate is between 28.15 and 30.65. If they are, add stored dictionary information on the person to a new list called underpaid_salaries.
   
6. For each value in the dictionary, calculate a raise in dollars according to the following rules:
   
   If the hourly rate (not the total hourly rate) is between 22 and 24 dollars per hour, apply a 5% raise to the current rate. If the hourly rate is between 24 and 26 dollars per hour, apply a 4% raise to the current rate. If the hourly rate is between 26 and 28 dollars per hour, apply a 3% raise to the current rate. All other salary ranges should get a standard 2% raise to the current rate.
   
   Add to a new list called company_raises the name of the employee and the raise you calculated for each person. This information will be stored as a dictionary in database-like format.
   
7. Print out the data in all three lists you generated for this assignment.

Answer 1

First of all let's understand the data given (shown in below image):

  

PART-1

Here, we can see that, we have 3 values for each row: emp_id, emp_name and hrl_wage. In some cases, there is a boolean value which has no significance since we don't know what it is. So let's first create a list of dictionaries where one dictionary will contain one employee information. Below is the code I am using for this.

import pandas as pd
import json

# Given Data:
data = [1121, "Jackie Grainger", 22.22,
1122, "Jignesh Thrakkar", 25.25,
1127, "Dion Green", 28.75, False,
24.32, 1132, "Jacob Gerber",
"Sarah Sanderson", 23.45, 1137, True,
"Brandon Heck", 1138, 25.84, True,
1152, "David Toma", 22.65,
23.75, 1157, "Charles King", False,
"Jackie Grainger", 1121, 22.22, False,
22.65, 1152, "David Toma"]

new_data = []
info = {}
for ele in data:
    if(type(ele)==int):
        info['emp_info'] = ele
    elif(type(ele)==str):
        info['emp_name'] = ele
    elif(type(ele)==float):
        info['emp_hrl_wage'] = ele
    else:
        pass
    if(len(info)==3):
        new_data.append(info)
        info = {}

df = pd.DataFrame(new_data)
df.drop_duplicates(subset ="emp_info",keep = False, inplace = True)
df['total_hrl_rate'] = df['emp_hrl_wage'] * 1.3
main_list = json.loads(df.to_json(orient ='records'))
main_list

In the above code we separated the rows based on the data type of values and whenever we see 3 elements in the dictionary, we assume we have all the data for one employee and then we repeat the same process.

Once we have the dataframe for all the data, we apply the drop_duplicates function. We also added a new key, total_hrl_rate, which is 1.3 times of emp_hrl_wage, and then converted the data to json/database-like format. Below is the output for the above code.

PART-2 : To get the data of employee having underpaid salaries (between 28.15 and 30.65), below is the code that can be used:

# Getting list of emp with underpaid Salary.
underpaid_salaries = df[df['total_hrl_rate'] < 30.65][df['total_hrl_rate'] > 28.15]
underpaid_salaries = json.loads(underpaid_salaries.to_json(orient ='records'))
underpaid_salaries

Here, in the above code, we applied the filter in dataframe format itself and then converted to json format.

Output for above code is:

PART-3 : Here we need to calculate the raise for each dictionary in the main_list.

For creating a raise column based on given condition, below code can be used:

def get_raise(wage):
    if(wage >= 22.0 and wage < 24.0):
        return wage * 0.05
    elif(wage >= 24.0 and wage < 26.0):
        return wage * 0.04
    elif(wage >= 26.0 and wage < 28.0):
        return wage * 0.03
    else:
        return wage * 0.02

#company_raises
df['raise'] = df['emp_hrl_wage'].map(get_raise)
company_raises = json.loads(df[['emp_name','raise']].to_json(orient ='records'))
company_raises

Here each row's wage data is mapped to a function which returns the corresponding raise. Further separated the emp_name and raise to a different dataframe and converted to json format.

Below is the output for the above code:

With this, I hope that, I have answered to your question and you got the idea to go ahead.  

Thanks and Have a nice day!!

=========================================================================

Below is the screenshots of all the codes used, to avoid any indentation issues: