Pandas進階修煉120題系列一共涵蓋了數據處理、計算、可視化等常用操作，希望通過120道精心挑選的習題吃透pandas。並且針對部分習題給出了多種解法與註解，動手敲一遍代碼一定會讓你有所收穫！
data = {"grammer":["Python","C","Java","GO",np.nan,"SQL","PHP","Python"],
"score":[1,2,np.nan,4,5,6,7,10]}
import numpy as np
import pandas as pd
df = pd.DataFrame(data)
# 假如是直接創建
df = pd.DataFrame({
"grammer": ["Python","C","Java","GO",np.nan,"SQL","PHP","Python"],
"score": [1,2,np.nan,4,5,6,7,10]})
grammer score
0 Python 1.0
7 Python 10.0
#> 1
df[df['grammer'] == 'Python']
#> 2
results = df['grammer'].str.contains("Python")
results.fillna(value=False,inplace = True)
df[results]
Index(['grammer', 'score'], dtype='object')
df.rename(columns={'score':'popularity'}, inplace = True)
df['grammer'].value_counts()
# pandas裡有一個插值方法，就是計算缺失值上下兩數的均值
df['popularity'] = df['popularity'].fillna(df['popularity'].interpolate())
df.drop_duplicates(['grammer'])
df['popularity'].mean()
# 4.75
df['grammer'].to_list()
# ['Python', 'C', 'Java', 'GO', nan, 'SQL', 'PHP', 'Python']
df.to_excel('filename.xlsx')
df[(df['popularity'] > 3) & (df['popularity'] < 7)]
temp = df['popularity']
df.drop(labels=['popularity'], axis=1,inplace = True)
df.insert(0, 'popularity', temp)
df[df['popularity'] == df['popularity'].max()]
df = df.drop(labels=df.shape[0]-1)
row = {'grammer':'Perl','popularity':6.6}
df = df.append(row,ignore_index=True)
df.sort_values("popularity",inplace=True)
df['grammer'] = df['grammer'].fillna('R')
df['len_str'] = df['grammer'].map(lambda x: len(x))
import pandas as pd
import numpy as np
df = pd.read_excel(r'C:\Users\chenx\Documents\Data Analysis\pandas120.xlsx')
# 方法一：apply + 自定義函數
def func(df):
lst = df['salary'].split('-')
smin = int(lst[0].strip('k'))
smax = int(lst[1].strip('k'))
df['salary'] = int((smin + smax) / 2 * 1000)
return df
df = df.apply(func,axis=1)
# 方法二：iterrows + 正則
import re
for index,row in df.iterrows():
nums = re.findall('\d+',row[2])
df.iloc[index,2] = int(eval(f'({nums[0]} + {nums[1]}) / 2 * 1000'))
education salary
不限 19600.000000
大專 10000.000000
本科 19361.344538
碩士 20642.857143
df.groupby('education').mean()
for index,row in df.iterrows():
df.iloc[index,0] = df.iloc[index,0].to_pydatetime().strftime("%m-%d")
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 135 entries, 0 to 134
Data columns (total 4 columns):
createTime 135 non-null object
education 135 non-null object
salary 135 non-null int64
categories 135 non-null category
dtypes: category(1), int64(1), object(2)
memory usage: 3.5+ KB
bins = [0,5000, 20000, 50000]
group_names = ['低', '中', '高']
df['categories'] = pd.cut(df['salary'], bins, labels=group_names)
df.sort_values('salary', ascending=False)
np.median(df['salary'])
# 17500.0
# Jupyter運行matplotlib成像需要運行魔術命令
%matplotlib inline
plt.rcParams['font.sans-serif'] = ['SimHei'] # 解決中文亂碼
plt.rcParams['axes.unicode_minus'] = False # 解決符號問題
import matplotlib.pyplot as plt
plt.hist(df.salary)
# 也可以用原生pandas方法繪圖
df.salary.plot(kind='hist')
df.salary.plot(kind='kde',xlim = (0,70000))
del df['categories']
# 等價於
df.drop(columns=['categories'], inplace=True)
df['test'] = df['education'] + df['createTime']
df["test1"] = df["salary"].map(str) + df['education']
df[['salary']].apply(lambda x: x.max() - x.min())
# salary 41500
# dtype: int64
pd.concat([df[1:2], df[-1:]])
createTime object
education object
salary int64
test object
test1 object
dtype: object
df.dtypes
# createTime object
# education object
# salary int64
# test object
# test1 object
# dtype: object
df.set_index("createTime")
df1 = pd.DataFrame(pd.Series(np.random.randint(1, 10, 135)))
df= pd.concat([df,df1],axis=1)
df["new"] = df["salary"] - df[0]
df.isnull().values.any()
# False
df['salary'].astype(np.float64)
len(df[df['salary'] > 10000])
# 119
本科 119
碩士 7
不限 5
大專 4
Name: education, dtype: int64
df.education.value_counts()
df['education'].nunique()
# 4
rowsums = df[['salary','new']].apply(np.sum, axis=1)
res = df.iloc[np.where(rowsums > 60000)[0][-3:], :]
import pandas as pd
import numpy as np
df = pd.read_excel(r'C:\Users\chenx\Documents\Data Analysis\Pandas51-80.xls')
備註
請將答案中路徑替換為自己機器存儲數據的絕對路徑，51—80相關習題與該數據有關
代碼 1
簡稱 2
日期 2
前收盤價(元) 2
開盤價(元) 2
最高價(元) 2
最低價(元) 2
收盤價(元) 2
成交量(股) 2
成交金額(元) 2
..
列名："代碼", 第[327]行位置有缺失值
列名："簡稱", 第[327, 328]行位置有缺失值
列名："日期", 第[327, 328]行位置有缺失值
列名："前收盤價(元)", 第[327, 328]行位置有缺失值
列名："開盤價(元)", 第[327, 328]行位置有缺失值
列名："最高價(元)", 第[327, 328]行位置有缺失值
列名："最低價(元)", 第[327, 328]行位置有缺失值
列名："收盤價(元)", 第[327, 328]行位置有缺失值
.
for i in df.columns:
if df[i].count() != len(df):
row = df[i][df[i].isnull().values].index.tolist()
print('列名："{}", 第{}行位置有缺失值'.format(i,row))
df.dropna(axis=0, how='any', inplace=True)
備註
axis：0-行操作（默認），1-列操作
how：any-只要有空值就刪除（默認），all-全部為空值才刪除
inplace：False-返回新的數據集（默認），True-在原數據集上操作
# Jupyter運行matplotlib
%matplotlib inline
df['收盤價(元)'].plot()
# 等價於
import matplotlib.pyplot as plt
plt.plot(df['收盤價(元)'])
plt.rcParams['font.sans-serif'] = ['SimHei'] # 解決中文亂碼
plt.rcParams['axes.unicode_minus'] = False # 解決符號問題
df[['收盤價(元)','開盤價(元)']].plot()
plt.hist(df['漲跌幅(%)'])
# 等價於
df['漲跌幅(%)'].hist()
df['漲跌幅(%)'].hist(bins = 30)
temp = pd.DataFrame(columns = df.columns.to_list())
for index,row in df.iterrows():
if type(row[13]) != float:
temp = temp.append(df.loc[index])
備註
通過上一題我們發現換手率的異常值只有--
df = df.reset_index(drop=True)
備註
有時我們修改數據會導致索引混亂
lst = []
for index,row in df.iterrows():
if type(row[13]) != float:
lst.append(index)
df.drop(labels=lst,inplace=True)
df['換手率(%)'].plot(kind='kde',xlim=(0,0.6))
data['收盤價(元)'].pct_change()
題目：以5個數據作為一個數據滑動窗口，在這個5個數據上取均值(收盤價)
df['收盤價(元)'].rolling(5).mean()
題目：以5個數據作為一個數據滑動窗口，計算這五個數據總和(收盤價)
df['收盤價(元)'].rolling(5).sum()
題目：將收盤價5日均線、20日均線與原始數據繪製在同一個圖上
df['收盤價(元)'].plot()
df['收盤價(元)'].rolling(5).mean().plot()
df['收盤價(元)'].rolling(20).mean().plot()
題目：按周為採樣規則，取一周收盤價最大值
df = df.set_index('日期')
df['收盤價(元)'].resample('W').max()
題目：繪製重採樣數據與原始數據
df['收盤價(元)'].plot()
df['收盤價(元)'].resample('7D').max().plot()
df['開盤價(元)'].expanding(min_periods=1).mean()
df['expanding Open mean']=df['開盤價(元)'].expanding(min_periods=1).mean()
df[['開盤價(元)', 'expanding Open mean']].plot(figsize=(16, 6))
df['former 30 days rolling Close mean']=df['收盤價(元)'].rolling(20).mean()
df['upper bound']=df['former 30 days rolling Close mean']+2*df['收盤價(元)'].rolling(20).std()
df['lower bound']=df['former 30 days rolling Close mean']-2*df['收盤價(元)'].rolling(20).std()
df[['收盤價(元)', 'former 30 days rolling Close mean','upper bound','lower bound' ]].plot(figsize=(16, 6))
import pandas as pd
import numpy as np
print(np.__version__)
# 1.16.5
print(pd.__version__)
# 0.25.1
tem = np.random.randint(1,100,20)
df1 = pd.DataFrame(tem)
tem = np.arange(0,100,5)
df2 = pd.DataFrame(tem)
tem = np.random.normal(0, 1, 20)
df3 = pd.DataFrame(tem)
df = pd.concat([df1,df2,df3],axis=0,ignore_index=True)
0 1 2
0 95 0 0.022492
1 22 5 -1.209494
2 3 10 0.876127
3 21 15 -0.162149
4 51 20 -0.815424
5 30 25 -0.303792
df = pd.concat([df1,df2,df3],axis=1,ignore_index=True)
題目：查看df所有數據的最小值、25%分位數、中位數、75%分位數、最大值np.percentile(df, q=[0, 25, 50, 75, 100])
df.columns = ['col1','col2','col3']
df['col1'][~df['col1'].isin(df['col2'])]
temp = df['col1'].append(df['col2'])
temp.value_counts()[:3]
np.argwhere(df['col1'] % 5==0)
df['col1'].diff().tolist()
df['col1'].take([1,10,15])
# 等價於
df.iloc[[1,10,15],0]
res = np.diff(np.sign(np.diff(df['col1'])))
np.where(res== -2)[0] + 1
# array([ 2, 4, 7, 9, 12, 15], dtype=int64)
df[['col1','col2','col3']].mean(axis=1)
np.convolve(df['col2'], np.ones(3)/3, mode='valid')
df.sort_values("col3",inplace=True)
df.col1[df['col1'] > 50] = '高'
np.linalg.norm(df['col1']-df['col2'])
# 194.29873905921264
df1 = pd.read_csv(r'C:\Users\chenx\Documents\Data Analysis\數據1.csv',encoding='gbk', usecols=['positionName', 'salary'],nrows = 10)
df2 = pd.read_csv(r'C:\Users\chenx\Documents\Data Analysis\數據2.csv',
converters={'薪資水平': lambda x: '高' if float(x) > 10000 else '低'} )
期望結果
df2.iloc[::20, :][['薪資水平']]
df = pd.DataFrame(np.random.random(10)**10, columns=['data'])
期望結果
Python解法
df = pd.DataFrame(np.random.random(10)**10, columns=['data'])
df.round(3)
df.style.format({'data': '{0:.2%}'.format})
df['data'].argsort()[len(df)-3]
df1= pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],
'key2': ['K0', 'K1', 'K0', 'K1'],
'A': ['A0', 'A1', 'A2', 'A3'],
'B': ['B0', 'B1', 'B2', 'B3']})
df2= pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],
'key2': ['K0', 'K0', 'K0', 'K0'],
'C': ['C0', 'C1', 'C2', 'C3'],
'D': ['D0', 'D1', 'D2', 'D3']})
pd.merge(df1, df2, on=['key1', 'key2'])
備註
只保存df1的數據
pd.merge(df1, df2, how='left', on=['key1', 'key2'])
left_join(df1,df2,by = c('key1','key2'))
df = pd.read_csv(r'C:\Users\chenx\Documents\Data Analysis\數據1.csv',encoding='gbk')
pd.set_option("display.max.columns", None)
np.where(df.secondType == df.thirdType)
np.argwhere(df['salary'] > df['salary'].mean())[2]
# array([5], dtype=int64)
df[['salary']].apply(np.sqrt)
df['split'] = df['linestaion'].str.split('_')
df[df['industryField'].str.startswith('數據')]
pd.pivot_table(df,values=["salary","score"],index="positionId")
df[["salary","score"]].agg([np.sum,np.mean,np.min])
df.agg({"salary":np.sum,"score":np.mean})
df[['district','salary']].groupby(by='district').mean().sort_values(
'salary',ascending=False).head(1)
以上就是Pandas進階修煉120題全部內容，如果能堅持走到這裡的讀者，我想你已經掌握了處理數據的常用操作，並且在之後的數據分析中碰到相關問題，希望武裝了Pandas的你能夠從容的解決！
習題與源碼電子版：
https://pan.baidu.com/s/1MSqmWMiurHJSXyNBlJOEpw
密碼:8mkx
免費開放，從 Python入門到Tensorflow基礎與實踐【天池大賽】3D人工智慧挑戰賽