使用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

逻辑

对于q122，id'aa'的w cons_功率值为500，我们加上r_功率值50，减去d_功率值20，得到的值为：530

然后，我们将530的值作为q222的下一个基线值，即加上r_幂，减去d_幂=550

计数使用相同的概念。（开始计算'aa'q122的20，加2减1）给我们：21的值

然后，我们将值21作为q222的下一个基线值，即加上r_计数，减去d_计数=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

修改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

Answer 2

我们还可以创建一个多索引，以允许使用顶级索引组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

---

工作原理：

^{}然后使用^{}创建多索引：

# 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

然后取^{}得到每个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

^{}将索引级别转换为列级别的日期列：

(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

现在剩下的就是用^{}清理并折叠多索引：

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

和^{}恢复默认范围索引：

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

---

设置和导入：

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

在groupby之前计算power和count，然后透视数据帧。最后平齐列多索引并重置行索引

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