按层次分组多列排序

huangapple
go评论89阅读模式
英文:

Groupby Multi-Column Sort based on hierarchy

问题

我正在尝试按列的层次结构/顺序对groupby的结果进行排序。

数据框示例：

  1. df = pd.DataFrame({'A':['A1','A1','A1','A2','A2','A2','A1','A1'],
  2.                    'B':['B2','B1','B1','B1','B1','B2','B2','B2'],
  3.                    'C':['C1','C3','C1','C3','C2','C2','C1','C3'],
  4.                    'V':[100,240,110,120,50,60,40,40]})

接着进行groupby操作：

  1. df.groupby(['A','B','C']).agg({'V':'sum'}).sort_values(by=['A','V','B','V'], ascending=[False,False,False,False])

输出结果：

  1. 			V
  2. A	B	C	
  3. A2	B1	C3	120
  4.     B2	C2	60
  5.     B1	C2	50
  6. A1	B1	C3	240
  7.     B2	C1	140
  8.     B1	C1	110
  9.     B2	C3	40

然而，我想要的是首先根据A1的总值（与B和C的值无关）对A1进行排序，然后根据B的总值对B进行排序。

因此，输出应该类似于这样：

  1. 			V
  2. A	B	C	
  3. A1	B1	C3	240
  4.     B1	C1	110
  5.     B2	C1	140
  6.     B2	C3	40
  7. A2	B1	C3	120
  8.     B1	C2	50
  9.     B2	C2	60

请让我知道是否我解释得清楚。

英文:

I am trying to sort outcome of a groupby by the hierarchy / order of the columns.

Example of dataframe:

  1. df = pd.DataFrame({'A':['A1','A1','A1','A2','A2','A2','A1','A1'],
  2.                    'B':['B2','B1','B1','B1','B1','B2','B2','B2'],
  3.                    'C':['C1','C3','C1','C3','C2','C2','C1','C3'],
  4.                    'V':[100,240,110,120,50,60,40,40]})

Followed by the groupby operation:

  1. df.groupby(['A','B','C']).agg({'V':'sum'}).sort_values(by=['A','V','B','V'],ascending=[False,False,False,False])

Output:

  1. 			V
  2. A	B	C	
  3. A2	B1	C3	120
  4.     B2	C2	60
  5.     B1	C2	50
  6. A1	B1	C3	240
  7.     B2	C1	140
  8.     B1	C1	110
  9.     B2	C3	40

What I want however is A1 should be sorted first based on it's own total value (irrespective of B & C values), followed by B based on it's own total.

So the output should look something like this:

  1. 			V
  2. A	B	C	
  3. A1	B1	C3	240
  4.     B1	C1	110
  5.     B2	C1	140
  6.     B2	C3	40
  7. A2	B1	C3	120
  8.     B1	C2	50
  9.     B2	C2	60

Let me know if I could explain it well.

答案1

得分: 1

  1. df = pd.DataFrame({'A':['A1','A1','A1','A2','A2','A2','A1','A1'],
  2.                    'B':['B2','B1','B1','B1','B1','B2','B2','B2'],
  3.                    'C':['C1','C3','C1','C3','C2','C2','C1','C3'],
  4.                    'V':[100,240,110,120,50,60,40,40]})
  6. aa = df.groupby(['A','B','C']).sum()
  7. print(aa)
  9. """
  10.             V
  11. A  B  C      
  12. A1 B1 C1  110
  13.       C3  240
  14.    B2 C1  140
  15.       C3   40
  16. A2 B1 C2   50
  17.       C3  120
  18.    B2 C2   60
  19. """
  21. t1= (
  22.     df.groupby(['A','B','C']).sum().sort_values(['V','C'],ascending=False)
  23.     .sort_index(level=[0,1],sort_remaining = False)
  24.     )
  26. print(t1)
  28. """
  29.             V
  30. A  B  C      
  31. A1 B1 C3  240
  32.       C1  110
  33.    B2 C1  140
  34.       C3   40
  35. A2 B1 C3  120
  36.       C2   50
  37.    B2 C2   60
  38. """
英文: 
  1. df = pd.DataFrame({'A':['A1','A1','A1','A2','A2','A2','A1','A1'],
  2.                    'B':['B2','B1','B1','B1','B1','B2','B2','B2'],
  3.                    'C':['C1','C3','C1','C3','C2','C2','C1','C3'],
  4.                    'V':[100,240,110,120,50,60,40,40]})
  9. aa = df.groupby(['A','B','C']).sum()
  10. print(aa)
  13. """
  14.             V
  15. A  B  C      
  16. A1 B1 C1  110
  17.       C3  240
  18.    B2 C1  140
  19.       C3   40
  20. A2 B1 C2   50
  21.       C3  120
  22.    B2 C2   60
  24. """
  27. t1= (
  28.     df.groupby(['A','B','C']).sum().sort_values(['V','C'],ascending=False)
  29.     .sort_index(level=[0,1],sort_remaining = False)
  30.     )
  32. print(t1)
  34. """
  35.             V
  36. A  B  C      
  37. A1 B1 C3  240
  38.       C1  110
  39.    B2 C1  140
  40.       C3   40
  41. A2 B1 C3  120
  42.       C2   50
  43.    B2 C2   60
  45. """

答案2

得分: 0

IIUC，您希望同时按A、B和V列进行排序

  1. out = (df.groupby(['A','B','C']).agg({'V':'sum'})
  2.        .sort_values(by=['A','B','V'], ascending=[True,True,False]))
  1. print(out)
  3.             V
  4. A  B  C
  5. A1 B1 C3  240
  6.       C1  110
  7.    B2 C1  140
  8.       C3   40
  9. A2 B1 C3  120
  10.       C2   50
  11.    B2 C2   60
英文:

IIUC, you want to sort by A, B and V column in the same time

  1. out = (df.groupby(['A','B','C']).agg({'V':'sum'})
  2.        .sort_values(by=['A','B','V'], ascending=[True,True,False]))
  1. print(out)
  3.             V
  4. A  B  C
  5. A1 B1 C3  240
  6.       C1  110
  7.    B2 C1  140
  8.       C3   40
  9. A2 B1 C3  120
  10.       C2   50
  11.    B2 C2   60

huangapple
  • 本文由 发表于 2023年3月1日 12:20:37
  • 转载请务必保留本文链接：https://go.coder-hub.com/75599546.html
  • group-by
  • pandas
  • python
  • sorting
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定