Python pandas 基于多个列值进行分组
问题描述
我在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
相关文章