python - 从关联实体结果到二维熊猫数据框的 Python sql 查询

Question

我想从作为关联实体的 sql 表中填充 pandas 数据框，以便数据框具有实体之一的索引和第二个实体的列标题。

例如，我有以下 SQL 表：

实体 1

代码	姓名
一个	A型
乙	B型

实体 2

代码	姓名
W	W型
X	X型
是	Y型
Z	Z型

关联实体

实体 1_code	实体 2_code	价值
一个	W	1
一个	是	7
一个	Z	3
乙	X	88
乙	是	5

我希望我的数据框具有以下结构

	W	X	是	Z
一个	1	钠	7	3
乙	钠	88	5	钠

从语义上讲，我可以通过使用以下伪代码加载一个空帧来做到这一点：

connection = psycopg2.connect( ... )

# create empty df with index set to Entity 1 codes
df = psql.read_sql('SELECT code FROM entity_1', connection, index_col='code')

cur = connection.cursor()
cur.execute('SELECT code FROM entity_2')

# create list of column names
entity_2_codes = [r[0] for r in cur.fetchall()]
# add columns from entity 2 codes
df=df.reindex(columns=entity_2_codes) 

# now loop through each associative entity entry and insert value into dataframe

有没有一种聪明的方法可以更有效地填充表格？一次添加一列或一行？请注意，数据是稀疏的，因此并非每个Entity 1xEntity 2组合都有一个值。

标签： pythonsqlpandasassociative

Answer 1

您可以使用 pandas pivot()或pivot_table()方法。pivot当您不需要聚合时使用（每个组合只有一个值Entity 1_code）Entity 2_code。pivot_table如果您有多个值，可用于进行聚合（总和、计数、最大值），您可以指定如何填充 NA 值等。

如果您可以将Associative entity表加载到 DataFramedf中，那么这将是：

df.pivot(index='Entity 1_code', columns='Entity 2_code', values='value')

或使用pivot_table：

df.pivot_table(index='Entity 1_code', columns='Entity 2_code', values='value', aggfunc='mean')

如果每个组合只有一个值，pivot_table可以pivot通过将 aggfunc 设置为“平均值”来模拟，因为平均值就是那个值。