current position：Home>You can easily get started with Excel. Python data analysis package pandas (2): advanced filtering (2)
You can easily get started with Excel. Python data analysis package pandas (2): advanced filtering (2)
20210823 04:46:59 【Excel catalyst】
Series articles ：
Preface
I often listen to others Python How powerful in the data field , As a result, I studied for a long time , Even data processing is a death of trouble . Only later , It's not Python Data processing is powerful , But he has a data analysis artifact —— pandas .
Followed by the advanced screening in the previous article , But the example in the previous article is too simple , It's a little difficult this time .
data
Continue to use the data in the previous article ：
  An examination report form of a school (8 Academic achievements )
Load data
pandas Need to load Excel data , as follows ：
Complex filtering
" People who are above the grade average ",Excel The condition area settings for advanced filtering are as follows ：
  This time you need to use the function formula in the condition value
  =K2>AVERAGE(K:K) , You can imagine ,Excel Will traverse each line , Ergodic time , I'll put... In the function formula K2 , Replace... With the value of the current line
pandas The implementation idea is the same , as follows ：
The above requirements are still a little simple , More stuff .
" A school whose total score is higher than the average score of its class ",Excel The condition area settings for advanced filtering are as follows ：
  You can't simply use AVERAGE , This is because we need to be based on class Get the average score of the class , This use AVERAGEIF(B:B,B2,K:K)
pandas Implementation is a little troublesome . as follows ：
  .groupby(' class ') , Group by class
  .apply , For each group, query the records whose total score exceeds the average score . there query The string is the same as the previous example
Embarrassment Excel The task of
Some tasks use Excel The builtin function is very cumbersome , such as ：
" In the whole class ,8 Students whose grades exceed the grade average "
If this task uses Excel Realization , Each account must be averaged one by one , Direct look at pandas The implementation of the ：
  Define a method , This facilitates reuse of logic
  front 2 sentence , First find the average score of each subject
  Then find out the number of subjects above average for each student count
  Finally, make a simple judgment , You get the result
Look at the call result ：
Now , If you need to see "8 Students whose grades exceed the average of the class ", It's very simple ：
  These are all in the class " Three good students "
Is it right? so easy？！
Let's see which students need to stay in class to see their parents .
"8 Students whose grades are below the average in the class ", It's still simple ：
  Just add one parameter sjs=0 , You get the result
This article is from WeChat official account.  Excel catalyst （ExcelCuiHuaJi）
The source and reprint of the original text are detailed in the text , If there is any infringement , Please contact the [email protected] Delete .
Original publication time ： 20190821
Participation of this paper Tencent cloud media sharing plan , You are welcome to join us , share .
copyright notice
author[Excel catalyst],Please bring the original link to reprint, thank you.
https://en.pythonmana.com/2021/08/20210823044656698P.html
The sidebar is recommended
 [Python introduction project] use Python to generate QR code
 Quickly build Django blog based on function calculation
 Python interface test unittest usage details
 Implementation of toplevel design pattern in Python
 You can easily get started with Excel. Python data analysis package pandas (VII): breakdown
 Python simulation random coin toss (non optimized version)
 Using linear systems in python with scipy.linalg
 Together with Python to do a license plate automatic recognition system, fun and practical!
 Using linear systems in python with scipy.linalg
 Fast power modulus Python implementation of large numbers
guess what you like

Quickly build Django blog based on function calculation

You can easily get started with Excel pandas (I): filtering function

You can easily get started with Excel. Python data analysis package pandas (II): advanced filtering (I)

How does Python correctly call jar package encryption to get the encrypted value?

Python 3 interview question: give an array. If there is 0 in the array, add a 0 after 0, and the overall array length remains the same

Python simple Snake game (single player mode)

Using linear systems in python with scipy.linalg

Python executes functions and even code through strings! Come and understand the operation of such a top!

Decoding the verification code of Taobao slider with Python + selenium, the road of information security

[Python introduction project] use Python to generate QR code
Random recommended
 Vanessa basks in her photos and gets caught up in the golden python. There are highlights in the accompanying text. She can't forget Kobe after all
 [windows] Python installation pyteseract
 [introduction to Python project] create bar chart animation in Python
 Python series tutorials 116
 Practical series 1 ️⃣ Wechat applet automatic testing practice (with Python source code)
 Python Basics: do you know how to use lists?
 [common links of Python & Python]
 [Python development tool Tkinter designer]: Lecture 1: introduction to the basic functions of Tkinter Designer
 [introduction to Python tutorial] use Python 3 to teach you how to extract any HTML main content
 Python socket implements UDP server and client
 Python socket implements TCP server and client
 leetcode 1974. Minimum Time to Type Word Using Special Typewriter（python）
 The mobile phone uses Python to operate picture files
 [learning notes] Python exception handling try except...
 Two methods of using pandas to read poorly structured excel. You're welcome to take them away
 Python sum (): the summation method of Python
 Using Python to realize multitasking process