Python pandas 基于多个列值进行分组 [英] Python pandas groupby values based on multiple column values

问题描述

我在Pandas数据集中有一个连续的活动数据。

#sample data code 
user_id = [9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,9,4705,4705,4705,4705,4705,223,223,223,223,223,223,223,223]
transaction_Value= [50,125,0,100,0,1000,473,0,47,110,0,44,93,0,49,92,0,242,0,75,0,47,122,0,50,100,200,0,35,85,0,50]
Campaign = ['M1','M1','Used','M1','Used','W1','Used','Used','W2','W2','Used','W2','W2','Used','W2','W2','Used','O1','Used','W3','Used','W2','S1','Lost','M1','M1','M1','Used','W2','S2','Lost','S2',]
transaction_c= [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,1,2,3,4,5,1,2,3,4,5,6,7,8]
 
df = pd.DataFrame(list(zip(user_id,transaction_Value,Campaign,transaction_c)), columns =['user_id','transaction_Value', 'Campaign','transaction_c'])

到目前为止，我已经使用以下代码对数据进行了分组

df2 = (df.set_index(['user_id',df.groupby('user_id').cumcount()])[('transaction_Value')]
         .unstack(fill_value='')
         .reset_index())

这将根据交易编号调换数值

| user_id | 0  | 1   | 2   | 3   | 4  | 5    | 6   | 7  | 8  | 9   | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17  | 18 |
|---------|----|-----|-----|-----|----|------|-----|----|----|-----|----|----|----|----|----|----|----|-----|----|
| 9       | 50 | 125 | 0   | 100 | 0  | 1000 | 473 | 0  | 47 | 110 | 0  | 44 | 93 | 0  | 49 | 92 | 0  | 242 | 0  |
| 223     | 50 | 100 | 200 | 0   | 35 | 85   | 0   | 50 |    |     |    |    |    |    |    |    |    |     |    |
| 4705    | 75 | 0   | 47  | 122 | 0  |      |     |    |    |     |    |    |    |    |    |    |    |     |    |

如何编写代码以使其更改为每次使用行值或丢失

我可以对活动值执行相同的操作，然后将这两个数据帧堆叠在一起

理想输出

| user_id | Type        | 1    | 2    | 3    | 4    |
|---------|-------------|------|------|------|------|
| 9       | Campaign    | M1   | M1   | Used |      |
| 9       | Campaign    | M1   | Used |      |      |
| 9       | Campaign    | W1   | Used |      |      |
| 9       | Campaign    | Used |      |      |      |
| 9       | Campaign    | W2   | W2   | Used |      |
| 9       | Campaign    | W2   | W2   | Used |      |
| 9       | Campaign    | W2   | W2   | Used |      |
| 9       | Campaign    | O1   | Used |      |      |
| 223     | Campaign    | M1   | M1   | M1   | Used |
| 223     | Campaign    | W2   | S2   | Lost |      |
| 223     | Campaign    | S2   |      |      |      |
| 9       | Transaction | 50   | 125  | 0    |      |
| 9       | Transaction | 100  | 0    |      |      |
| 9       | Transaction | 1000 | 473  |      |      |
| 9       | Transaction | 0    |      |      |      |
| 9       | Transaction | 47   | 110  | 0    |      |
| 9       | Transaction | 44   | 93   | 0    |      |
| 9       | Transaction | 49   | 92   | 0    |      |
| 223     | Transaction | 242  | 0    |      |      |
| 223     | Transaction | 50   | 100  | 200  | 0    |
| 223     | Transaction | 35   | 85   | 0    |      |
| 223     | Transaction | 50   |      |      |      |

感谢您在解决此问题方面提供的所有帮助。 谢谢：)

推荐答案

创建分组依据测试CampaignSeries.isin，变更单依据iloc和创建分组依据Series.cumsum，添加到set_index和groupby，然后使用DataFrame.stack并按第三级排序，最后删除第二级并将MultiIndex转换为列：

g = df['Campaign'].isin(['Used','Lost']).iloc[::-1].cumsum().iloc[::-1]
g = pd.factorize(g)[0]

df2 = (df.set_index(['user_id',g, df.groupby(['user_id', g]).cumcount()])[['Campaign','transaction_Value']]
          .unstack(fill_value='')
          .stack(0)
          .sort_index(level=[2])
          .rename_axis(['user_id','Campaign','Type'])
          .reset_index(level=1, drop=True)
          .reset_index())

---

print (df2)
    user_id               Type     0     1     2     3
0         9           Campaign    M1    M1  Used      
1         9           Campaign    M1  Used            
2         9           Campaign    W1  Used            
3         9           Campaign  Used                  
4         9           Campaign    W2    W2  Used      
5         9           Campaign    W2    W2  Used      
6         9           Campaign    W2    W2  Used      
7         9           Campaign    O1  Used            
8       223           Campaign    M1    M1    M1  Used
9       223           Campaign    W2    S2  Lost      
10      223           Campaign    S2                  
11     4705           Campaign    W3  Used            
12     4705           Campaign    W2    S1  Lost      
13        9  transaction_Value    50   125     0      
14        9  transaction_Value   100     0            
15        9  transaction_Value  1000   473            
16        9  transaction_Value     0                  
17        9  transaction_Value    47   110     0      
18        9  transaction_Value    44    93     0      
19        9  transaction_Value    49    92     0      
20        9  transaction_Value   242     0            
21      223  transaction_Value    50   100   200     0
22      223  transaction_Value    35    85     0      
23      223  transaction_Value    50                  
24     4705  transaction_Value    75     0            
25     4705  transaction_Value    47   122     0      

这篇关于Python pandas 基于多个列值进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！