Complex pivoting in pandas
<p>I have a dataframe like:</p>

<pre><code> In [4]: df
Out[4]: 
        A        B   C   D      E     F   G
0   apple   orange  10  20    cat   rat  10
1   apple   orange  10  20    cat   rat  20
2  grapes   banana  22  34    dog  frog  34
3  grapes   banana  22  34    dog  frog  40
4  grapes   banana  22  34    dog  frog  67
5    kiwi  avocado  90  89    ant   fox  76
6   apple   orange  10  20    cat   rat  10
7  cherry     date  56  91  tiger  lion  65
</code></pre>

<p>My desired output is like:</p>

<pre><code>In [3]: df
Out[3]: 
        A        B   C   D      E     F  G_1  G_2  G_3
0   apple   orange  10  20    cat   rat   10   20    10
1  grapes   banana  22  34    dog  frog   34   40   67
2    kiwi  avocado  90  89    ant   fox   76    0    0
3  cherry     date  56  91  tiger  lion   65    0    0
</code></pre>

<p>I'm confused and tried a lot with <code>pivot_table</code> but could not figure how to add additional columns depending on values.
Thanks for your reply.<br>
<strong>EDIT</strong>
I found a method using groupby but it works only if it is unique:</p>

<pre><code>df.groupby(['A','B','C','D','E','F'])['G'].unique()
Out[26]: 
A       B        C   D   E      F   
apple   orange   10  20  cat    rat         [10, 20]
cherry  date     56  91  tiger  lion            [65]
grapes  banana   22  34  dog    frog    [34, 40, 67]
kiwi    avocado  90  89  ant    fox             [76]
</code></pre>

<p>then I will have to split the list into separate columns.</p>

<p>Suppose if I have two duplicated rows then still I would like to add the value in <code>G</code> as separate column as shown in desired output.How can I include the duplicated values in separate columns.</p>

<p>Here's one way</p>

<pre><code>In [237]: dff = (df.groupby(['A','B','C','D','E','F'])['G'].unique()
   .....:          .apply(pd.Series, 1).fillna(0))

In [238]: dff.columns =  ['G_%s' % (x+1) for x in dff.columns]

In [239]: dff
Out[239]:
                                  G_1   G_2   G_3
A      B       C  D  E     F
apple  orange  10 20 cat   rat   10.0  20.0   0.0
cherry date    56 91 tiger lion  65.0   0.0   0.0
grapes banana  22 34 dog   frog  34.0  40.0  67.0
kiwi   avocado 90 89 ant   fox   76.0   0.0   0.0
</code></pre>

python pandas numpy