I asked for a copy of excel from the library, which contains a large amount of data. I divided the excel into student articles and faculty articles. The excel is divided into the first author and the first author. Big guy don’t spray, just a little bit of learning.

Import the Excel and filter criteria that you want to filter

import pandas as pd df = pd.read_excel('D:/py_project/datafenxi/scut.xls',sheet_name='savedrecs') ji_he = {'name1','name2','name3', any number of names}Copy the code

Here ji_HE is the name of all teachers in the school provided, matched with the following form A to be screened, marking papers in A as teachers. The rest of the unmarked are the students.

Form A to filter

Excel content looks something like this (sorry! Jay Lee Hom purple chess):

Here are jaron, Lee Hom, and Purple.

Train of thought

1. Select the Author Full Names column from table A and press; Split it into an array and take the 0th element, because I got a table 1 that had all sorts of weird formats, and – and so forth, so I changed it a little bit. Sheet1 sheet1 is sheet1, sheet1 is sheet1, sheet1 is sheet1, Sheet1 is sheet1, Sheet1 is sheet1.

While I < len(df): a = df.loc[I, 'Author Full Names'] ')[0].replace('-', '') # replace('-', ') # A1 # print('q', a1) if a1 in ji_he: print('qq', I) # for matching index df3. Loc [I] = [' the teacher '] # to match up a teacher on the teacher df3. To_excel (" D: / py_project datafenxi/outputscut. XLS ") # The output is the new list I += 1Copy the code

Sheet1 is A sheet1 that matches the identity of the teacher.

Merge () then merge the two tables into a complete table based on the ID, and basically complete the requirements.

import pandas as pd

sheet1 = pd.read_excel('D:/py_project/datafenxi/scut.xls', sheet_name='savedrecs', index_col='ID')
sheet2 = pd.read_excel('D:/py_project/datafenxi/scut.xls', sheet_name='Sheet1', index_col='ID')

table = sheet1.merge(sheet2, how='left', on='ID')
table.to_excel("D:/py_project/datafenxi/outputfinal.xls")
print(table)
Copy the code

The results of

For this grammar, please refer to the video Link of station B, remember to put two sheets in one Excel (in fact, I have seen other bosses directly output multiple sheets in one Excel, but I don’t have time to change, I will see later). Here is the final result. The table on the far right distinguishes the identity: teacher, blank for student.

PS: If you need Python learning materials, please click on the link below to obtain them

Free Python learning materials and group communication solutions click to join