About Excel, you must use 36 Python functions

From Excel to Python: the most commonly used 36 Pandas functions
About Excel, you must use 36 Python functions

This paper deals with the 36 most commonly used functions of pandas. Through these functions, it introduces how to complete data generation and import, data cleaning, preprocessing, as well as the most common data classification, data filtering, classification summary, perspective and other most common operations.

Generate data table

There are two common methods to generate data tables, the first is to import external data, and the second is to write data directly.



The "file" menu in Excel provides the function of obtaining external data, and supports the import of multiple data sources of database, text file and page.

Python supports importing from multiple types of data. You need to import numpy and pandas libraries before you start to import data in Python

import numpy as np
import pandas as pd

Import external data


There are many optional parameter settings, such as column name, index column, data format, etc

Write data directly

df = pd.DataFrame({"id":[1001,1002,1003,1004,1005,1006],
"date":pd.date_range('20130102', periods=6),
"city":['Beijing ', 'SH', ' guangzhou ', 'Shen
zhen', 'shanghai', 'BEIJING '],
columns =['id','date','city','category','age',

Data sheet check

The purpose of data table inspection is to understand the overall situation of the data table, obtain the key information and data overview of the data table, such as the size, occupied space, data format, whether there are empty values, duplicates and specific data content of the whole data table, so as to prepare for the subsequent cleaning and pretreatment.

1. Data dimension (row and column)

You can view row and column numbers in Excel by using CTRL + down cursor keys and CTRL + right cursor keys. In Python, the shape function is used to view the dimensions of the data table, that is, the number of rows and columns.


2. Data sheet information

Use the info function to view the overall information of the data table, including data dimension, column name, data format and occupied space. #Data sheet information

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 6 entries, 0 to 5
Data columns (total 6 columns):
id 6 non-null int64
date 6 non-null datetime64[ns]
city 6 non-null object
category 6 non-null object
age 6 non-null int64
price 4 non-null float64
dtypes: datetime64[ns](1), float64(1), int64(2), object(2)
memory usage: 368.0+ bytes

3. View data format

Excel determines the format of data by selecting cells and viewing the value types in the start menu. The dtypes function is used in Python to return the data format.

Dtypes is a function for viewing data formats. You can view all data formats in a data table at once, or you can specify a column to view separately

#View data table column formats
id int64
date datetime64[ns]
city object
category object
age int64
price float64
dtype: object
#View single column format

4. View null value

The way to view null values in Excel is to use "find and select" directory under "start" directory with "positioning conditions"

Isnull is a function that checks for null values in Python

#Check data null
#Check for specific column nulls

5. View unique value

The way to view unique values in Excel is to color them using conditional formatting.

Use the unique function in Python to view unique values.

#View unique values in the city column
array(['Beijing ', 'SH', ' guangzhou ', 'Shenzhen', 'shanghai', '
BEIJING '], dtype=object)

6. View data table values

The Values function in Python is used to view the Values in the data table

#View values of data table

7. View column name

The colors function is used to view the column names in the data table separately.

#View column name
Index(['id', 'date', 'city', 'category', 'age', 'price'], dtype='

8. View the first 10 rows of data

The Head function is used to view the first N rows of data in the data table

#View the first three rows of data

9. View the last 10 rows of data

The number of Tail rows is the opposite of the head function, which is used to view the data of the last N rows in the data table

#View last 3 lines

Data sheet cleaning

This chapter introduces how to clean the problems in the data table, including the handling of null value, case problem, data format and duplicate value.

1. Process null values (delete or fill)

Null values can be processed through find and replace function in Excel

The method of handling null values in Python is flexible. You can use Dropna function to delete the data with null values in the data table, or use fillna function to fill in null values.

#Delete rows with null values in the data table

You can also use numbers to fill in null values

#Fill the data table with the number 0

Use the mean value of the price column to fill in the NA field. Also use the fillna function. Use the mean function in the value to be filled to calculate the current mean value of the price column, and then use the mean value to fill in the NA field.

#useprice Mean pairNA Fill in
0    1200.0
1    3299.5
2    2133.0
3    5433.0
4    3299.5
5    4432.0
Name: price, dtype: float64

2. Clear space

Space in character is also a common problem in data cleaning

#Clear character spaces in city fields

3. Case conversion

In English fields, it is also a common problem that the case of letters is not uniform. Excel has functions such as UPPER and LOWER. Python also has functions with the same name to solve case problems.

#city column case conversion

4. Change data format

The data format can be modified in Excel through the format cell function.

In Python, the astype function is used to modify the data format.

#Change data format
0 1200
1 3299
2 2133
3 5433
4 3299
5 4432
Name: price, dtype: int32

5. Change column name

Rename is a function to change the column name. We will change the category column in the data table to category size in the future.

#Change column name
df.rename(columns={'category': 'category-size'})

6. Delete duplicate values

Excel data directory has the function of "delete duplicates"

Delete duplicate values in Python using the drop? Duplicates function

0 beijing
1 sh
2 guangzhou
3 shenzhen
4 shanghai
5 beijing
Name: city, dtype: object

There are duplicates in the city column, which are deleted in the first and last drop ABCD duplicates() functions respectively

#Duplicate values after deletion
0 beijing
1 sh
2 guangzhou
3 shenzhen
4 shanghai
Name: city, dtype: object

After setting the keep='last 'parameter, in contrast to the previous result of deleting duplicate values, the first occurrence of beijing is deleted

#Delete previous duplicate values
1 sh
2 guangzhou
3 shenzhen
4 shanghai
5 beijing
Name: city, dtype: objec

7. Value modification and replacement

Using "find and replace" function in Excel can realize the replacement of numerical value

Using replace function to realize data replacement in Python

#Data replacement
df['city'].replace('sh', 'shanghai')
0 beijing
1 shanghai
2 guangzhou
3 shenzhen
4 shanghai
5 beijing
Name: city, dtype: object

Data preprocessing

This chapter mainly deals with data preprocessing, sorting out the cleaned data for later statistics and analysis. It mainly includes data table merging, sorting, numerical value sorting, data grouping and marking.

1. Data table consolidation

There is no function of data table merging in Excel, which can be realized step by step by VLOOKUP function. In Python, it can be implemented at one time through the merge function.

#Establish df1 data table

The merge function is used to merge the two data tables. The merge method is inner, which matches the data shared by the two data tables together to generate a new data table. And it is named DF ﹣ inner.

#Data table match merge

There are also left, right and outer ways to merge


2. Set index column

Index column can be used for data extraction, summary and data filtering

#Set index column

3. Sort (by index, by value)

In Excel, you can sort the data table directly through the sort button in the data directory

In Python, you need to use the ort_values function and sort_index function to complete sorting

#Sort by value of a specific column

The sort? Index function is used to sort the data table by the value of the index column.

#Sort by index column

4. Data grouping

In Excel, you can use the VLOOKUP function to perform approximate matching to complete grouping of values, or use the PivotTable to complete grouping

Where function is used to judge and group data in Python

#If the price column's value is > 3000, the group column shows high, otherwise low
df_inner['group'] = np.where(df_inner['price'] > 3000,'high','low

You can also group data after judging the values of multiple fields. In the following code, the data with city column equal to beijing and price column greater than or equal to 4000 is marked as 1.

#Group data with multiple conditions
df_inner.loc[(df_inner['city'] == 'beijing') & (df_inner['price']
>= 4000), 'sign']=1

5. Data breakdown

"Column" function is provided under the data directory in Excel.

In Python, we use the split function to realize that the data listed in the category column of the data table contains two information. The first number is the category id, and the last letter is the size value. The middle is hyphenated. We use the split function to split this field and match the split data table back to the original data table.

#The values of the category field are listed in turn, and a data table is created. The index value is the index column of df_inner, and the column name is called category and size
pd.DataFrame((x.split('-') for x in df_inner['category']),index=d
#Match the completed data table with the original DF ﹣ inner data table
df_inner=pd.merge(df_inner,split,right_index=True, left_index=Tru

Data extraction

1. Extract by label (loc)

#Extract the value of a single row by index
id 1004
date 2013-01-05 00:00:00
city shenzhen
category 110-C
age 32
price 5433
gender female
m-point 40
pay Y
group high
sign NaN
category_1 110
size C
Name: 3, dtype: object

Use colons to limit the range of extracted data, preceded by the start tag value and followed by the end tag value.

#Extract area row values by index

The reset? Index function is used to recover the index. Here, we reset the date of the date field to the index of the data table, and extract the data according to the date.

#Reset index
#Set date as index
#Extract all data before 4 days

2. Extraction by location (iloc)

Use the iloc function to extract the data in the data table by location. The number before and after the colon is no longer the label name of the index, but the location of the data, starting from 0.

#useiloc Extract data by location area

In addition to extracting data by region, iloc function can also extract data by location one by one

#useiloc Extract data individually by location

The 0,2,5 in the square brackets at the front represents the position of the row where the data is located, and the number in the square brackets at the back represents the position of the column where the data is located.

3. Extract by label and location (ix)

ix is a mixture of loc and iloc, which can extract data by index tag and location

#Using ix to extract data by index label and location

4. Extract by condition (area and condition value)

Use the two functions of loc and isin to extract data according to specified conditions

#Determine whether the value of the city column is beijing
2013-01-02 True
2013-01-05 False
2013-01-07 True
2013-01-06 False
2013-01-03 False
2013-01-04 False
Name: city, dtype: bool

The isin function is nested in the data extraction function of loc, and the result is extracted as the Ture data. Here we change the judgment condition to whether the city value is beijing and shanghai. If so, extract this data.

#First, determine whether the city column contains beijing and shanghai, and then extract the data of composite conditions.

Data filtering

Filter by criteria (and, or, not)

The "filter" function is provided under the Excel data directory, which is used to filter the data table according to different conditions.

In Python, the loc function is used in conjunction with the filter criteria to complete the filtering function. With sum and count functions, the functions of sumif and counttif in Excel can also be realized. Use the and condition to filter if you are older than 25 and the city is beijing.

#Filter with and criteria
df_inner.loc[(df_inner['age'] > 25) & (df_inner['city'] == 'beiji
ng'), ['id','city','age','category','gender']]/
#Filter with or criteria
df_inner.loc[(df_inner['age'] > 25) | (df_inner['city'] == 'beiji
ng'), ['id','city','age','category','gender']].sort(['age'])
#Filter with non criteria
df_inner.loc[(df_inner['city'] != 'beijing'), ['id','city','age',

Add the city column after the previous code and use the count function to count. Function equivalent to countifs function in Excel

#Count the filtered data by city column
df_inner.loc[(df_inner['city'] != 'beijing'), ['id','city','age',

Another way to filter is to use the query function

#Filter using the query function
df_inner.query('city == ["beijing", "shanghai"]')

Add the price field and sum function after the previous code. Sum the filtered price field, which is equivalent to the function of sumifs in Excel.

#Sum the filtered results by price
df_inner.query('city == ["beijing", "shanghai"]').price.sum()

Data summary

Excel uses subtotal and pivoting to summarize data by specific dimensions. The main functions used in Python are groupby and pivot table.

1. Classification and summary

#Count all columns
#Count and summarize specific ID columns
beijing 2
guangzhou 1
shanghai 2
shenzhen 1
Name: id, dtype: int64
#Summarize and count the two fields
city size
beijing A 1
F 1
guangzhou A 1
shanghai A 1
B 1
shenzhen C 1
Name: id, dtype: int64

You can also calculate the summarized data by multiple dimensions at the same time

#Summarize the city field and calculate the total and average value of price.
df_inner.groupby('city')['price'].agg([len,np.sum, np.mean])

2. Data perspective

In Python, the same effect is achieved through the pivot table function

#Set city as the row field, size as the column field, and price as the value field.
The quantity and amount of price are calculated separately and summarized by rows and columns.

data statistics

1. Data sampling



The data sampling function is provided in the data analysis function of Excel

Python completes data sampling through the sample function

#Simple data sampling

The Weights parameter is the weight of the sampling. You can change the result of the sampling by setting different Weights

#Set sampling weight manually
weights = [0, 0, 0, 0, 0.5, 0.5]
df_inner.sample(n=2, weights=weights)

The parameter replace in the Sample function is used to set whether to put it back after sampling

#Do not put back after sampling
df_inner.sample(n=6, replace=False)
#Put it back after sampling
df_inner.sample(n=6, replace=True)

2. Descriptive statistics

Data can be described and counted through description in Python

#Descriptive statistics of data table

3. Correlation analysis

In Python, the corr function completes the operation of correlation analysis and returns the correlation coefficient.

#correlation analysis 
#Data table correlation analysis

data output

1. Write to Excel

#Export to Excel format
df_inner.to_Excel('Excel_to_Python.xlsx', sheet_name='bluewhale_c

2. Write csv

#Export to CSV format
#In the process of learning Python, I often don't want to learn it because there is no information or guidance, so I specially prepared a group of 592539176. There are a large number of PDF books and tutorials in the group for free use! No matter which stage you learn, you can get the corresponding information!

Reference resources

From Excel to Python: an advanced guide to data analysis

Keywords: Python Excel Database

Added by splitinfo on Mon, 23 Dec 2019 23:28:54 +0200