按组合并并将每次出现保存在列中

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

GroupBy and save each Occurence in Columns

问题

我有以下问题：

  1. df
  3. Key1     Key2      Value1     Value2     FixedValue
  4. A        A         12         32         15
  5. A        A         40         25         15
  6. A        A         13         12         15
  7. A        A         80         100        15
  8. B        A         0          1          20
  9. B        A         0          12         20
  10. A        B         50         50         40
  11. B        B         7          8          30

我想创建一个新的数据框，每个（Key1，Key2）对只有一行，但创建新列以保存Value1和Value2的不同值（请参考输出示例以更好理解）。FixedValue直接取决于（Key1，Key2），所以不会随时间改变。我想限制创建的新列数量，以避免输出爆炸。

如果我将“新列按Value限制为3”：

  1. Key1     Key2      Value1_1   Value1_2   Value1_3     Value2_1   Value2_2   Value2_3     FixedValue
  2. A        A         12         40         13           32         25         12           15
  3. B        A         0          0                       1          12                      20
  4. A        B         50                                 50                                 40
  5. B        B         7                                  8                                  30

我不介意空白的类型变成不存在的值（它们可以是NaN，''，... 任何值）。

提前感谢您的帮助。

英文:

I have the following problem :

  1. df
  3. Key1     Key2      Value1     Value2     FixedValue
  4. A        A         12         32         15
  5. A        A         40         25         15
  6. A        A         13         12         15
  7. A        A         80         100        15
  8. B        A         0          1          20
  9. B        A         0          12         20
  10. A        B         50         50         40
  11. B        B         7          8          30

What I want is to create a new Dataframe, with only one line for each (Key1, Key2) couple, but creating new columns to keep the different values taken by Value1 and Value2 (see Output Example to understand better).
FixedValue directly depends to (Key1, Key2) so won't change in time.
I'd like to limit to a certain number of new columns created, so my output doesn't explode

  1. Output wanted if I limit number of "new column by Value" to 3 :
  3. Key1     Key2      Value1_1   Value1_2   Value1_3     Value2_1   Value2_2   Value2_3     FixedValue
  4. A        A         12         40         13           32         25         12           15
  5. B        A         0          0                       1          12                      20
  6. A        B         50                                 50                                 40
  7. B        B         7                                  8                                  30

I don't mind the type of the blank going to non-existant values (they can be NaN, '', ... whatever)

Thanks in advance for your help

答案1

得分: 1

使用pivot在过滤每个组保留的行数之后：

  1. N = 3 # 每个组要保留的行数
  3. out = (df
  4.  .assign(col=lambda d: d.groupby(['Key1', 'Key2']).cumcount().add(1))
  5.  .query('col <= @N')
  6.  .pivot(index=['Key1', 'Key2', 'FixedValue'],
  7.         columns='col', values=['Value1', 'Value2']
  8.        )
  9. )
  11. out.columns = out.columns.map(lambda x: f'{x[0]}_{x[1]}')
  12. out = out.reset_index()

输出：

  1.   Key1 Key2  FixedValue  Value1_1  Value1_2  Value1_3  Value2_1  Value2_2  Value2_3
  2. 0    A    A          15      12.0      40.0      13.0      32.0      25.0      12.0
  3. 1    A    B          40      50.0       NaN       NaN      50.0       NaN       NaN
  4. 2    B    A          20       0.0       0.0       NaN       1.0      12.0       NaN
  5. 3    B    B          30       7.0       NaN       NaN       8.0       NaN       NaN
英文:

Use a pivot after filtering the number of rows to keep per group:

  1. N = 3 # rows to keep per group
  3. out = (df
  4.  .assign(col=lambda d: d.groupby([&#39;Key1&#39;, &#39;Key2&#39;]).cumcount().add(1))
  5.  .query(&#39;col &lt;= @N&#39;)
  6.  .pivot(index=[&#39;Key1&#39;, &#39;Key2&#39;, &#39;FixedValue&#39;],
  7.         columns=&#39;col&#39;, values=[&#39;Value1&#39;, &#39;Value2&#39;]
  8.        )
  9. )
  11. out.columns = out.columns.map(lambda x: f&#39;{x[0]}_{x[1]}&#39;)
  12. out = out.reset_index()

Output:

  1.   Key1 Key2  FixedValue  Value1_1  Value1_2  Value1_3  Value2_1  Value2_2  Value2_3
  2. 0    A    A          15      12.0      40.0      13.0      32.0      25.0      12.0
  3. 1    A    B          40      50.0       NaN       NaN      50.0       NaN       NaN
  4. 2    B    A          20       0.0       0.0       NaN       1.0      12.0       NaN
  5. 3    B    B          30       7.0       NaN       NaN       8.0       NaN       NaN

huangapple
  • 本文由 发表于 2023年2月6日 20:45:58
  • 转载请务必保留本文链接：https://go.coder-hub.com/75361487.html
  • group-by
  • pandas
  • python
匿名

发表评论

匿名网友

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

确定