numpy和pandas矢量运算实现sql的功能

---

    以前在python里面对数据集合进行处理都是用for循环，然后得到每一条记录的具体某个值进行运算，虽然有列表推导式但也是功能有限。这样非常麻烦也不高效，代码一堆，却只做了一句sql就能实现的功能。人生还有很多重要的事，怎么能就耗在这for循环里面啦？
    实际上有numpy和pandas这个神器，numpy目的就是来做大量的维度数组与矩阵运算运算的，pandas 是基于 Numpy 构建的含有更高级数据结构和工具的数据分析包，当然就能实现sql的这些功能。更关键是NumPy是Python中的一个运算速度非常快的一个数学库，它非常重视数组。它允许你在Python中进行向量和矩阵计算，并且由于许多底层函数实际上是用C编写的，因此你可以体验在原生Python中永远无法体验到的速度。
    下面我们就来演示一下在python 里面 如何 用一条语句实现一句sql的效果。数据集存放到json数组里面，对应数据库里面的一个表，这下可以基本不写烦人的for循环啦！

需要的库：

要用到numpy和pandas

pip install numpy
pip install pandas

安装完成后，import一下：

import pandas as pd
import numpy as np

首选准备演示用的数据，这儿有一个库，里面有学生，班级和课程的表。

用一个json结构表示，模拟对应的数据库和表：

# 模拟返回数据相当于数据库,直接读取文件可用：df1 = pd.read_json("F:\\a3\\z\\ja1.json")
def getData():
    return {
        "ja_class" : [{"id": 201801, "name": "2018级1班"},
                        {"id": 201802, "name": "2018级2班"},
                        {"id": 201803, "name": "2018级3班"},
                        {"id": 201804, "name": "2018级4班"}
                    ],
        "ja_student" : [{"id": 1, "name": "赵一", "age": 12, "sex": 1, "class_id":201801},
                      {"id": 2, "name": "钱二", "age": 13, "sex": 0, "class_id":201802},
                      {"id": 3, "name": "孙三", "age": 12, "sex": 0, "class_id":201803},
                      {"id": 4, "name": "李四", "age": 12, "sex": 1, "class_id":201803},
                      {"id": 5, "name": "周五", "age": 11, "sex": 0, "class_id":201801},
                      {"id": 6, "name": "吴六", "age": 12, "sex": 0, "class_id":201802},
                      {"id": 7, "name": "郑七", "age": 13, "sex": 1, "class_id":201803},
                      {"id": 8, "name": "王九", "age": 12, "sex": 0, "class_id":201801},
                      {"id": 9, "name": "上官", "age": 14, "sex": 1, "class_id":201801}
                    ],
        "ja_course": [{"id": 1, "name": "语文"},
                      {"id": 2, "name": "数学"},
                      {"id": 3, "name": "英语"},
                      {"id": 4, "name": "物理"},
                      {"id": 5, "name": "化学"}
                      ]
    }

将json数组导入到pandas的 DataFrame

数据可以直接东json数组读入也可以从文件读入
直接读取文件可用：df1 = pd.read_json("F:\a3\z\ja1.json")

    data = getData()
    #这儿的columns为数据加上列名，如果不填columns参数就会取json对象里面的第一行的key作为列名。一般情况下建议写上。这儿还有一个小技巧，可以用来吧原json里面的key名改为新的列名。
    df_student = pd.DataFrame(data["ja_student"],columns=['id','name', 'age','sex','class_id'])

基本的select操作

这儿有一个注意的地方，对列进行计算，用到了numpy的hstack水平堆叠的方法，然后再从numpy的数组重新生成 pandas的 DataFrame。

#基本的select 操作
def selectDemo():
    data = getData()
    df_student = pd.DataFrame(data["ja_student"],columns=['id','name', 'age','sex','class_id'])

    # print(df_student)

    # select * from student
    print(df_student)

    # select name,age+2 from student, 注意这儿用到了numpy里面的hstack堆叠，再重新转换回来。
    print(pd.DataFrame( np.hstack([df_student[['name']],df_student[['age']]*2]) , columns=['name', 'age*2'] ))

    #选出来要转换成json就用这个.to_json(orient='records')
    print(df_student.to_json(orient='records'))

    # select * from student where age >=13
    print(df_student[df_student['age'] >=13])

    # select * from student order by age desc
    print(df_student.sort_values(by=['age'], ascending=False))

    # select id,name from student where age >=13
    print(df_student[df_student['age'] >= 13][['id','name']])

    # select id,name as studentName from student where age >=13
    print(df_student[df_student['age'] >= 13][['id', 'name']].rename(columns={'name': 'studentName'}))

    # select id,name from student where age >=13 and sex = 1
    print(df_student[ (df_student['age'] >= 13) & (df_student['sex'] == 1) ][['id', 'name']])
    # 还可以用query来实现where的效果,这样更简单，语法和sql一样
    print(df_student.query(" age >=13 and sex ==1 ")[['id', 'name']])

group by操作

grouup by 会吧 by 的列作为 DataFrame 的index，这时候要加一个参数, as_index=False，或者在后面用 , reset_index 也可以吧索引提成一列。

