您如何在Pandas中使用条件，聚合和串联来“透视"? [英] How do you “pivot” using conditions, aggregation, and concatenation in Pandas?

问题描述

我有一个格式如下的数据框:

I have a dataframe in a format such as the following:

Index    Name    Fruit           Quantity
0        John    Apple Red       10
1        John    Apple Green      5
2        John    Orange Cali     12
3        Jane    Apple Red       10
4        Jane    Apple Green      5
5        Jane    Orange Cali     18
6        Jane    Orange Spain     2

我需要将其变成这样的数据框:

I need to turn it into a dataframe such as this:

Index    Name    All Fruits                                         Apples Total  Oranges Total
0        John    Apple Red, Apple Green, Orange Cali                          15             12
1        Jane    Apple Red, Apple Green, Orange Cali, Orange Spain            15             20

问题是我该怎么做?我查看了groupby文档以及有关数据透视和聚合的大量文章，但是将其翻译成此用例却使我不知所措.非常感谢任何帮助或指示.

Question is how do I do this? I have looked at the groupby docs as well as a number of posts on pivot and aggregation but translating that into this use case somehow escapes me. Any help or pointers much appreciated.

干杯！

推荐答案

使用 DataFrame.pivot_table ，最后通过连接在一起 DataFrame.join :

Use GroupBy.agg with join, create column F by split and pass to DataFrame.pivot_table, last join together by DataFrame.join:

df1 = df.groupby('Name', sort=False)['Fruit'].agg(', '.join)
df2 = (df.assign(F = df['Fruit'].str.split().str[0])
        .pivot_table(index='Name', 
                     columns='F', 
                     values='Quantity',
                     aggfunc='sum')
        .add_suffix(' Total'))


df3 = df1.to_frame('All Fruits').join(df2).reset_index()
print (df3)
   Name                                         All Fruits  Apple Total  \
0  John                Apple Red, Apple Green, Orange Cali           15   
1  Jane  Apple Red, Apple Green, Orange Cali, Orange Spain           15   

   Orange Total  
0            12  
1            20  

这篇关于您如何在Pandas中使用条件，聚合和串联来“透视"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！