SQL查询中的Python数据分析

Question

我即将开始一些Python数据分析，这与我以前做过的任何事情都不一样。我目前正在学习numpy，但到目前为止，它并没有给我提供关于如何做到这一点的洞察力。

我使用python2.7.14 Anaconda和cx_Oracle查询复杂记录。

每个记录都是一个独特的个人，其中包含员工ID、Relationship Tuples (关系类型代码与部门编号配对，可能包含多个)、帐户标志(国旗字符串，可能包含多个)。(3栏共计)

因此，一项记录可能是：

 [(123456), (135:2345678, 212:4354670, 198:9876545), (Flag1, Flag2, Flag3)]

我需要开发一个python脚本，它将记录这些记录并创建各种计数。

示例记录将被计算为至少9个不同的计数。

多少有关系: 135

有多少人有关系: 212

有多少人有关系: 198

部门中有多少人: 2345678

部门中有多少人: 4354670

部门中有多少人: 9876545

有多少个有国旗: Flag1

有多少个有国旗: Flag2

有多少个有国旗: Flag3

另一个棘手的部分是，我不能预先定义关系代码、部门或标志，我的计数必须由从查询中检索到的数据来确定。

一旦我明白了如何做到这一点，希望下一步也得到多少关系X有国旗y，等等，将是直观的。

我知道这是一个很大的问题，但如果有人能为我指明正确的方向，这样我就可以研究或尝试一些教程，这将是非常有帮助的。谢谢!

Answer 1

如果您愿意考虑其他包，请查看构建在numpy之上的熊猫。您可以将sql语句直接读取到dataframe中，然后进行筛选。

例如,

import pandas
sql = '''SELECT * FROM <table> WHERE <condition>'''
df = pandas.read_sql(sql, <connection>)

# Your output might look like the following:

        0                                         1                     2
0   12346   (135:2345678, 212:4354670, 198:9876545) (Flag1, Flag2, Flag3)
1   12345   (136:2343678, 212:4354670, 198:9876545) (Flag1, Flag2, Flag4)

# Format your records into rows
# This part will take some work, and really depends on how your data is formatted
# Do you have repeated values? Are the records always the same size?

# Select only the rows where relationship = 125
rel_125 = df[df['Relationship'] = 125]

熊猫的格式比问答更有深度，但这里有一些很好的资源：10分钟到潘达斯。

您也可以直接过滤行，尽管它可能不是最有效的。例如，下面的查询只选择关系以'212‘开头的行。

df[df['Relationship'].apply(lambda x: any(y.startswith('212') for y in x))]

Answer 2

至少您需要构造这个数据才能进行一个很好的分析，您可以在数据库引擎或python中这样做(我会这样做，就像SNygard建议的那样)。

首先，我创建一些假数据(它是由您提供的)：

import pandas as pd 
import numpy as np
from ast import literal_eval

data = [[12346, '(135:2345678, 212:4354670, 198:9876545)', '(Flag1, Flag2, Flag3)'],
[12345, '(136:2343678, 212:4354670, 198:9876541, 199:9876535)', '(Flag1, Flag4)']]

df = pd.DataFrame(data,columns=['id','relationships','flags'])
df = df.set_index('id')
df

这将返回如下所示的数据格式：数据帧

为了总结或按列计数，我们需要改进我们的数据结构，在某种程度上我们可以使用部门、关系或标志的操作来应用组。

我们将把关系和标志列从字符串类型转换为字符串的python列表。因此，标志列将是标志的python列表，关系列将是关系的python列表。

df['relationships'] = df['relationships'].str.replace('\(','').str.replace('\)','')
df['relationships'] = df['relationships'].str.split(',')

df['flags'] = df['flags'].str.replace('\(','').str.replace('\)','')
df['flags'] = df['flags'].str.split(',')
df

结果是：1

通过将relationships列转换为list，我们可以创建一个新的数据格式，其列数量与列表中的关系相同。

rel = pd.DataFrame(df['relationships'].values.tolist(), index=rel.index)

之后，我们需要对保留索引的列进行堆栈，因此我们将使用multi_index: id和关系列号(0、1、2、3)。

relations = rel.stack()
relations.index.names = ['id','relation_number']
relations

我们得到：2

目前，我们的所有关系都在行中，但仍然不能使用relation_type特性进行分组。因此，我们将我们的关系数据分成两列：relation_type和使用:的department。

clear_relations = relations.str.split(':')
clear_relations = pd.DataFrame(clear_relations.values.tolist(), index=clear_relations.index,columns=['relation_type','department'])
clear_relations

结果是关系

我们的关系是可以分析的，但是我们的旗帜结构仍然是非常无用的。因此，我们将将标志列表转换为列，然后将它们堆叠起来。

flags = pd.DataFrame(df['flags'].values.tolist(), index=rel.index)
flags = flags.stack()
flags.index.names = ['id','flag_number']

结果是标志

哇！，一切都准备好分析了！

例如，每种类型的关系有多少，其中最大的是。

clear_relations.groupby('relation_type').agg('count')['department'].sort_values(ascending=False)

我们得到：类型

所有代码：Github项目