按照分组显示来自其他列的相同行值

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

groupby to show same row value from other columns

问题

  1. 使用以下代码段通过“Mode”列分组并从“max, min”的“indicator”中提取值后，让相对值显示在与下方相同的数据框中：

df = pd.read_csv(r'relative.csv')
Grouped = df.groupby('Mode')['Indicator'].agg(['max', 'min'])
print(Grouped)

  2. <details>
  3. <summary>英文:</summary>
  5. [![enter image description here][1]][1]
  7. After groupby by &quot;Mode&quot; column and take out the value from &quot;indicator&quot; of &quot;max, min&quot;, how to let the relative value to show in the same dataframe like below:

df = pd.read_csv(r'relative.csv')
Grouped = df.groupby('Mode')['Indicator'].agg(['max', 'min'])
print(Grouped)

  1. [![enter image description here][2]][2]
  3. (from google, maybe can use from col_value or row_value function, but seem be more complicated, could someone can help to solve it by easy ways? thank you.)
  6.   [1]: https://i.stack.imgur.com/bl63Q.png
  7.   [2]: https://i.stack.imgur.com/2Ynod.png
  9. </details>
  12. # 答案1
  13. **得分**: 1
  15. 您可以在两个步骤中完成，使用`groupby`和`idxmin()`或`idxmax()`：
  17. ```python
  18. # 创建一个包含'Indicator'的最小值的DataFrame，将列名'Value'重命名为'B'
  19. min = df.loc[df.groupby('Mode')['Indicator'].idxmin()].reset_index(drop=True).rename(columns={'Indicator': 'min', 'Value': 'B'})
  20. print(min)
  21. #   Mode  min  B
  22. # 0    A    1  6
  23. # 1    B    1  7
  25. # 创建一个包含'Indicator'的最大值的DataFrame，将列名'Value'重命名为'A'
  26. max = df.loc[df.groupby('Mode')['Indicator'].idxmax()].reset_index(drop=True).rename columns={'Indicator': 'max', 'Value': 'A'})
  27. print(max)
  28. #   Mode  max  A
  29. # 0    A    3  2
  30. # 1    B    4  3
  32. # 合并这两个DataFrame
  33. result = pd.merge(min, max)
  35. # 重新排列列以匹配期望的输出
  36. print(result[['Mode', 'max', 'min', 'A', 'B']])
  37. #   Mode  max  min  A  B
  38. # 0    A    3    1  2  6
  39. # 1    B    4    1  3  7
英文:

You can do it in two steps, using groupby and idxmin() or idxmix():

  1. # Create a df with the min values of &#39;Indicator&#39;, renaming the column &#39;Value&#39; to &#39;B&#39;
  2. min = df.loc[df.groupby(&#39;Mode&#39;)[&#39;Indicator&#39;].idxmin()].reset_index(drop=True).rename(columns={&#39;Indicator&#39;: &#39;min&#39;, &#39;Value&#39;: &#39;B&#39;})
  3. print(min)
  4. #   Mode  min  B
  5. # 0    A    1  6
  6. # 1    B    1  7
  8. # Create a df with the max values of &#39;Indicator&#39;, renaming the column &#39;Value&#39; to &#39;A&#39;
  9. max = df.loc[df.groupby(&#39;Mode&#39;)[&#39;Indicator&#39;].idxmax()].reset_index(drop=True).rename(columns={&#39;Indicator&#39;: &#39;max&#39;, &#39;Value&#39;: &#39;A&#39;})
  10. print(max)
  11. #   Mode  max  A
  12. # 0    A    3  2
  13. # 1    B    4  3
  15. # Merge the dataframes together 
  16. result = pd.merge(min, max)
  18. # reorder the columns to match expected output
  19. print(result[[&#39;Mode&#39;, &#39;max&#39;,&#39;min&#39;,&#39;A&#39;, &#39;B&#39;]])
  20. #   Mode  max  min  A  B
  21. # 0    A    3    1  2  6
  22. # 1    B    4    1  3  7
  24. </details>
  28. # 答案2
  29. **得分**: 0
  31. 以下是代码的翻译部分：
  33. ```python
  34. import pandas as pd
  36. df = pd.DataFrame(
  37.     {
  38.         "Mode": ["A", "A", "A", "B", "B", "B", "B"],
  39.         "Indicator": [1, 2, 3, 4, 3, 2, 1],
  40.         "Value": [6, 5, 2, 3, 6, 8, 7],
  41.     }
  42. )
  44. new_df = df.groupby("Mode")["Indicator"].agg(["max", "min"])
  46. print(new_df)
  47. # 输出
  48.       max  min
  49. Mode
  50. A       3    1
  51. B       4    1

