The most complete summary of Python pandas usage

1. Generate data table

1. First import the pandas library. Generally, the numpy library is used, so Let’s import the backup first:

import numpy as np
import pandas as pd

2. Import CSV or xlsx file:

df = pd.DataFrame(pd.read_csv('name.csv',header=1))
df = pd.DataFrame(pd.read_excel('name.xlsx'))

3. Use pandas to create a data table:

df = pd.DataFrame({"id":[1001,1002,1003,1004,1005,1006], 
 "date":pd.date_range('20130102', periods=6),
 "city":['Beijing ', 'SH', ' guangzhou ', 'Shenzhen', 'shanghai', 'BEIJING '],
 "age":[23,44,54,32,34,32],
 "category":['100-A','100-B','110-A','110-C','210-A','130-F'],
 "price":[1200,np.nan,2133,5433,np.nan,4432]},
columns =['id','date','city','category','age','price'])

2. Data table information View

1. Dimension view:

df.shape

2. Basic information of the data table (dimension, column name, data format, occupied space, etc.):

df.info()

3. The format of each column of data:

df.dtypes

4. The format of a certain column:

df['B'].dtype

5. Null value:

df.isnull()

6. View the null value of a certain column:

df.isnull()

7. View the unique value of a column:

df['B'].unique()

8. View the value of the data table:

df.values

9. View the column name:

df.columns

10 , View the first 10 rows of data and the last 10 rows of data:

df.head() #默认前10行数据
df.tail()    #默认后10 行数据

Related recommendations: "Python Video Tutorial"

3. Data table cleaning

1. Fill the empty values ​​with the number 0:

df.fillna(value=0)

2. Use the mean value of the column prince to fill the NA:

df['prince'].fillna(df['prince'].mean())

3. Clear the character spaces in the city field:

df['city']=df['city'].map(str.strip)

4. Case conversion:

df['city']=df['city'].str.lower()

5. Change data format:

df['price'].astype('int')

6. Change column name:

df.rename(columns={'category': 'category-size'})

7. After deletion Duplicate values ​​that appear:

df['city'].drop_duplicates()

8. Delete duplicate values ​​that appear first:

df['city'].drop_duplicates(keep='last')

9. Data replacement:

df['city'].replace('sh', 'shanghai')

4. Data preprocessing

df1=pd.DataFrame({"id":[1001,1002,1003,1004,1005,1006,1007,1008], 
"gender":['male','female','male','female','male','female','male','female'],
"pay":['Y','N','Y','Y','N','Y','N','Y',],
"m-point":[10,12,20,40,40,40,30,20]})

1. Merge data tables

df_inner=pd.merge(df,df1,how='inner')  # 匹配合并，交集
df_left=pd.merge(df,df1,how='left')        #
df_right=pd.merge(df,df1,how='right')
df_outer=pd.merge(df,df1,how='outer')  #并集

2. Set index columns

df_inner.set_index('id')

3. Sort by the value of a specific column:

df_inner.sort_values(by=['age'])

4. Sort by index column:

df_inner.sort_index()

5. If the value of the prince column is >3000, the group column displays high, otherwise it displays low:

df_inner['group'] = np.where(df_inner['price'] > 3000,'high','low')

6. Group data that combines multiple conditions Mark

df_inner.loc[(df_inner['city'] == 'beijing') & (df_inner['price'] >= 4000), 'sign']=1

7. Sort the values ​​of the category field into columns in sequence and create a data table. The index value is the index column of df_inner. The column names are category and size

pd.DataFrame((x.split('-') for x in df_inner['category']),index=df_inner.index,columns=['category','size']))

8. It will be completed. Match the split data table with the original df_inner data table

df_inner=pd.merge(df_inner,split,right_index=True, left_index=True)

5. Data extraction

The three main functions used: loc, iloc and ix, loc The function extracts by label value, iloc extracts by position, and ix can extract by label and position at the same time.