#group by 操作
def groupDemo():
    data = getData()
    df_student = pd.DataFrame(data["ja_student"], columns=['id', 'name', 'age', 'sex','class_id'])

    #默认对选出来的数字类型的列进行计算,结果会吧groupby 的列作为index
    print( df_student[['sex','age','id']].groupby('sex').mean() )

    #也可以先分组后，再进行运算，这时候要指定运算的列名，分组后group by 的字段会变成index，因此转换成json的时候需要指定.to_json(orient='index')
    print( df_student.groupby('sex')[['age','id']].mean().to_json(orient='index') )

    # 可以指定as_index=False 不吧groupby的列作为index,这样转换成json就更方便了
    print(df_student.groupby('sex', as_index=False)[['age', 'id']].max())
    print(df_student.groupby('sex', as_index=False)[['age', 'id']].max().to_json(orient='records'))

    print("------>>")
    # 再来一个 select count(age),max(age),min(age),count(class_id),max(class_id),min(class_id) from student group by sex
    grouped = df_student.groupby('sex')
    #对这'age','class_id' 列进行 'count', 'min', 'max' 的运算。然后吧索引提成列。
    print(grouped[['age','class_id']].agg(['count', 'min', 'max']).reset_index())

    # groupy 多列求不同的值, select avg(age),max(id) from student group by class_id limit 11 ,[注意：]这个是最好的方式！
    print(df_student.groupby('class_id', as_index=False).agg({'age': 'mean', 'id': 'max'}).head(11))

    # grouyby 多级索引的问题。select avg(age),max(age),min(id),max(id) from student group by class_id limit 11
    grouped = df_student.groupby('class_id', as_index=False).agg({'age': ['mean', 'max'], 'id': ['max', 'min']}).head()
    grouped.columns = grouped.columns.get_level_values(0) + '_' + grouped.columns.get_level_values(1)
    print(grouped)

自定义函数

就是类似sql里面的自定义函数然后将字段的内容作为参数，返回新的字段。

#自定函数
def funcDemo():

    def myFun(x,y):
        return x + y

    data = getData()
    df_student = pd.DataFrame(data["ja_student"], columns=['id', 'name', 'age', 'sex'])

    # 实现select name,myFun(id,age) as 'id+age' from student
    v_myFun = np.vectorize(myFun)
    print(pd.DataFrame(
            np.hstack( [df_student[['name']], v_myFun( df_student[['id']], df_student[['age']]) ])
            , columns=['name', 'id+age']))

union all

这个就是用到了numpy 的垂直堆叠 vstack 方法。

#union all
def unionAllDemo():
    data = getData()
    df_student = pd.DataFrame(data["ja_student"], columns=['id', 'name', 'age', 'sex'])

    # 实现 select id,name,age from student where age = 11
    #      UNION ALL
    #      select id,name,age from student where age = 13

    print(pd.DataFrame(
        np.vstack([df_student[df_student['age'] == 11][['id','name','age']], df_student[df_student['age'] == 13][['id','name','age']] ])
        , columns=['id','name','age']))

join

可以实现 inner join，left join 还有笛卡尔积，直接看代码。

# join
def joinDemo():
    data = getData()
    df_student = pd.DataFrame(data["ja_student"], columns=['id', 'name', 'age', 'sex','class_id'])
    df_class = pd.DataFrame(data["ja_class"], columns=['id', 'name'])

    #select * from student inner join class on student.class_id = class.id
    print( pd.merge(df_student, df_class, how='inner', left_on='class_id', right_on='id') )
    #select * from class left join student on class.id = student.class_id
    print( pd.merge( df_class,df_student, how='left', left_on='id', right_on='class_id') )

另外一种直接用index的连接方式，concat

# concat 数据连接 ,可以根据index或者columns 连接, 效果相当于outer join
def concatDemo():
    df1 = pd.DataFrame(np.arange(6).reshape(3, 2),
                       index=['a', 'b', 'c'],
                       columns = ['one', 'two'])

    df2 = pd.DataFrame(5 + np.arange(4).reshape(2, 2),
                       index=['a', 'c'],
                       columns = ['three', 'four'])

    #concat 和merge 的outer 是一样的效果，下面两句结果一样。
    print(df1,'\n',df2,'\n',pd.concat([df2, df1], axis='columns') ,'\n',pd.merge(df2, df1, how='outer', left_index=True, right_index=True) )

神操作来了，数据填充，两个数据集打补丁

这个操作sql没法搞的哟。

# 数据填充
def combineDemo():
    df1 = pd.DataFrame({'a': [1., np.nan, 5., np.nan],
                        'b': [np.nan, 2., np.nan, 6.],
                        'c': range(2, 18, 4)})

    df2 = pd.DataFrame({'a': [5., 4., np.nan, 3., 7.],
                        'b': [np.nan, 3., 4., 6., 8.]})

    print('df1里面的空的位置就用df2的值来填充:','\n',df1.combine_first(df2))

去重复 distinct 操作

