A university in the United States has 200 teachers. The University and the teachers' Union have just signed an agreement. According to the agreement, the salary of all teachers with an annual salary of more than $26000 and including $26000 will remain unchanged, and the salary of teachers with an annual salary of less than $26000 will be increased. The increased salary is calculated according to the following method: each person supported by the teacher (including the teacher himself) will be given a subsidy of $100 per year. In addition, the teacher will be given an additional subsidy of $50 per year for one year of service. However, The total annual salary after increase cannot exceed $26000.
The teacher's salary file is stored on the CD-ROM of the administrative office, which contains the current annual salary, number of dependants, employment date and other information. We need to write a program to calculate and print the original salary and the adjusted new salary of each teacher.
Data flow diagram
requirement analysis
1. Task overview
For teachers whose annual salary does not exceed $26000, that is, $100 for each dependent person and $50 for each year of service, the upper limit of salary increase is $26000.
2. Data description
The database adopts teacher salary table.
3. Functional requirements
(1) Obtain the current salary of teachers, obtain the number of dependants, obtain the length of service, and update the teacher information.
(2) Calculate salary and determine salary according to quota.
(3) Sorting and query
(4) Print the report and print the salary comparison sheet.
Algorithm design
Search the salary file data, find out the people whose annual salary is less than $26000, calculate the new salary, check whether it exceeds $26000, save the new salary, and print the old and new salary comparison table.
HIPO diagram is made for algorithm design
Program source code analysis and running result display
Analysis of python program source code
import pandas as pd#Introducing pandas Library from datetime import datetime#Introducing datetime Library def sal(): #Using pd.read_excel to read the teacher salary table #Using index_col = 0 to specify the first column as the row index df = pd.read_excel('C:/Users/admin/Desktop/teacherSal.xlsx', index_col = 0) print(df) #Create an empty excel table through DataFrame() in pandas library. There are two fields in this table (namely 'original teacher salary' and 'current teacher salary') df1 = pd.DataFrame(columns = ['Annual salary of former teachers'] + ['Annual salary of current teachers']) for i in range(1,201): #df.loc [] get the value of a row or a column #loc only supports the use of table row and column indexes, not built-in numerical indexes if df.loc[i, 'Teachers' annual salary'] >= 26000: sal = df.loc[i, 'Teachers' annual salary'] else: #datetime.now() indicates the system local time working_age = int((datetime.now() - df.loc[i, 'Date of employment']).days / 365.0) sal = df.loc[i, 'Teachers' annual salary'] + df.loc[i, 'Number of dependants'] * 100 + working_age * 50 if sal > 26000: sal = 26000 df1.loc[i, 'Annual salary of former teachers'] = df.loc[i, 'Teachers' annual salary'] df1.loc[i, 'Annual salary of current teachers'] = sal #Write the data in df1 into the adjusted payroll df1.to_excel('C:/Users/admin/Desktop/adjustSal.xlsx') #Read the data in the adjusted payroll and specify the first column as the row index df1 = pd.read_excel('C:/Users/admin/Desktop/adjustSal.xlsx', index_col = 0) print(df1) if __name__ == '__main__': sal()
Display of operation results
1. Learned to use the pandas Library in python to read the data in excel
2. We can use pd.read_excel('excel path ') to read the teacher salary table (PS: Excel path refers to the path where the excel table is stored). In addition, it can be displayed in pd.read_ Add the parameter index in Excel ()_ Col = 0 to specify that the first column is the row index, that is, pd.read_excel(‘file-path’, index-col = 0)
3. Learned to use pd.DataFrame() to create a table, such as df = pd.DataFrame(data=None, index=None, columns=None, dtype=None, copy=None)
Parameter data: indicates the data to be passed in, including ndarray, series, map, lists, dict, constant and another DataFrame.
Parameter index: row index. If it is not assigned manually, it will be assigned from 0 by default. Format is ['x1', 'x2']
Parameter columns: column index. If it is not assigned manually, it will be assigned from 0 by default. Format is ['x1', 'x2']
Parameter dtype: type of each column.
Parameter copy: you can write false or true. Copy data from the input. The default value is false. No copying is allowed.
If you want to create an empty table, you can write only row and column indexes, or write only column indexes.
4. Get the element value of a row or column in the table:
df.iloc[i, j]: i indicates row order and j indicates column order. Returns elements in order of value.
df.loc [row index, column index]: returns elements by index.
5. datetime.now() in the datetime Library in python represents the system local time
6. Write data to the table: use data.to_excel(‘excel-path’).