這篇文章是『Pandas進(jìn)階修煉120題』的匯總，我們對(duì)Pandas中常用的操作以習(xí)題的形式發(fā)布。從讀取數(shù)據(jù)到高級(jí)操作全部包含，希望可以通過刷題的方式來完整學(xué)習(xí)pandas中數(shù)據(jù)處理的各種方法，當(dāng)然如果你是高手，也歡迎嘗試給出與答案不同的解法。
data = {'grammer':['Python','C','Java','GO','R','SQL','PHP','Python'],
'score':[1,2,np.nan,4,5,6,7,10]}
df = pd.DataFrame(data)grammer score
0 Python 1.0
7 Python 10.0result=df[df['grammer'].str.contains('Python')]Index(['grammer', 'score'], dtype='object')df.columnsdf.rename(columns={'score':'popularity'}, inplace = True)df['grammer'].value_counts()df['popularity'] = df['popularity'].fillna(df['popularity'].interpolate())df[df['popularity'] > 3]df.drop_duplicates(['grammer'])df['popularity'].mean()df['grammer'].to_list()df.to_excel('filename.xlsx')df.shapedf[(df['popularity'] > 3) & (df['popularity'] < 7)]'''
方法1
'''
temp = df['popularity']
df.drop(labels=['popularity'], axis=1,inplace = True)
df.insert(0, 'popularity', temp)
df
'''
方法2
cols = df.columns[[1,0]]
df = df[cols]
df
'''df[df['popularity'] == df['popularity'].max()]df.tail()df.drop([len(df)-1],inplace=True)row={'grammer':'Perl','popularity':6.6}
df = df.append(row,ignore_index=True)df.sort_values('popularity',inplace=True)df['grammer'].map(lambda x: len(x))
第二期：數(shù)據(jù)處理基礎(chǔ)
df = pd.read_excel('pandas120.xlsx')本期部分習(xí)題與該數(shù)據(jù)相關(guān)
df.head()
#備注，在某些版本pandas中.ix方法可能失效，可使用.iloc，參考https://mp.weixin.qq.com/s/5xJ-VLaHCV9qX2AMNOLRtw
#為什么不能直接使用max，min函數(shù)，因?yàn)槲覀兊臄?shù)據(jù)中是20k-35k這種字符串，所以需要先用正則表達(dá)式提取數(shù)字
import re
for i in range(len(df)):
str1 = df.ix[i,2]
k = re.findall(r'\d+\.?\d*',str1)
salary = ((int(k[0]) + int(k[1]))/2)*1000
df.ix[i,2] = salary
dfeducation salary
不限 19600.000000
大專 10000.000000
本科 19361.344538
碩士 20642.857143df.groupby('education').mean()
#備注，在某些版本pandas中.ix方法可能失效，可使用.iloc，參考https://mp.weixin.qq.com/s/5xJ-VLaHCV9qX2AMNOLRtw
for i in range(len(df)):
df.ix[i,0] = df.ix[i,0].to_pydatetime().strftime('%m-%d')
df.head()<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+ KBdf.info()df.describe()bins = [0,5000, 20000, 50000]
group_names = ['低', '中', '高']
df['categories'] = pd.cut(df['salary'], bins, labels=group_names)df.sort_values('salary', ascending=False)df.loc[32]np.median(df['salary'])df.salary.plot(kind='hist')df.salary.plot(kind='kde',xlim=(0,80000))del df['categories']df['test'] = df['education']+df['createTime']df['test1'] = df['salary'].map(str) + df['education']df[['salary']].apply(lambda x: x.max() - x.min())pd.concat([df[:1], df[-2:-1]])df.append(df.iloc[7])createTime object
education object
salary int64
test object
test1 object
dtype: objectdf.dtypesdf.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()df['salary'].astype(np.float64)len(df[df['salary']>10000])本科 119
碩士 7
不限 5
大專 4
Name: education, dtype: int64df.education.value_counts()df['education'].nunique()df1 = df[['salary','new']]
rowsums = df1.apply(np.sum, axis=1)
res = df.iloc[np.where(rowsums > 60000)[0][-3:], :]第三期：金融數(shù)據(jù)處理
data = pd.read_excel('/Users/Desktop/600000.SH.xls')備注
請(qǐng)將答案中路徑替換為自己機(jī)器存儲(chǔ)數(shù)據(jù)的絕對(duì)路徑，本期相關(guān)習(xí)題與該數(shù)據(jù)有關(guān)
data.head(3)代碼 1
簡(jiǎn)稱 2
日期 2
前收盤價(jià)(元) 2
開盤價(jià)(元) 2
最高價(jià)(元) 2
最低價(jià)(元) 2
收盤價(jià)(元) 2
成交量(股) 2
成交金額(元) 2
.................答案
data.isnull().sum()data[data['日期'].isnull()]列名：'代碼', 第[327]行位置有缺失值
列名：'簡(jiǎn)稱', 第[327, 328]行位置有缺失值
列名：'日期', 第[327, 328]行位置有缺失值
列名：'前收盤價(jià)(元)', 第[327, 328]行位置有缺失值
列名：'開盤價(jià)(元)', 第[327, 328]行位置有缺失值
列名：'最高價(jià)(元)', 第[327, 328]行位置有缺失值
列名：'最低價(jià)(元)', 第[327, 328]行位置有缺失值
列名：'收盤價(jià)(元)', 第[327, 328]行位置有缺失值
................答案
for columname in data.columns:
if data[columname].count() != len(data):
loc = data[columname][data[columname].isnull().values==True].index.tolist()
print('列名：'{}', 第{}行位置有缺失值'.format(columname,loc))data.dropna(axis=0, how='any', inplace=True)備注
axis：0-行操作（默認(rèn)），1-列操作
how：any-只要有空值就刪除（默認(rèn)），all-全部為空值才刪除
inplace：False-返回新的數(shù)據(jù)集（默認(rèn)），True-在原數(shù)據(jù)集上操作data['收盤價(jià)(元)'].plot()答案
data[['收盤價(jià)(元)','開盤價(jià)(元)']].plot()備注
中文顯示請(qǐng)自己設(shè)置，我的字體亂了
答案
data['漲跌幅(%)'].hist()data['漲跌幅(%)'].hist(bins = 30)temp = pd.DataFrame(columns = data.columns.to_list())for i in range(len(data)):
if type(data.iloc[i,13]) != float:
temp = temp.append(data.loc[i])
tempdata[data['換手率(%)'].isin(['--'])]備注
通過上一題我們發(fā)現(xiàn)換手率的異常值只有--
data = data.reset_index()備注
有時(shí)我們修改數(shù)據(jù)會(huì)導(dǎo)致索引混亂
k =[]
for i in range(len(data)):
if type(data.iloc[i,13]) != float:
k.append(i)
data.drop(labels=k,inplace=True)data['換手率(%)'].plot(kind='kde')data['收盤價(jià)(元)'].diff()data['收盤價(jià)(元)'].pct_change()data.set_index('日期')題目：以5個(gè)數(shù)據(jù)作為一個(gè)數(shù)據(jù)滑動(dòng)窗口，在這個(gè)5個(gè)數(shù)據(jù)上取均值(收盤價(jià))
data['收盤價(jià)(元)'].rolling(5).mean()題目：以5個(gè)數(shù)據(jù)作為一個(gè)數(shù)據(jù)滑動(dòng)窗口，計(jì)算這五個(gè)數(shù)據(jù)總和(收盤價(jià))
data['收盤價(jià)(元)'].rolling(5).sum()題目：將收盤價(jià)5日均線、20日均線與原始數(shù)據(jù)繪制在同一個(gè)圖上
data['收盤價(jià)(元)'].plot()
data['收盤價(jià)(元)'].rolling(5).mean().plot()
data['收盤價(jià)(元)'].rolling(20).mean().plot()題目：按周為采樣規(guī)則，取一周收盤價(jià)最大值
data['收盤價(jià)(元)'].resample('W').max()題目：繪制重采樣數(shù)據(jù)與原始數(shù)據(jù)
data['收盤價(jià)(元)'].plot()
data['收盤價(jià)(元)'].resample('7D').max().plot()data.shift(5)data.shift(-5)data['開盤價(jià)(元)'].expanding(min_periods=1).mean()答案
data[' expanding Open mean']=data['開盤價(jià)(元)'].expanding(min_periods=1).mean()
data[['開盤價(jià)(元)', 'expanding Open mean']].plot(figsize=(16, 6))data['former 30 days rolling Close mean']=data['收盤價(jià)(元)'].rolling(20).mean()
data['upper bound']=data['former 30 days rolling Close mean']+2*data['收盤價(jià)(元)'].rolling(20).std()#在這里我們?nèi)?0天內(nèi)的標(biāo)準(zhǔn)差
data['lower bound']=data['former 30 days rolling Close mean']-2*data['收盤價(jià)(元)'].rolling(20).std()data[['收盤價(jià)(元)', 'former 30 days rolling Close mean','upper bound','lower bound' ]].plot(figsize=(16, 6))第四期：當(dāng)Pandas遇上NumPy
import pandas as pd
import numpy as np
print(np.__version__)
print(pd.__version__)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)
dfprint(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().index[:3]np.argwhere(df['col1'] % 5==0)df['col1'].diff().tolist()df.ix[:, ::-1]df['col1'].take([1,10,15])tem = np.diff(np.sign(np.diff(df['col1'])))
np.where(tem == -2)[0] + 1df[['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'])第五期：一些補(bǔ)充
df = pd.read_csv('數(shù)據(jù)1.csv',encoding='gbk', usecols=['positionName', 'salary'],nrows = 10)答案
df = pd.read_csv('數(shù)據(jù)2.csv',converters={'薪資水平': lambda x: '高' if float(x) > 10000 else '低'} )期望結(jié)果
答案
df.iloc[::20, :][['薪資水平']]df = pd.DataFrame(np.random.random(10)**10, columns=['data'])期望結(jié)果
答案
df.round(3)df.style.format({'data': '{0:.2%}'.format})df['data'].argsort()[::-1][7]df.iloc[::-1, :]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的數(shù)據(jù)
答案
pd.merge(df1, df2, how='left', on=['key1', 'key2'])df = pd.read_csv('數(shù)據(jù)1.csv',encoding='gbk')
pd.set_option('display.max.columns', None)
dfnp.where(df.secondType == df.thirdType)np.argwhere(df['salary'] > df['salary'].mean())[2]df[['salary']].apply(np.sqrt)df['split'] = df['linestaion'].str.split('_')df.shape[1]df[df['industryField'].str.startswith('數(shù)據(jù)')]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進(jìn)階修煉120題全部?jī)?nèi)容，如果能堅(jiān)持走到這里的讀者，我想你已經(jīng)掌握了處理數(shù)據(jù)的常用操作，并且在之后的數(shù)據(jù)分析中碰到相關(guān)問題，希望武裝了Pandas的你能夠從容的解決！
聯(lián)系客服
