我有一个pivot table dataframe,它有列,其中包含日期范围和位置的原因代码计数。在
理想情况下,我希望在每个位置下插入一行,其中包含该特定原因代码表示的该位置总计数的%age。在
因此,如果第MS列=6,且该行的总数为52,则直接下一行的该列将显示11.5%。在
如果这更有意义的话,我也可以把它作为第二个专栏。
这是我当前使用的代码
issue_query = """
select distinct int(obhssq), (obwhid), obrtrc, SUBSTRING(int(obivdt), 3, 2) as inv_month, ((SUBSTRING(int(obivdt), 3, 2) - 1) / 3 + 1) as inv_quarter,
int(obivdt)
from hsdet where obrtrc != '' and obrtrc != 'TX' and obivdt > 170000 and obivdt < 990000 and obwhid in ('01', '03', '05', '06', '07', '08', '09', '11', '12')
"""
cursor.execute(issue_query)
total_issues = 0
hedrows = cursor.fetchall()
for row in hedrows :
total_issues = total_issues + 1
issue_df = pd.read_sql(issue_query, cnxn)
issue_df.rename(columns={'00001' : 'Invoices', 'OBWHID' : 'Warehouse', 'OBRTRC':'Reason', 'INV_MONTH':'Month', 'INV_QUARTER':'Quarter', '00006':'Date'}, inplace=True)
pivoted = pd.pivot_table(issue_df, index=["Warehouse", "Quarter"], values=["Invoices"], columns=['Reason'], aggfunc='count', fill_value=0)
pivoted['Total']= pivoted.sum(axis=1)
pivoted.loc['Total'] = pivoted.sum()
print(pivoted)
以下是我当前的输出:
^{pr2}$我想插入如下行:
Invoices \
Reason CE CS DG DR IC IO IP IW LC LD NC NO PB QC QW SC WH TTL
(01, 1) 9 4 4 0 1 8 7 5 0 0 17 5 2 2 2 2 0 68
%age 13% 6% 6% 0% 1% 12% 10% 7% 0% 0% 25% 7% 3% 3% 3% 3% 0% 23%
谢谢!在
不确定这是最优雅的解决方案,但它很管用:
输入:
代码如下:
^{pr2}$输出:
相关问题 更多 >
编程相关推荐