In [52]:
import pandas as pd
import numpy as np
df=pd.DataFrame({"id":[1001,1002,1003,1004,1005,1006],
"date":pd.date_range('20130102', periods=6),
"city":['Beijing ', 'SH', ' guangzhou ', 'Shenzhen', 'shanghai', 'BEIJING '],
"age":[23,44,54,32,34,32],
"category":['100-A','100-B','110-A','110-C','210-A','130-F'],
"price":[1200,np.nan,2133,5433,np.nan,4432]},columns =['id','date','city','category','age','price'])
df
Out[52]:
id date city category age price
0 1001 2013-01-02 Beijing 100-A 23 1200.0
1 1002 2013-01-03 SH 100-B 44 NaN
2 1003 2013-01-04 guangzhou 110-A 54 2133.0
3 1004 2013-01-05 Shenzhen 110-C 32 5433.0
4 1005 2013-01-06 shanghai 210-A 34 NaN
5 1006 2013-01-07 BEIJING 130-F 32 4432.0
In [53]:
df1=pd.DataFrame({"id":[1001,1002,1003,1004,1005,1006,1007,1008],
"gender":['male','female','male','female','male','female','male','female'],
"pay":['Y','N','Y','Y','N','Y','N','Y',],
"m-point":[10,12,20,40,40,40,30,20]})
df1
Out[53]:
id gender pay m-point
0 1001 male Y 10
1 1002 female N 12
2 1003 male Y 20
3 1004 female Y 40
4 1005 male N 40
5 1006 female Y 40
6 1007 male N 30
7 1008 female Y 20
In [54]:
df_inner=pd.merge(left=df,right=df1,how='inner',on='id')
df_inner
Out[54]:
id date city category age price gender pay m-point
0 1001 2013-01-02 Beijing 100-A 23 1200.0 male Y 10
1 1002 2013-01-03 SH 100-B 44 NaN female N 12
2 1003 2013-01-04 guangzhou 110-A 54 2133.0 male Y 20
3 1004 2013-01-05 Shenzhen 110-C 32 5433.0 female Y 40
4 1005 2013-01-06 shanghai 210-A 34 NaN male N 40
5 1006 2013-01-07 BEIJING 130-F 32 4432.0 female Y 40
In [55]:
# 按索引提取索引号为3的单行数据，提取了index=3的行数据
df_inner.loc[3]
Out[55]:
id                         1004
date        2013-01-05 00:00:00
city                   Shenzhen
category                  110-C
age                          32
price                      5433
gender                   female
pay                           Y
m-point                      40
Name: 3, dtype: object
In [56]:
# 使用冒号可以限定提取行数据的范围，是dataframe数据类型，这里的序号就代表index的行号
df_inner.loc[0:5]
Out[56]:
id date city category age price gender pay m-point
0 1001 2013-01-02 Beijing 100-A 23 1200.0 male Y 10
1 1002 2013-01-03 SH 100-B 44 NaN female N 12
2 1003 2013-01-04 guangzhou 110-A 54 2133.0 male Y 20
3 1004 2013-01-05 Shenzhen 110-C 32 5433.0 female Y 40
4 1005 2013-01-06 shanghai 210-A 34 NaN male N 40
5 1006 2013-01-07 BEIJING 130-F 32 4432.0 female Y 40
In [16]:
# 使用不规则index测试下
dfs=df_inner.set_index(keys='age')
dfs
Out[16]:
id date city category price gender pay m-point
age
23 1001 2013-01-02 Beijing 100-A 1200.0 male Y 10
44 1002 2013-01-03 SH 100-B NaN female N 12
54 1003 2013-01-04 guangzhou 110-A 2133.0 male Y 20
32 1004 2013-01-05 Shenzhen 110-C 5433.0 female Y 40
34 1005 2013-01-06 shanghai 210-A NaN male N 40
32 1006 2013-01-07 BEIJING 130-F 4432.0 female Y 40
In [22]:
dfs.loc[23:54]
Out[22]:
id date city category price gender pay m-point
age
23 1001 2013-01-02 Beijing 100-A 1200.0 male Y 10
44 1002 2013-01-03 SH 100-B NaN female N 12
54 1003 2013-01-04 guangzhou 110-A 2133.0 male Y 20
In [62]:
# 使用日期作为索引
df_inner.set_index('date',inplace=True)
df_inner
Out[62]:
id city category age price gender pay m-point
date
2013-01-02 1001 Beijing 100-A 23 1200.0 male Y 10
2013-01-03 1002 SH 100-B 44 NaN female N 12
2013-01-04 1003 guangzhou 110-A 54 2133.0 male Y 20
2013-01-05 1004 Shenzhen 110-C 32 5433.0 female Y 40
2013-01-06 1005 shanghai 210-A 34 NaN male N 40
2013-01-07 1006 BEIJING 130-F 32 4432.0 female Y 40
In [63]:
# 提取4号之前的所有数据
df_inner.loc[:'2013-01-04']
Out[63]:
id city category age price gender pay m-point
date
2013-01-02 1001 Beijing 100-A 23 1200.0 male Y 10
2013-01-03 1002 SH 100-B 44 NaN female N 12
2013-01-04 1003 guangzhou 110-A 54 2133.0 male Y 20
In [64]:
# df.loc是按标签提取,df.iloc是按位置提取
# 使用iloc按位置区域提取数据，iloc可以使用-1这种
df_inner.iloc[:3,:2]
Out[64]:
id city
date
2013-01-02 1001 Beijing
2013-01-03 1002 SH
2013-01-04 1003 guangzhou
In [65]:
df_inner
Out[65]:
id city category age price gender pay m-point
date
2013-01-02 1001 Beijing 100-A 23 1200.0 male Y 10
2013-01-03 1002 SH 100-B 44 NaN female N 12
2013-01-04 1003 guangzhou 110-A 54 2133.0 male Y 20
2013-01-05 1004 Shenzhen 110-C 32 5433.0 female Y 40
2013-01-06 1005 shanghai 210-A 34 NaN male N 40
2013-01-07 1006 BEIJING 130-F 32 4432.0 female Y 40
In [66]:
# iloc按位置区域提取数据
df_inner.iloc[0:3,0:4]
Out[66]:
id city category age
date
2013-01-02 1001 Beijing 100-A 23
2013-01-03 1002 SH 100-B 44
2013-01-04 1003 guangzhou 110-A 54
In [67]:
# iloc按位置单独提取数据
df_inner.iloc[[0,2,5],[0,4,5]]
Out[67]:
id price gender
date
2013-01-02 1001 1200.0 male
2013-01-04 1003 2133.0 male
2013-01-07 1006 4432.0 female
In [61]:
# 按标签和位置组合提取 ix
# ix是loc和iloc的混合，既能按索引标签提取，也能按位置进行数据提取
In [69]:
# 使用ix按索引标签和位置混合提取数据，不推荐使用了
df_inner.ix[:'2020-01-03',:4]

