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)
2021-08-23 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 built-in 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 : 2019-08-21
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 top-level 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