Realizing Excel data filtering and data matching with Python

Fan Doudou 123 2021-11-25 09:27:42
realizing excel data filtering data

Data filtering :

Output filtering results according to actual needs , Or save the filter results . To sum up, it can be divided into three categories :

① Store the screening results into the learned data structure , such as : list , Tuple or dictionary .
② Store the results of the filter in a file .
③ Print out the filtered results .

# Late for more than 45 Minutes late 3 Employees who have attended more than times will be recorded as unqualified , You need to save the filtered results as 【10 Monthly late person information .xlsx】, And report the sorted information to the leaders
# Ideas : get data -> Using data -> Output data
# 1、 Traverse every row of the table , Compare lateness time and number of lateness , If it is unqualified , Append the row information to the new table
from openpyxl import load_workbook,Workbook
# Open sheet
s=load_workbook('D:/ Attendance statistics .xlsx')
active_s=s.active
# New sheet
new=Workbook()
active_new=new.active
# Get header
for row in active_s.iter_rows(min_row=1,max_row=1,values_only=True):
active_new.append(row)
# Traverse worksheet s
for row in active_s.iter_rows(min_row=2,values_only=True):
if row[3]>45 & row[4]>3:
active_new.append(row)
new.save('D:/ Information of latecomers .xlsx')

Data matching ( With the help of a dictionary ):

Application : There are associated data in the two tables , At this time, you need to match the data in the table .

Concrete Operation steps You can still get data according to , Use data and data output to summarize :

1、 First of all get data , Make sure you want to get scattered cells , It's a single line / Single column , Or more lines / Multi column data .
2、 The next step is the key part of using data , The matching logic is implemented here . First, clarify the relationship between tables , Then the data in a table obtained , Store as needed ( A dictionary is recommended , Take the data that can be connected to the table as the key , The data to be matched as the value ). Then associate the value to be matched in another table with the corresponding value in the dictionary , Implement matching logic .3、 Finally, the data output part .

# Data matching
# Check whether the corresponding data in the two tables match
# Ideas :1、 get data : Get the corresponding data in the two tables
# 2、 Using data : Compare whether the corresponding data are equal
# 3、 Output data
from openpyxl import load_workbook
s=load_workbook('D:/ Attendance statistics .xlsx')
active_s=s.active
# Create a late Dictionary
late_dics={}
# Take out the number of late personnel and the number of late times
for row in active_s.iter_rows(min_row=2,values_only=True):
id=row[0]
late=row[4]
# Add information to the dictionary
late_dics[id]=late
s1=load_workbook('D:/ Attendance statistics 1.xlsx')
active_s1=s1.active
# Cycle out the number of late personnel and the number of late times
for row1 in active_s1.iter_rows(min_row=2,values_only=True):
id1=row1[0]
late1=row1[4]
# Match whether the number of lateness is equal
if late1!=late_dics[id1]:
print(' Number {} Late situation does not match '.format(id1))

版权声明
本文为[Fan Doudou 123]所创,转载请带上原文链接,感谢
https://pythonmana.com/2021/11/20211109005047254n.html

  1. The difference between single quotation marks, double quotation marks, three single quotation marks and three double quotation marks in Python
  2. Python exercise (11.7)
  3. python11~20
  4. Matlab, python example exercise -- Day2
  5. Matlab, python example exercise -- Day1
  6. White Python road -- Day5
  7. Python正则表达式选择字符
  8. The white way of Python -- day4 (I can't learn it gradually)
  9. The white way of Python -- Day3
  10. The white Python road -- Day2
  11. The white way of Python -- Day1
  12. Difference between input in python2 and python3
  13. Exception handling in Python
  14. Metaclass and singleton pattern of Python
  15. Python methods and functions
  16. Python regular expression select Characters
  17. Introduction to Python object-oriented reflection and built-in methods
  18. Object oriented composition of Python
  19. Python object oriented polymorphism
  20. Object oriented encapsulation of Python
  21. Object oriented inheritance in Python
  22. Python Software Development Directory
  23. Python package
  24. Python module
  25. Python is process oriented and functional
  26. Python function recursion
  27. Python generator
  28. Python iterator
  29. Python decorator
  30. Python function closure
  31. Python namespace and scope
  32. Python function
  33. Python file processing
  34. Python data type
  35. Process control of Python
  36. Garbage collection mechanism of Python
  37. Python: the field notes of dtype = 'object' can also be arranged in ascending and descending order
  38. . value in pandas_ Usage of counts()
  39. 202111 | some hands-on practice of Python time function
  40. 202111 | in Python, timestamp, time string and real date time are converted to each other
  41. Pandas draws line chart, bar chart and bar chart
  42. Exploration and practice of easy transformation between various data structures in Python
  43. A simple practice of Python list generation
  44. Use Python to guess the number of machines and judge the number of guesses
  45. Question about Python: did you learn Python
  46. 20210928 | Python case: building tax calculation function
  47. Python basic syntax collation
  48. Some small accumulation of writing programs in python (4)
  49. Some small accumulation of writing programs in python (3)
  50. Python leak detection tips (3)
  51. Python leak detection tips (2)
  52. Python leak detection tips (1)
  53. Python foundation and MySQL Foundation
  54. Some small accumulation of writing programs in python (2)
  55. Some small accumulation of writing programs in Python
  56. Python 3.7.3 + cuda9.2 installing Python
  57. Python knowledge used to write programs
  58. Python installation + vscode configuration Python environment
  59. Some small problems during Python installation
  60. Answer and Q & A of Python practice introduction course