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

Preface

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 .

data

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 .