current position:Home>You can easily get started with Excel pandas (I): filtering function

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

2021-08-23 04:42:13 Excel catalyst


I often listen to others Python How powerful the data field is , 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 .

Excel It has its own screening function , You can filter various conditions in the table . We'll use it today pandas See how to do it Excel The screening function of , And look at Excel Functions that you can't do .


The sample data in this article are as follows :

  • The data is generated in code , Just some personal information data

Load data

pandas Need to load Excel data , Very easy to , as follows :

  • - import pandas as pd , Import pandas package
  • - pd.read_excel , You can load Excel data
  • - specify the path to a file , Because the file is in Python Script same as directory , Enter the file name directly
  • - sheet_name Specify which worksheet to read
  • - There are many other parameters , Our data this time is very standard , Therefore, no other parameters are required

Filter by location

Excel The filter of can only be operated according to the column value , So we add a sequence number column to the table . Look at the picture :

  • - In order to pandas Row indexes are consistent , The column values added here are from 0 Start

Then try ," According to the first 3 to 6 That's ok ", as follows :

  • - Function card " data " page , stay " Sorting and filtering " Click on the big " Screening " Icon
  • - Click the lower label of the first row and the first column
  • - The default is to select all , click " Future generations ", You can cancel all selected
  • - Click the corresponding values respectively

have a look pandas How to do , as follows :

  • - pandas Medium DataFrame Own row index
  • - Use it directly df.loc[2:5]

Filter by value

Location filtering is too simple , Very few scenarios use . Look at this time " Show all men " Record .

Excel Not introduced , have a look pandas How to complete :

  • - df['sex'] , It's gender
  • - df['sex']=='M' , Semantic clarity , Gender equals M

There are many conditions ," It shows a male and the blood type is A+", as follows :

Of course , There is a simpler way to write , as follows :

  • - query Method , You can directly accept a query string , Is it very similar Sql Well

Specifying multiple values is also simple ," What's the blood type A+ or B-", as follows :

  • - The query string can be used directly in

Pattern matching

Too simple. ? A little more complicated .

" The initial letter of blood group value is A" The record of .Excel Screening in is also powerful , It has this function directly . as follows :

pandas The corresponding operation is as follows :

  • - Blood type Columns are text types , So you can use .str , To use a series of text shortcuts

Of course ,pandas Text processing functions in Excel Much stronger , Let's see .

" The address contains tianjin 3 word " The record of . as follows :

what ,Excel It's fine too ? Let's take a look at the following requirements .

" The address includes x seat ,x It's the letters a to c, Both case and case count ", have a look pandas How do you do it? :

  • - contains Methods can be regular expressions

Range of values

" The income is in 5000 to 8000 Of men ",pandas as follows :

Easy .Excel The operation is also simple , Don't show .

Look at this demand .

" Was born in 1980 to 1990 Between men "

The tip of the iceberg

Excel The filtering function of is undoubtedly powerful , however pandas It's also very powerful . There must be a little friend who will say , since Excel It has its own functions , use pandas Why? ? Automation, of course . also pandas There are many functions in , stay Excel You need to use complex function formulas or Vba Can achieve .

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.

Random recommended