D:\software\anaconda\lib\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
Out[69]:
id city category age
date
2013-01-02 1001 Beijing 100-A 23
2013-01-03 1002 SH 100-B 44
2013-01-04 1003 guangzhou 110-A 54
2013-01-05 1004 Shenzhen 110-C 32
2013-01-06 1005 shanghai 210-A 34
2013-01-07 1006 BEIJING 130-F 32
In [80]:
# 有个空格
df_inner.loc['2013-01-02'].city
Out[80]:
'Beijing '
In [73]:
# 按条件提取
# 使用isin函数对city中的值是否为beijing进行判断
df_inner['city'].isin(['SH'])
Out[73]:
date
2013-01-02    False
2013-01-03     True
2013-01-04    False
2013-01-05    False
2013-01-06    False
2013-01-07    False
Name: city, dtype: bool
In [82]:
# 将isin函数嵌套到loc的数据提取函数中，将判断结果为Ture数据提取出来
df_inner.loc[df_inner['city'].isin(['SH','Shenzhen'])]
Out[82]:
id city category age price gender pay m-point
date
2013-01-03 1002 SH 100-B 44 NaN female N 12
2013-01-05 1004 Shenzhen 110-C 32 5433.0 female Y 40
In [83]:
# 数值提取还可以完成类似数据分列的工作，从合并的数值中提取出制定的数值
pd.DataFrame(df_inner['category'].str[:3])
Out[83]:
category
date
2013-01-02 100
2013-01-03 100
2013-01-04 110
2013-01-05 110
2013-01-06 210
2013-01-07 130