Pivot并使用Python从数据集创建运行总数

Question

我有一个数据集df，我想把它的行枢轴到列标题，并创建一个运行总数:

cons_power  cons_count  id  date    r_power r_count d_power d_count
500         20          aa  q122    50      2       20      1
0           0           aa  q222    20      1       0       0
1000        80          bb  q122    0       0       50      3
0           0           bb  q222    100     5       0       0

的

id  q122_power  q122_count  q222_power  q222_count
aa  530         21          550         22
bb  950         77          1050        82

我们开始为id 'aa'的con_power值为500，为q122添加r_power值为50，并减去d_power值为20，得到:530值

然后，我们将530的值作为q222过程的下一个基线值，即添加r_power并减去d_power = 550

计数也使用相同的概念。(开始为'aa' q122计数20，加2，减1)给我们:21值

然后，我们将值21作为q222过程的下一个基线值，即添加r_count并减去d_count = 22

df['q122_power'] = df['r_power'].add(df.groupby('id', 'date') 
['r_power'].cumsum()).sub(df.groupby('id', 'date')['d_power'].cumsum())
df['q222_power'] = df['r_power'].add(df.groupby('id', 'date') 
['r_power'].cumsum()).sub(df.groupby('id', 'date')['d_power'].cumsum())
df['q122_count'] = df['r_count'].add(df.groupby('id', 'date') 
['r_count'].cumsum()).sub(df.groupby('id', 'date')['d_count'].cumsum())
df['q222_count'] = df['r_count'].add(df.groupby('id', 'date') 
['r_count'].cumsum()).sub(df.groupby('id', 'date')['d_count'].cumsum())

试图找到一种更优雅的方式来产生期望的输出。我已经能够解决分组和计算，但不确定如何在脚本中合并枢轴步骤。如有任何建议，欢迎。

Answer 1

在groupby之前计算power和count，然后pivot您的数据框架。最后扁平化列多索引并重置行索引。

out = df[['id', 'date']].join(
df.assign(power=df['cons_power'] + df['r_power'] - df['d_power'],
count=df['cons_count'] + df['r_count'] - df['d_count'])
.groupby('id')[['power', 'count']].cumsum()
).pivot('id', 'date', ['power', 'count'])
out.columns = out.columns.to_flat_index().map('_'.join)

输出

>>> out.reset_index()
id  power_q122  power_q222  count_q122  count_q222
0  aa         530         550          21          22
1  bb         950        1050          77          82

Answer 2

我们还可以创建一个MultiIndex，以允许使用顶级索引组cons,r和d进行计算:

# Columns Not To Split into MultiIndex
df = df.set_index(['id', 'date'])
# Split into MultiIndex
df.columns = df.columns.str.split('_', expand=True)
df = (
# Do Computations on the 2D top-level indexes
(df['cons'] + df['r'] - df['d'])
.groupby(level='id').cumsum()  # Rolling Total within IDs
.unstack(level='date')  # Convert date index level into Column Level
)
# Collapse MultiIndex
df.columns = df.columns.map('_'.join)
# Restore ID column
df = df.reset_index()

df:

id  power_q122  power_q222  count_q122  count_q222
0  aa         530         550          21          22
1  bb         950        1050          77          82

---

工作原理:

set_index，然后创建MultiIndex与str.split:

# Columns Not To Split into MultiIndex
df = df.set_index(['id', 'date'])
# Split into MultiIndex
df.columns = df.columns.str.split('_', expand=True)

cons           r           d      
power count power count power count
id date                                    
aa q122   500    20    50     2    20     1
q222     0     0    20     1     0     0
bb q122  1000    80     0     0    50     3
q222     0     0   100     5     0     0

我们现在可以访问顶级索引并进行计算(索引对齐将确保计算在较低级别索引power和count中进行):

df['cons'] + df['r'] - df['d']
power  count
id date              
aa q122    530     21
q222     20      1
bb q122    950     77
q222    100      5

然后取groupby cumsum以获得每个id中的滚动总数:

(df['cons'] + df['r'] - df['d']).groupby(level='id').cumsum()
power  count
id date              
aa q122    530     21
q222    550     22
bb q122    950     77
q222   1050     82

unstack的日期列，将索引级别转换为列级别:

(df['cons'] + df['r'] - df['d']).groupby(level='id').cumsum().unstack(level='date') 
power       count     
date  q122  q222  q122 q222
id                         
aa     530   550    21   22
bb     950  1050    77   82

现在剩下的就是用Index.map:

清理MultiIndex

df.columns = df.columns.map('_'.join)
power_q122  power_q222  count_q122  count_q222
id                                                
aa         530         550          21          22
bb         950        1050          77          82

和reset_index恢复默认范围索引:

df = df.reset_index()
id  power_q122  power_q222  count_q122  count_q222
0  aa         530         550          21          22
1  bb         950        1050          77          82

---

Setup and imports:

import pandas as pd
df = pd.DataFrame({
'cons_power': [500, 0, 1000, 0], 'cons_count': [20, 0, 80, 0],
'id': ['aa', 'aa', 'bb', 'bb'], 'date': ['q122', 'q222', 'q122', 'q222'],
'r_power': [50, 20, 0, 100], 'r_count': [2, 1, 0, 5],
'd_power': [20, 0, 50, 0], 'd_count': [1, 0, 3, 0]
})

Answer 3

修改df，使任何以d_开头的列都有一个负号:

df_c = pd.DataFrame({key : value * -1 
if key.startswith('d_') 
else value  
for key, value 
in df.items()})

df_c = df_c.set_index(['id', 'date'])

power和count的过滤器和聚合:

power = df_c.filter(like='power').sum(1).rename('power')
count = df_c.filter(like='count').sum(1).rename('count')
df_c = pd.concat([power, count], axis = 1)

计算累计总和id和重塑:

df_c = (df_c.groupby('id')
.cumsum()
.unstack('date')
.swaplevel(axis=1)
.rename_axis(columns=[None, None])
)
df_c.columns = df_c.columns.map('_'.join)
df_c.reset_index()

id  q122_power  q222_power  q122_count  q222_count
0  aa         530         550          21          22
1  bb         950        1050          77          82