1. Extract the value of a single row by index

df_inner.loc[3]

2. Extract the value of a regional row by index

df_inner.iloc[0:5]

3. Reset the index

df_inner.reset_index()

4. Set date as index

df_inner=df_inner.set_index('date')

5. Extract all data before 4 days

df_inner[:'2013-01-04']

6. Use iloc to extract data by location area

df_inner.iloc[:3,:2] #冒号前后的数字不再是索引的标签名称，而是数据所在的位置，从0开始，前三行，前两列。

7. Adapt iloc individually by location File data

df_inner.iloc[[0,2,5],[4,5]] #提取第0、2、5行，4、5列

8. Use ix to extract data by index label and position mixture

df_inner.ix[:'2013-01-03',:4] #2013-01-03号之前，前四列数据

9. Determine whether the value of the city column is Beijing

df_inner['city'].isin(['beijing'])

10. Determine the city column contains beijing and shanghai, and then extract the data that meets the conditions

df_inner.loc[df_inner['city'].isin(['beijing','shanghai'])]

11. Extract the first three characters and generate a data table

pd.DataFrame(category.str[:3])

6. Data filtering

Use the three conditions of AND, OR, NOT and greater than, less than, and equal to filter the data, and perform counting and summing.

1. Use "AND" to filter

df_inner.loc[(df_inner['age'] > 25) & (df_inner['city'] == 'beijing'), ['id','city','age','category','gender']]

2. Use "OR" to filter

df_inner.loc[(df_inner['age'] > 25) | (df_inner['city'] == 'beijing'), ['id','city','age','category','gender']]
.sort(['age'])

3. Use "NOT" condition to filter

df_inner.loc[(df_inner['city'] != 'beijing'), ['id','city','age','category','gender']].sort(['id'])

4. Count the filtered data by city column

df_inner.loc[(df_inner['city'] != 'beijing'), ['id','city','age','category','gender']].sort(['id']).city.count()

5. Use query function to filter

df_inner.query('city == ["beijing", "shanghai"]')

6. Sum the filtered results by prince

df_inner.query('city == ["beijing", "shanghai"]').price.sum()

7. Data summary

The main functions are groupby and pivot_table

1. Count and summarize all columns

df_inner.groupby('city').count()

2. Count the id field by city

df_inner.groupby('city')['id'].count()

3. Summarize the two fields

df_inner.groupby(['city','size'])['id'].count()

4. Summarize the city field and calculate the total and mean of prince respectively

df_inner.groupby('city')['price'].agg([len,np.sum, np.mean])

8. Data statistics

Data sampling, calculation of standard deviation, covariance and correlation coefficient

1. Simple data sampling

df_inner.sample(n=3)

2. Manually set the sampling weight

weights = [0, 0, 0, 0, 0.5, 0.5]
df_inner.sample(n=2, weights=weights)

3. No replacement after sampling

df_inner.sample(n=6, replace=False)

4. Replacement after sampling

df_inner.sample(n=6, replace=True)

5. Descriptive statistics of data table

df_inner.describe().round(2).T #round函数设置显示小数位，T表示转置

6. Calculate the standard deviation of a column

df_inner['price'].std()

7. Calculate the covariance between two fields

df_inner['price'].cov(df_inner['m-point'])

8. Calculate the covariance between all fields in the data table

df_inner.cov()

9. Correlation analysis of two fields

df_inner['price'].corr(df_inner['m-point']) #相关系数在-1到1之间，接近1为正相关，接近-1为负相关，0为不相关

10. Correlation analysis of data table

df_inner.corr()

9. Data output

The analyzed data can be output to xlsx format and csv format

1, written to Excel

df_inner.to_excel('excel_to_python.xlsx', sheet_name='bluewhale_cc')

2, written to CSV

df_inner.to_csv('excel_to_python.csv')

The above is the detailed content of The most complete summary of Python pandas usage. For more information, please follow other related articles on the PHP Chinese website!