以下是第二段代码的翻译：

  1. from itertools import product
  3. for row, (col, func) in product(["A", "B"], [("A", "max"), ("B", "min")]):
  4.     new_df.at[row, col] = df.loc[
  5.         (df["Mode"] == row) & (df["Indicator"] == new_df.loc[row, func]), "Value"
  6.     ].values[0]
  8. new_df = new_df.astype(int)

然后：

  1. print(new_df)
  2. # 输出
  3.       max  min  A  B
  4. Mode
  5. A       3    1  2  6
  6. B       4    1  3  7
英文:

With the dataframe you provided:

  1. import pandas as pd
  4. df = pd.DataFrame(
  5.     {
  6.         &quot;Mode&quot;: [&quot;A&quot;, &quot;A&quot;, &quot;A&quot;, &quot;B&quot;, &quot;B&quot;, &quot;B&quot;, &quot;B&quot;],
  7.         &quot;Indicator&quot;: [1, 2, 3, 4, 3, 2, 1],
  8.         &quot;Value&quot;: [6, 5, 2, 3, 6, 8, 7],
  9.     }
  10. )
  12. new_df = df.groupby(&quot;Mode&quot;)[&quot;Indicator&quot;].agg([&quot;max&quot;, &quot;min&quot;])
  14. print(new_df)
  15. # Output
  16.       max  min
  17. Mode
  18. A       3    1
  19. B       4    1

Here is one way to do it with product from Python standard library's itertools module and Pandas at property:

  1. from itertools import product
  4. for row, (col, func) in product([&quot;A&quot;, &quot;B&quot;], [(&quot;A&quot;, &quot;max&quot;), (&quot;B&quot;, &quot;min&quot;)]):
  5.     new_df.at[row, col] = df.loc[
  6.         (df[&quot;Mode&quot;] == row) &amp; (df[&quot;Indicator&quot;] == new_df.loc[row, func]), &quot;Value&quot;
  7.     ].values[0]
  9. new_df = new_df.astype(int)

Then:

  1. print(new_df)
  2. # Output
  3.       max  min  A  B
  4. Mode
  5. A       3    1  2  6
  6. B       4    1  3  7

答案3

得分: 0

以下是您要的翻译：

The logic is unclear, there is no real reason why you would call your columns A/B since the 6/3 values in it are not coming from A/B.
逻辑不清晰，实际上没有真正的原因让你将列命名为A/B，因为其中的6/3值并不是来自A/B。

I assume you want to achieve:
我猜您想要实现以下目标：

  1. (df.groupby('Mode')['Indicator'].agg(['idxmax', 'idxmin'])
  2.    .rename(columns={'idxmin': 'min', 'idxmax': 'max'}).stack()
  3.    .to_frame('x').merge(df, left_on='x', right_index=True)
  4.    .drop(columns=['x', 'Mode']).unstack()
  5. )

Output:
输出：

  1.      Indicator     Value    
  2.            max min   max min
  3. Mode                        
  4. A            3   1     2   6
  5. B            4   1     3   7
  6. C           10  10    20  20

Used input:
使用的输入数据：

  1.   Mode  Indicator  Value
  2. 0    A          1      6
  3. 1    A          2      5
  4. 2    A          3      2
  5. 3    B          4      3
  6. 4    B          3      6
  7. 5    B          2      8
  8. 6    B          1      7
  9. 7    C         10     20

请注意，上述内容是您要求的纯翻译，不包含其他信息。

英文:

The logic is unclear, there is no real reason why you would call your columns A/B since the 6/3 values in it are not coming from A/B.

I assume you want to achieve:

  1. (df.groupby(&#39;Mode&#39;)[&#39;Indicator&#39;].agg([&#39;idxmax&#39;, &#39;idxmin&#39;])
  2.    .rename(columns={&#39;idxmin&#39;: &#39;min&#39;, &#39;idxmax&#39;: &#39;max&#39;}).stack()
  3.    .to_frame(&#39;x&#39;).merge(df, left_on=&#39;x&#39;, right_index=True)
  4.    .drop(columns=[&#39;x&#39;, &#39;Mode&#39;]).unstack()
  5. )

Output:

  1.      Indicator     Value    
  2.            max min   max min
  3. Mode                        
  4. A            3   1     2   6
  5. B            4   1     3   7
  6. C           10  10    20  20

Used input:

  1.   Mode  Indicator  Value
  2. 0    A          1      6
  3. 1    A          2      5
  4. 2    A          3      2
  5. 3    B          4      3
  6. 4    B          3      6
  7. 5    B          2      8
  8. 6    B          1      7
  9. 7    C         10     20

huangapple
  • 本文由 发表于 2023年2月18日 09:41:42
  • 转载请务必保留本文链接：https://go.coder-hub.com/75490662.html
  • pandas
匿名

发表评论

匿名网友

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

确定