# 去重
def uniqueDemo():
    df_student = pd.DataFrame(getData()["ja_student"], columns=['id', 'name', 'age', 'sex', 'class_id'])

    df1,cnt = np.unique(df_student['age'], return_counts=True)
    print(df1) #得到去重后的数组
    print(cnt) #得到去重后每一个值出现的次数

继续神操作，sql里面一直令人头疼的难题，行变列，列边行

#  列变行和行变列
def meltPivotDemo():
    df = pd.DataFrame({'key': ['foo', 'bar', 'baz'],
                        'A': [1, 2, 3],
                        'B': [4, 5, 6],
                        'C': [7, 8, 9]})

    print(df)
    
    print('列变行：','\n',pd.melt(df, ['key']))
    #melt操作自动会吧新的两列加上列名 variable 和 value
    melted = pd.melt(df, ['key'])

    print('行变列：','\n',melted.pivot(index='key', columns='variable', values='value'))

小试牛刀，来一个生成两位数的加减法小学数学练习题

# 生成两位数的加减法数学题
def shuxuetiMake():
    #-99到99的随机整数，生成两列400行
    df = pd.DataFrame(np.random.randint(-99,99,800).reshape(400,2),columns=['x','y'])
    #第一个数大于等于0，并且结果大于等于0的选出来
    df1 = df[ (df['x'] >=0) & (df['x'] + df['y'] >= 0) ]
    #列算式
    def f_MakeSuanshi(x,y):
        return ' '.join([str(x),'+' if y >= 0 else '-',str(abs(y)),'='])

    print('得到结果：','\n',np.vectorize(f_MakeSuanshi)( df1[['x']] , df1[['y']] ) )

再来一个有一点挑战的例子。这个用sql是写不出来的哟。分组抽样

#扑克牌随机抽样的例子
def chouyangDemo():
    card_val = (list(range(1, 11)) + [10] * 3) * 4
    base_names = ['A'] + list(range(2, 11)) + ['J', 'K', 'Q']
    cards = []
    for suit in ['方块', '梅花','红桃', '黑桃']:
        cards.extend( suit + str(num) for num in base_names)

    #将花色单独放到一个列
    def getHuase(x):
        return x[0:2]
    cardDf = pd.DataFrame({'cards': cards,
                          'vals': card_val})

    cardDf1 = pd.DataFrame(np.hstack([cardDf,(np.vectorize(getHuase))(cardDf[['cards']]) ]),columns=['cards', 'vals','huase'])
    #print(pd.DataFrame(np.hstack([df_student[['name']], df_student[['age']] * 2]), columns=['name', 'age*2']))

    #使用sample函数来从整付牌里面抽取5张。
    print(cardDf1.sample(5))

    #按不同花色随机抽n张。
    def f_chouN(df,n):
        return df.sample(n)
    
    print(cardDf1.groupby('huase', as_index=False).apply(f_chouN,2))

计算字段，通过现有列计算得到一个新的列

#计算增加新列
def newColumnDemo():
    df_student = pd.DataFrame(getData()["ja_student"], columns=['id', 'name', 'age', 'sex', 'class_id'])
    df_student[['age1']] = df_student[['age']] + 100

    print(df_student)

#每行遍历执行函数。
def applyDemo():
    df_student = pd.DataFrame(getData()["ja_student"], columns=['id', 'name', 'age', 'sex', 'class_id'])
    #print(df_student)
    def captalizer(x):
        x['age1'] = x['age'] + 100
        return x

    print(df_student.apply(captalizer,axis=1))

多维度的表

#多维度的表：班级》学生》考试成绩，放到一个多维数据集
def muDfDemo():
    data = getData()
    df_student = pd.DataFrame(data["ja_student"], columns=['id', 'name', 'age', 'sex', 'class_id'])
    df_class = pd.DataFrame(data["ja_class"], columns=['id', 'name'])
    df_cores = pd.DataFrame(data["ja_cores"], columns=['id', 'student_id','course_id','cores'])

    #在每一行增加一个列
    def getStudent(cls):
        cls['students'] = df_student[ df_student['class_id'] == cls['id'] ]
        #cls['students'].apply(getCores,axis=1)
        return cls

    #apply相当于循环语句调用函数getStudent
    df1 = df_class.apply(getStudent, axis=1)

    #双重apply
    def getCores(students):
        def getCoresOfOneStudent(student):
            student['cores'] = df_cores[ df_cores['student_id']==student['id'] ]
            #这个函数只能返回对参数的显示的修改。
            return student

        #注意：这儿不能直接返回students，否则得到的没有改动的students，要返回students.apply 。
        return students.apply(getCoresOfOneStudent,axis=1)


    df1['students'] = df1['students'].apply(getCores).to_json(orient='records')

    print(df1.to_json(orient='records'))
    print(df1)

总结一下

使用for循环对每一个具体的数值进行计算，这个属于标量运算；直接对矩阵和数组进行计算是向量运算，这个主要在大数据和机器学习上面要用到。对于日常的基于数据的业务软件，涉及到数组的计算也非常适合使用向量运算。代码少就能提高效率和软件质量。