import pandas as pd
import numpy as np
1.将下面的字典创建为DataFrame
data = {"grammer":["Python","C","Java","GO",np.nan,"SQL","PHP","Python"],
"score":[1,2,np.nan,4,5,6,7,10]}
df = pd.DataFrame(data)
df
2.提取含有字符串"Python"的行
#方法一
df[df['grammer'] == 'Python']
#方法二
results = df['grammer'].str.contains("Python")
results.fillna(value=False,inplace = True)
df[results]
3.输出df的所有列名
print(df.columns)
Index(['grammer', 'score'], dtype='object')
4.修改第二列列名为’popularity’
df.rename(columns={'score':'popularity'}, inplace = True)
df
5.统计grammer列中每种编程语言出现的次数
df['grammer'].value_counts()
6.将空值用上下值的平均值填充
df['popularity'] = df['popularity'].fillna(df['popularity'].interpolate())
df
7.提取popularity列中值大于3的行
df[df['popularity'] > 3]
8.按照grammer列进行去除重复值
df.drop_duplicates(['grammer'])
9.计算popularity列平均值
df['popularity'].mean()
4.75
10.将grammer列转换为list
df['grammer'].to_list()
['Python', 'C', 'Java', 'GO', nan, 'SQL', 'PHP', 'Python']
11.将DataFrame保存为EXCEL
df.to_excel('test.xlsx')
12.查看数据行列数
df.shape
(8, 2)
13.提取popularity列值大于3小于7的行
df[(df['popularity'] > 3) & (df['popularity'] < 7)]
14.交换两列位置
'''
方法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
'''
'\n方法2\ncols = df.columns[[1,0]]\ndf = df[cols]\ndf\n'
15.提取popularity列最大值所在行
df[df['popularity'] == df['popularity'].max()]
16.查看最后5行数据
df.tail()
17.删除最后一行数据
df.drop([len(df)-1],inplace=True)
df
18.添加一行数据[‘Perl’,6.6]
row={'grammer':'Perl','popularity':6.6}
df = df.append(row,ignore_index=True)
df
19.对数据按照"popularity"列值的大小进行排序
df.sort_values("popularity",inplace=True)
df
20.统计grammer列每个字符串的长度
df['grammer'] = df['grammer'].fillna('R')
df['len_str'] = df['grammer'].map(lambda x: len(x))
df
第二期 Pandas数据处理
21.读取本地EXCEL数据
import pandas as pd
df = pd.read_excel('pandas120.xlsx')
22.查看df数据前5行
df.head()
23.将salary列数据转换为最大值与最小值的平均值
#备注,在某些版本pandas中.ix方法可能失效,可使用.iloc,参考https://mp.weixin.qq.com/s/5xJ-VLaHCV9qX2AMNOLRtw
#为什么不能直接使用max,min函数,因为我们的数据中是20k-35k这种字符串,所以需要先用正则表达式提取数字
import re
# 方法一: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)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:5: FutureWarning:
.ix is deprecated. Please use
.loc for label based indexing or
.iloc for positional indexing
See the documentation here:
http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#ix-indexer-is-deprecated
"""
/Users/anaconda/anaconda3/lib/python3.7/site-packages/pandas/core/indexing.py:961: FutureWarning:
.ix is deprecated. Please use
.loc for label based indexing or
.iloc for positional indexing
See the documentation here:
http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#ix-indexer-is-deprecated
return getattr(section, self.name)[new_key]
/Users/anaconda/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:8: FutureWarning:
.ix is deprecated. Please use
.loc for label based indexing or
.iloc for positional indexing
See the documentation here:
http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#ix-indexer-is-deprecated
24.将数据根据学历进行分组并计算平均薪资
print(df.groupby('education').mean())
25.将createTime列时间转换为月-日
#备注,在某些版本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()
/Users/anaconda/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:2: FutureWarning:
.ix is deprecated. Please use
.loc for label based indexing or
.iloc for positional indexing
See the documentation here:
http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#ix-indexer-is-deprecated
26.查看索引、数据类型和内存信息
df.info()
<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
27.查看数值型列的汇总统计
df.describe()
28.新增一列根据salary将数据分为三组
bins = [0,5000, 20000, 50000]
group_names = ['低', '中', '高']
df['categories'] = pd.cut(df['salary'], bins, labels=group_names)
df
29.按照salary列对数据降序排列
df.sort_values('salary', ascending=False)
30.取出第33行数据
df.loc[32]
createTime 03-16
education 硕士
salary 22500
categories 高
Name: 32, dtype: object
31.计算salary列的中位数
np.median(df['salary'])
17500.0
32.绘制薪资水平频率分布直方图
#执行两次
df.salary.plot(kind='hist')
33.绘制薪资水平密度曲线
df.salary.plot(kind='kde',xlim=(0,80000))
34.删除最后一列categories
del df['categories']
# 等价于
df.drop(columns=['categories'], inplace=True)
35.将df的第一列与第二列合并为新的一列
df['test'] = df['education']+df['createTime']
df
36.将education列与salary列合并为新的一列
#备注:salary为int类型,操作与35题有所不同
df["test1"] = df["salary"].map(str) + df['education']
df
37.计算salary最大值与最小值之差
df[['salary']].apply(lambda x: x.max() - x.min())
salary 41500
dtype: int64
38.将第一行与最后一行拼接
pd.concat([df[:1], df[-2:-1]])
39.将第8行数据添加至末尾
df.append(df.iloc[7])
40.查看每列的数据类型
df.dtypes
createTime object
education object
salary int64
test object
test1 object
dtype: object
41.将createTime列设置为索引
df.set_index("createTime")
42.生成一个和df长度相同的随机数dataframe
df1 = pd.DataFrame(pd.Series(np.random.randint(1, 10, 135)))
df1
43.将上一题生成的dataframe与df合并
df= pd.concat([df,df1],axis=1)
df
44.生成新的一列new为salary列减去之前生成随机数列
df["new"] = df["salary"] - df[0]
df
45.检查数据中是否含有任何缺失值
df.isnull().values.any()
False
46.将salary列类型转换为浮点数
df['salary'].astype(np.float64)
47.计算salary大于10000的次数
len(df[df['salary']>10000])
119
48.查看每种学历出现的次数
df.education.value_counts()
本科 119
硕士 7
不限 5
大专 4
Name: education, dtype: int64
49.查看education列共有几种学历
df['education'].nunique()
4
50.提取salary与new列的和大于60000的最后3行
df1 = df[['salary','new']]
rowsums = df1.apply(np.sum, axis=1)
res = df.iloc[np.where(rowsums > 60000)[0][-3:], :]
res
第三期 金融数据处理
51.使用绝对路径读取本地Excel数据
#请将下面的路径替换为你存储数据的路径
data = pd.read_excel('/Users/Desktop/600000.SH.xls')
WARNING *** OLE2 inconsistency: SSCS size is 0 but SSAT size is non-zero
52.查看数据前三行
data.head(3)
53.查看每列数据缺失值情况
data.isnull().sum()
代码 1
简称 2
日期 2
前收盘价(元) 2
开盘价(元) 2
最高价(元) 2
最低价(元) 2
收盘价(元) 2
成交量(股) 2
成交金额(元) 2
涨跌(元) 2
涨跌幅(%) 2
均价(元) 2
换手率(%) 2
A股流通市值(元) 2
总市值(元) 2
A股流通股本(股) 2
市盈率 2
dtype: int64
54.提取日期列含有空值的行
data[data['日期'].isnull()]
55.输出每列缺失值具体行数
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))
列名:"代码", 第[327]行位置有缺失值
列名:"简称", 第[327, 328]行位置有缺失值
列名:"日期", 第[327, 328]行位置有缺失值
列名:"前收盘价(元)", 第[327, 328]行位置有缺失值
列名:"开盘价(元)", 第[327, 328]行位置有缺失值
列名:"最高价(元)", 第[327, 328]行位置有缺失值
列名:"最低价(元)", 第[327, 328]行位置有缺失值
列名:"收盘价(元)", 第[327, 328]行位置有缺失值
列名:"成交量(股)", 第[327, 328]行位置有缺失值
列名:"成交金额(元)", 第[327, 328]行位置有缺失值
列名:"涨跌(元)", 第[327, 328]行位置有缺失值
列名:"涨跌幅(%)", 第[327, 328]行位置有缺失值
列名:"均价(元)", 第[327, 328]行位置有缺失值
列名:"换手率(%)", 第[327, 328]行位置有缺失值
列名:"A股流通市值(元)", 第[327, 328]行位置有缺失值
列名:"总市值(元)", 第[327, 328]行位置有缺失值
列名:"A股流通股本(股)", 第[327, 328]行位置有缺失值
列名:"市盈率", 第[327, 328]行位置有缺失值
56.删除所有存在缺失值的行
'''
备注
axis:0-行操作(默认),1-列操作
how:any-只要有空值就删除(默认),all-全部为空值才删除
inplace:False-返回新的数据集(默认),True-在原数据集上操作
'''
data.dropna(axis=0, how='any', inplace=True)
57.绘制收盘价的折线图
import matplotlib.pyplot as plt
plt.style.use('seaborn-darkgrid') # 设置画图的风格
plt.rc('font', size=6) #设置图中字体和大小
plt.rc('figure', figsize=(4,3), dpi=150) # 设置图的大小
data['收盘价(元)'].plot()
# 等价于
import matplotlib.pyplot as plt
plt.plot(df['收盘价(元)'])
58.同时绘制开盘价与收盘价
data[['收盘价(元)','开盘价(元)']].plot()
<matplotlib.axes._subplots.AxesSubplot at 0x12b997410>
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:211: RuntimeWarning: Glyph 25910 missing from current font.
font.set_text(s, 0.0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:211: RuntimeWarning: Glyph 30424 missing from current font.
font.set_text(s, 0.0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:211: RuntimeWarning: Glyph 20215 missing from current font.
font.set_text(s, 0.0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:211: RuntimeWarning: Glyph 20803 missing from current font.
font.set_text(s, 0.0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:211: RuntimeWarning: Glyph 24320 missing from current font.
font.set_text(s, 0.0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:180: RuntimeWarning: Glyph 25910 missing from current font.
font.set_text(s, 0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:180: RuntimeWarning: Glyph 30424 missing from current font.
font.set_text(s, 0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:180: RuntimeWarning: Glyph 20215 missing from current font.
font.set_text(s, 0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:180: RuntimeWarning: Glyph 20803 missing from current font.
font.set_text(s, 0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:180: RuntimeWarning: Glyph 24320 missing from current font.
font.set_text(s, 0, flags=flags)
59.绘制涨跌幅的直方图
plt.hist(df['涨跌幅(%)'])
# 等价于
df['涨跌幅(%)'].hist()
60.让直方图更细致
data['涨跌幅(%)'].hist(bins = 30)
61.以data的列名创建一个dataframe
temp = pd.DataFrame(columns = data.columns.to_list())
62.打印所有换手率不是数字的行
for i in range(len(data)):
if type(data.iloc[i,13]) != float:
temp = temp.append(data.loc[i])
temp
63.打印所有换手率为–的行
data[data['换手率(%)'].isin(['--'])]
64.重置data的行号
data = data.reset_index()
65.删除所有换手率为非数字的行
k =[]
for i in range(len(data)):
if type(data.iloc[i,13]) != float:
k.append(i)
data.drop(labels=k,inplace=True)
66.绘制换手率的密度曲线
data['换手率(%)'].plot(kind='kde')
67.计算前一天与后一天收盘价的差值
data['收盘价(元)'].diff()
68.计算前一天与后一天收盘价变化率
data['收盘价(元)'].pct_change()
69.设置日期为索引
data = data.set_index('日期')
70.以5个数据作为一个数据滑动窗口,在这个5个数据上取均值(收盘价)
data['收盘价(元)'].rolling(5).mean()
71.以5个数据作为一个数据滑动窗口,计算这五个数据总和(收盘价)
data['收盘价(元)'].rolling(5).sum()
0 NaN
1 NaN
2 NaN
3 NaN
4 78.4789
...
322 75.7100
323 75.6400
324 75.3500
325 75.0000
326 74.6000
Name: 收盘价(元), Length: 309, dtype: float64
72.将收盘价5日均线、20日均线与原始数据绘制在同一个图上
data['收盘价(元)'].plot()
data['收盘价(元)'].rolling(5).mean().plot()
data['收盘价(元)'].rolling(20).mean().plot()
73.按周为采样规则,取一周收盘价最大值
data['收盘价(元)'].resample('W').max()
日期
2016-01-10 15.9855
2016-01-17 15.8265
2016-01-24 15.6940
2016-01-31 15.0405
2016-02-07 16.2328
...
2017-04-16 15.9700
2017-04-23 15.5600
2017-04-30 15.2100
2017-05-07 15.1600
2017-05-14 14.8600
Freq: W-SUN, Name: 收盘价(元), Length: 71, dtype: float64
74.绘制重采样数据与原始数据
data['收盘价(元)'].plot()
data['收盘价(元)'].resample('7D').max().plot()
<matplotlib.axes._subplots.AxesSubplot at 0x12c56c6d0>
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:211: RuntimeWarning: Glyph 26085 missing from current font.
font.set_text(s, 0.0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:211: RuntimeWarning: Glyph 26399 missing from current font.
font.set_text(s, 0.0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:180: RuntimeWarning: Glyph 26085 missing from current font.
font.set_text(s, 0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:180: RuntimeWarning: Glyph 26399 missing from current font.
font.set_text(s, 0, flags=flags)
75.将数据往后移动5天
data.shift(5)
76.将数据向前移动5天
data.shift(-5)
77.使用expending函数计算开盘价的移动窗口均值
data['开盘价(元)'].expanding(min_periods=1).mean()
日期
2016-01-04 16.144400
2016-01-05 15.804400
2016-01-06 15.805867
2016-01-07 15.784525
2016-01-08 15.761120
...
2017-05-03 16.041489
2017-05-04 16.038314
2017-05-05 16.034769
2017-05-08 16.030695
2017-05-09 16.026356
Name: 开盘价(元), Length: 309, dtype: float64
78.绘制上一题的移动均值与原始数据折线图
data['expanding Open mean']=data['开盘价(元)'].expanding(min_periods=1).mean()
data[['开盘价(元)', 'expanding Open mean']].plot(figsize=(16, 6))
<matplotlib.axes._subplots.AxesSubplot at 0x12c789dd0>
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:211: RuntimeWarning: Glyph 26085 missing from current font.
font.set_text(s, 0.0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:211: RuntimeWarning: Glyph 26399 missing from current font.
font.set_text(s, 0.0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:180: RuntimeWarning: Glyph 26085 missing from current font.
font.set_text(s, 0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:180: RuntimeWarning: Glyph 26399 missing from current font.
font.set_text(s, 0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:211: RuntimeWarning: Glyph 24320 missing from current font.
font.set_text(s, 0.0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:211: RuntimeWarning: Glyph 30424 missing from current font.
font.set_text(s, 0.0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:211: RuntimeWarning: Glyph 20215 missing from current font.
font.set_text(s, 0.0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:211: RuntimeWarning: Glyph 20803 missing from current font.
font.set_text(s, 0.0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:180: RuntimeWarning: Glyph 24320 missing from current font.
font.set_text(s, 0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:180: RuntimeWarning: Glyph 30424 missing from current font.
font.set_text(s, 0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:180: RuntimeWarning: Glyph 20215 missing from current font.
font.set_text(s, 0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:180: RuntimeWarning: Glyph 20803 missing from current font.
font.set_text(s, 0, flags=flags)
79.计算布林指标
data['former 30 days rolling Close mean']=data['收盘价(元)'].rolling(20).mean()
data['upper bound']=data['former 30 days rolling Close mean']+2*data['收盘价(元)'].rolling(20).std()#在这里我们取20天内的标准差
data['lower bound']=data['former 30 days rolling Close mean']-2*data['收盘价(元)'].rolling(20).std()
80.计算布林线并绘制
data[['收盘价(元)', 'former 30 days rolling Close mean','upper bound','lower bound' ]].plot(figsize=(16, 6))
<matplotlib.axes._subplots.AxesSubplot at 0x12c7e90d0>
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:211: RuntimeWarning: Glyph 26085 missing from current font.
font.set_text(s, 0.0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:211: RuntimeWarning: Glyph 26399 missing from current font.
font.set_text(s, 0.0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:180: RuntimeWarning: Glyph 26085 missing from current font.
font.set_text(s, 0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:180: RuntimeWarning: Glyph 26399 missing from current font.
font.set_text(s, 0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:211: RuntimeWarning: Glyph 25910 missing from current font.
font.set_text(s, 0.0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:211: RuntimeWarning: Glyph 30424 missing from current font.
font.set_text(s, 0.0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:211: RuntimeWarning: Glyph 20215 missing from current font.
font.set_text(s, 0.0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:211: RuntimeWarning: Glyph 20803 missing from current font.
font.set_text(s, 0.0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:180: RuntimeWarning: Glyph 25910 missing from current font.
font.set_text(s, 0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:180: RuntimeWarning: Glyph 30424 missing from current font.
font.set_text(s, 0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:180: RuntimeWarning: Glyph 20215 missing from current font.
font.set_text(s, 0, flags=flags)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/matplotlib/backends/backend_agg.py:180: RuntimeWarning: Glyph 20803 missing from current font.
font.set_text(s, 0, flags=flags)
第四期 当Pandas遇上NumPy
81.导入并查看pandas与numpy版本
import pandas as pd
import numpy as np
print(np.__version__)
print(pd.__version__)
1.17.2
0.25.3
82.从NumPy数组创建DataFrame
#备注 使用numpy生成20个0-100随机数
tem = np.random.randint(1,100,20)
df1 = pd.DataFrame(tem)
df1
83.从NumPy数组创建DataFrame
#备注 使用numpy生成20个0-100固定步长的数
tem = np.arange(0,100,5)
df2 = pd.DataFrame(tem)
df2
84.从NumPy数组创建DataFrame
#备注 使用numpy生成20个指定分布(如标准正态分布)的数
tem = np.random.normal(0, 1, 20)
df3 = pd.DataFrame(tem)
df3
85.将df1,df2,df3按照行合并为新DataFrame
df = pd.concat([df1,df2,df3],axis=0,ignore_index=True)
df
86.将df1,df2,df3按照列合并为新DataFrame
df = pd.concat([df1,df2,df3],axis=1,ignore_index=True)
df
87.查看df所有数据的最小值、25%分位数、中位数、75%分位数、最大值
print(np.percentile(df, q=[0, 25, 50, 75, 100]))
[-1.34817283 1.41754194 23.5 50. 95. ]
88.修改列名为col1,col2,col3
df.columns = ['col1','col2','col3']
89.提取第一列中不在第二列出现的数字
df['col1'][~df['col1'].isin(df['col2'])]
90.提取第一列和第二列出现频率最高的三个数字
temp = df['col1'].append(df['col2'])
temp.value_counts().index[:3]
Int64Index([65, 55, 50], dtype='int64')
91.提取第一列中可以整除5的数字位置
np.argwhere(df['col1'] % 5==0)
/Users/anaconda/anaconda3/lib/python3.7/site-packages/numpy/core/fromnumeric.py:61: FutureWarning: Series.nonzero() is deprecated and will be removed in a future version.Use Series.to_numpy().nonzero() instead
return bound(*args, **kwds)
array([[ 2],
[ 6],
[ 7],
[10],
[14]])
92.计算第一列数字前一个与后一个的差值
df['col1'].diff().tolist()
[nan,
-38.0,
24.0,
-21.0,
-38.0,
32.0,
12.0,
-15.0,
48.0,
-40.0,
12.0,
-41.0,
44.0,
-45.0,
32.0,
-18.0,
-10.0,
5.0,
26.0,
-6.0]
93.将col1,col2,clo3三列顺序颠倒
df.ix[:, ::-1]
/Users/anaconda/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:1: FutureWarning:
.ix is deprecated. Please use
.loc for label based indexing or
.iloc for positional indexing
See the documentation here:
http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#ix-indexer-is-deprecated
"""Entry point for launching an IPython kernel.
/Users/anaconda/anaconda3/lib/python3.7/site-packages/pandas/core/indexing.py:822: FutureWarning:
.ix is deprecated. Please use
.loc for label based indexing or
.iloc for positional indexing
See the documentation here:
http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#ix-indexer-is-deprecated
retval = getattr(retval, self.name)._getitem_axis(key, axis=i)
94.提取第一列位置在1,10,15的数字
df['col1'].take([1,10,15])
# 等价于
df.iloc[[1,10,15],0]
1 41
10 55
15 27
Name: col1, dtype: int64
95.查找第一列的局部最大值位置
#备注 即比它前一个与后一个数字的都大的数字
tem = np.diff(np.sign(np.diff(df['col1'])))
np.where(tem == -2)[0] + 1
array([ 2, 6, 8, 10, 12, 14, 18])
96.按行计算df的每一行均值
df[['col1','col2','col3']].mean(axis=1)
0 26.807709
1 15.852683
2 25.049486
3 20.145295
4 9.262599
5 21.271703
6 26.230921
7 22.883942
8 41.345904
9 30.088320
10 34.774255
11 23.256581
12 39.485689
13 25.635775
14 38.931544
15 34.466929
16 32.293192
17 35.257769
18 45.725352
19 45.789433
dtype: float64
97.对第二列计算移动平均值
#备注 每次移动三个位置,不可以使用自定义函数
np.convolve(df['col2'], np.ones(3)/3, mode='valid')
array([ 5., 10., 15., 20., 25., 30., 35., 40., 45., 50., 55., 60., 65.,
70., 75., 80., 85., 90.])
98.将数据按照第三列值的大小升序排列
df.sort_values("col3",inplace=True)
99.将第一列大于50的数字修改为’高’
df.col1[df['col1'] > 50]= '高'
/Users/anaconda/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning:
A value is trying to be set on a copy of a slice from a DataFrame
See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
"""Entry point for launching an IPython kernel.
100.计算第二列与第三列之间的欧式距离
np.linalg.norm(df['col2']-df['col3'])
247.80470977803168
第五期 一些补充
101.从CSV文件中读取指定数据
#备注 从数据1中的前10行中读取positionName, salary两列
df = pd.read_csv('数据1.csv',encoding='gbk', usecols=['positionName', 'salary'],nrows = 10)
df
102.从CSV文件中读取指定数据
#备注 从数据2中读取数据并在读取数据时将薪资大于10000的为改为高
df = pd.read_csv('数据2.csv',converters={'薪资水平': lambda x: '高' if float(x) > 10000 else '低'} )
df
103.从上一题数据中,对薪资水平列每隔20行进行一次抽样
df.iloc[::20, :][['薪资水平']]
104.将数据取消使用科学计数法
#输入
df = pd.DataFrame(np.random.random(10)**10, columns=['data'])
df
df.round(3)
105.将上一题的数据转换为百分数
df.style.format({'data': '{0:.2%}'.format})
106.查找上一题数据中第3大值的行号
df['data'].argsort()[::-1][7]
5
107.反转df的行
df.iloc[::-1, :]
108.按照多列对数据进行合并
#输入
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'])
109.按照多列对数据进行合并
pd.merge(df1, df2, how='left', on=['key1', 'key2'])
110.再次读取数据1并显示所有的列
df = pd.read_csv('数据1.csv',encoding='gbk')
pd.set_option("display.max.columns", None)
df
111.查找secondType与thirdType值相等的行号
np.where(df.secondType == df.thirdType)
(array([ 0, 2, 4, 5, 6, 10, 14, 23, 25, 27, 28, 29, 30,
33, 37, 38, 39, 40, 41, 48, 49, 52, 53, 55, 57, 61,
65, 66, 67, 71, 73, 74, 75, 79, 80, 82, 85, 88, 89,
91, 96, 100]),)
112.查找薪资大于平均薪资的第三个数据
np.argwhere(df['salary'] > df['salary'].mean())[2]
array([5])
113.将上一题数据的salary列开根号
df[['salary']].apply(np.sqrt)
114.将上一题数据的linestaion列按_拆分
df['split'] = df['linestaion'].str.split('_')
115.查看上一题数据中一共有多少列
df.shape[1]
54
116.提取industryField列以’数据’开头的行
df[df['industryField'].str.startswith('数据')]
117.按列制作数据透视表
pd.pivot_table(df,values=["salary","score"],index="positionId")
118.同时对salary、score两列进行计算
df[["salary","score"]].agg([np.sum,np.mean,np.min])
119.对salary求平均,对score列求和
df.agg({"salary":np.sum,"score":np.mean})
salary 3.331000e+06
score 1.271429e+01
dtype: float64
120.计算并提取平均薪资最高的区
df[['district','salary']].groupby(by='district').mean().sort_values('salary',ascending=False).head(1)