The most complete summary of Python pandas usage-Python Tutorial-php.cn

The most complete summary of Python pandas usage

爱喝马黛茶的安东尼

Release： 2019-08-03 17:57:43

forward

15428 people have browsed it

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

Copy after login

2. Import CSV or xlsx file:

df = pd.DataFrame(pd.read_csv(&#39;name.csv&#39;,header=1))
df = pd.DataFrame(pd.read_excel(&#39;name.xlsx&#39;))

Copy after login

3. Use pandas to create a data table:

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

Copy after login

2. Data table information View

1. Dimension view:

df.shape

Copy after login

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

df.info()

Copy after login

3. The format of each column of data:

df.dtypes

Copy after login

4. The format of a certain column:

df[&#39;B&#39;].dtype

Copy after login

5. Null value:

df.isnull()

Copy after login

6. View the null value of a certain column:

df.isnull()

Copy after login

7. View the unique value of a column:

df[&#39;B&#39;].unique()

Copy after login

8. View the value of the data table:

df.values

Copy after login

9. View the column name:

df.columns

Copy after login

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

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

Copy after login

Related recommendations: "Python Video Tutorial"

3. Data table cleaning

1. Fill the empty values with the number 0:

df.fillna(value=0)

Copy after login

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

df[&#39;prince&#39;].fillna(df[&#39;prince&#39;].mean())

Copy after login

3. Clear the character spaces in the city field:

df[&#39;city&#39;]=df[&#39;city&#39;].map(str.strip)

Copy after login

4. Case conversion:

df[&#39;city&#39;]=df[&#39;city&#39;].str.lower()

Copy after login

5. Change data format:

df[&#39;price&#39;].astype(&#39;int&#39;)

Copy after login

6. Change column name:

df.rename(columns={&#39;category&#39;: &#39;category-size&#39;})

Copy after login

7. After deletion Duplicate values that appear:

df[&#39;city&#39;].drop_duplicates()

Copy after login

8. Delete duplicate values that appear first:

df[&#39;city&#39;].drop_duplicates(keep=&#39;last&#39;)

Copy after login

9. Data replacement:

df[&#39;city&#39;].replace(&#39;sh&#39;, &#39;shanghai&#39;)

Copy after login

4. Data preprocessing

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

Copy after login

1. Merge data tables

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

Copy after login

2. Set index columns

df_inner.set_index(&#39;id&#39;)

Copy after login

3. Sort by the value of a specific column:

df_inner.sort_values(by=[&#39;age&#39;])

Copy after login

4. Sort by index column:

df_inner.sort_index()

Copy after login

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

df_inner[&#39;group&#39;] = np.where(df_inner[&#39;price&#39;] > 3000,&#39;high&#39;,&#39;low&#39;)

Copy after login

6. Group data that combines multiple conditions Mark

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

Copy after login

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(&#39;-&#39;) for x in df_inner[&#39;category&#39;]),index=df_inner.index,columns=[&#39;category&#39;,&#39;size&#39;]))

Copy after login

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)

Copy after login

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]

Copy after login

2. Extract the value of a regional row by index

df_inner.iloc[0:5]

Copy after login

3. Reset the index

df_inner.reset_index()

Copy after login

4. Set date as index

df_inner=df_inner.set_index(&#39;date&#39;)

Copy after login

5. Extract all data before 4 days

df_inner[:&#39;2013-01-04&#39;]

Copy after login

6. Use iloc to extract data by location area

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

Copy after login

7. Adapt iloc individually by location File data

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

Copy after login

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

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

Copy after login

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

df_inner[&#39;city&#39;].isin([&#39;beijing&#39;])

Copy after login

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

df_inner.loc[df_inner[&#39;city&#39;].isin([&#39;beijing&#39;,&#39;shanghai&#39;])]

Copy after login

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

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

Copy after login

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[&#39;age&#39;] > 25) & (df_inner[&#39;city&#39;] == &#39;beijing&#39;), [&#39;id&#39;,&#39;city&#39;,&#39;age&#39;,&#39;category&#39;,&#39;gender&#39;]]

Copy after login

2. Use "OR" to filter

df_inner.loc[(df_inner[&#39;age&#39;] > 25) | (df_inner[&#39;city&#39;] == &#39;beijing&#39;), [&#39;id&#39;,&#39;city&#39;,&#39;age&#39;,&#39;category&#39;,&#39;gender&#39;]]
.sort([&#39;age&#39;])

Copy after login

3. Use "NOT" condition to filter

df_inner.loc[(df_inner[&#39;city&#39;] != &#39;beijing&#39;), [&#39;id&#39;,&#39;city&#39;,&#39;age&#39;,&#39;category&#39;,&#39;gender&#39;]].sort([&#39;id&#39;])

Copy after login

4. Count the filtered data by city column

df_inner.loc[(df_inner[&#39;city&#39;] != &#39;beijing&#39;), [&#39;id&#39;,&#39;city&#39;,&#39;age&#39;,&#39;category&#39;,&#39;gender&#39;]].sort([&#39;id&#39;]).city.count()

Copy after login

5. Use query function to filter

df_inner.query(&#39;city == ["beijing", "shanghai"]&#39;)

Copy after login

6. Sum the filtered results by prince

df_inner.query(&#39;city == ["beijing", "shanghai"]&#39;).price.sum()

Copy after login

7. Data summary

The main functions are groupby and pivot_table

1. Count and summarize all columns

df_inner.groupby(&#39;city&#39;).count()

Copy after login

2. Count the id field by city

df_inner.groupby(&#39;city&#39;)[&#39;id&#39;].count()

Copy after login

3. Summarize the two fields

df_inner.groupby([&#39;city&#39;,&#39;size&#39;])[&#39;id&#39;].count()

Copy after login

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

df_inner.groupby(&#39;city&#39;)[&#39;price&#39;].agg([len,np.sum, np.mean])

Copy after login

8. Data statistics

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

1. Simple data sampling

df_inner.sample(n=3)

Copy after login

2. Manually set the sampling weight

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

Copy after login

3. No replacement after sampling

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

Copy after login

4. Replacement after sampling

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

Copy after login

5. Descriptive statistics of data table

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

Copy after login

6. Calculate the standard deviation of a column

df_inner[&#39;price&#39;].std()

Copy after login

7. Calculate the covariance between two fields

df_inner[&#39;price&#39;].cov(df_inner[&#39;m-point&#39;])

Copy after login

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

df_inner.cov()

Copy after login

9. Correlation analysis of two fields

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

Copy after login

10. Correlation analysis of data table

df_inner.corr()

Copy after login

9. Data output

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

1, written to Excel

df_inner.to_excel(&#39;excel_to_python.xlsx&#39;, sheet_name=&#39;bluewhale_cc&#39;)

Copy after login

2, written to CSV

df_inner.to_csv(&#39;excel_to_python.csv&#39;)

Copy after login

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!