Pandas的DataFrame

1 a = [[1, 2, 2],[3,None,6],[3, 7, None],[5,None,7]]
2 data = DataFrame(a)

 1 import os
 2 import pandas as pd
 3 base_path = "D:\\practicespace\\Python\\datasets"
 4 file_name = "data.xlsx"
 5 path = os.path.join(base_path, file_name)
 6 print(path)
 7 if(os.path.exists(path)):
 8     print("file exists")
 9 
10 data = pd.read_excel(path, sheet_name="Sheet4", header=2, usecols="C:J")
11 data.head()

1 print("region count: ", len(data.Region.value_counts()))
2 print("Sub Region count: ", len(data["Sub Region"].value_counts()))

df = df.rename(columns=lambda x: x.replace("‘","").replace(‘"‘,‘‘)).replace(" ","")

1 total = data.isnull().sum().sort_values(ascending=True)
2 percent = (data.isnull().sum()/data.isnull().count()).sort_values(ascending=True)
3 table = pd.concat([total, percent], axis=1, keys=["total", "percent"])

1 # 使用dropna方法删除含有缺失值的行，默认是行
2 print(data.dropna())
3 # 删除含有缺失值的列
4 print(data.dropna(axis=1))

 1 from pandas import DataFrame
 2 a = [[1, 2, 2],[3,None,6],[3, 7, None],[5,None,7]]
 3 data = DataFrame(a)
 4 print(data)
 5 # 统一填充缺失值为指定值
 6 print(data.fillna(0))
 7 # index=1（从0开始）列缺失值填充为1，index=2的列的缺失值填充为2
 8 print(data.fillna({1:1,2:2}))
 9 # 使用平均值进行填充
10 print(data.fillna(data.mean()))
11 # 前向填充，使用默认是上一行的值,设置axis=1可以使用列进行填充，不存在或者上一行也是None的时候就不填充
12 print(data.fillna(method="ffill"))
13 print()
14 # 后向填充，使用下一行的值,不存在或者下一行也是None的时候就不填充
15 print(data.fillna(method="bfill"))

 1 # 最原始，效率最低的迭代方案
 2 def myfunction(df):
 3     res_list = []
 4     for i in range(0,len(df)):
 5         res_list.append(df.iloc[i][‘first‘]/df.iloc[i][‘second‘])
 6     return disftance_list
 7 # 通过iterrows做遍历
 8 def haversine_looping(df):
 9     disftance_list = []
10     for index,row in df.iterrows():
11         disftance_list.append(row[‘high‘]/row[‘open‘])
12     return disftance_list
13 # Cython做了全局优化，效率比iterrow有所提高，这里注意axis必须要设置
14 df.apply(lambda row: row[‘high‘]/row[‘open‘], axis =1)
15 # pandas的矢量化处理，比较快，做了底层实现优化
16 dftest4[‘rate‘] = dftest4[‘high‘]/dftest4[‘open‘]
17 # 通过values将pandas的serias数据转化为numpy arrays，效率最高，因为numpy在底层做了C的预编译
18 dftest5[‘rate‘] = dftest5[‘high‘].values/dftest5[‘open‘].values

1 df1[‘total‘] = df1.Jan + df1.Feb + df1.Mar
2 df1[‘category‘] = np.where(df1[‘total‘] > 200000, ‘A‘, ‘B‘)

1 del DF[‘column-name‘]
2 DF= DF.drop(‘column_name‘, 1)；
3 DF.drop(‘column_name‘,axis=1, inplace=True)
4 DF.drop([DF.columns[[0,1, 3]]], axis=1,inplace=True)   # Note: zero indexed

1 # 单分组
2 groupall = data.groupby("Region")
3 groupall = groupall.sum()
4 groupall
5 # 多分组
6 groupall = data.groupby(["Region", "Sub Region"])
7 groupall = groupall.sum()
8 groupall