Pandas pivot table arrangement no aggregation with sympbol-CodePudding

I want to pivot a pandas dataframe without aggregation, and instead of presenting the pivot index column vertically I want to present it horizontally.

data = {'Period:': ['Value1', 'Value2', 'Value3', 'Value1', 'Value2', 'Value3'],
        '2011': [10, 21, 20, 10, 39, 15],
        '2012': [12, 45, 19, 10, 39, 15],
        '2013': [12, 45, 19, 10, 39, 16],
        'symbol': ['a','a','a','b','b','b']}
df = pd.DataFrame(data)
print (df)


Period:  2011  2012  2013 symbol
0  Value1    10    12    12      a
1  Value2    21    45    45      a
2  Value3    20    19    19      a
3  Value1    10    10    10      b
4  Value2    39    39    39      b
5  Value3    15    15    16      b

But i want to have:

   Period:  Value1  Value2  Value3 symbol
0    2011      10      21      20      a
1    2012      12      45      19      a
2    2013      12      45      19      a
3    2011      10      39      15      b
4    2012      10      39      15      b
5    2013      10      39      16      b

CodePudding user response：

You need to melt and pivot:

(df.melt(['Period:', 'symbol'])
 .pivot(['variable', 'symbol'], 'Period:', 'value')
 .reset_index().rename_axis(index=None, columns=None)
 .rename(columns={'variable':'Period:'})
 )

Output:

   Period: symbol  Value1  Value2  Value3
0     2011      a      10      21      20
1     2011      b      10      39      15
2     2012      a      12      45      19
3     2012      b      10      39      15
4     2013      a      12      45      19
5     2013      b      10      39      16

CodePudding user response：

The answer by @mozway does the heavy lifting required by the question.

Here is a slight variation that renames/reorders rows and columns to get a result more closely matching the one in the question:

x = df.melt(['Period:', 'symbol'])
x = x.pivot(['variable', 'symbol'], 'Period:', 'value')
x = x.reset_index().rename_axis(columns=None).rename(columns={'variable':'Period:'})
x = x[['Period:']   list(df['Period:'].unique())   ['symbol']].sort_values(
    ['symbol', 'Period:']).reset_index(drop=True)
print(x)

Input:

  Period:  2011  2012  2013 symbol
0  Value1    10    12    12      a
1  Value2    21    45    45      a
2  Value3    20    19    19      a
3  Value1    10    10    10      b
4  Value2    39    39    39      b
5  Value3    15    15    16      b

Output:

  Period:  Value1  Value2  Value3 symbol
0    2011      10      21      20      a
1    2012      12      45      19      a
2    2013      12      45      19      a
3    2011      10      39      15      b
4    2012      10      39      15      b
5    2013      10      39      16      b

CodePudding user response：

You can use stack/unstack and set_index/reset_index:

out = (df.set_index(['Period:', 'symbol']).unstack('Period:')
         .stack(level=0).reset_index('symbol'))
print(out)

# Output
Period: symbol  Value1  Value2  Value3
2011         a      10      21      20
2012         a      12      45      19
2013         a      12      45      19
2011         b      10      39      15
2012         b      10      39      15
2013         b      10      39      16

CodePudding user response：

You could just do the transpose like this:

import pandas as pd

data = {'Period:': ['Value1', 'Value2', 'Value3', 'Value1', 'Value2', 'Value3'],
        '2011': [10, 21, 20, 10, 39, 15],
        '2012': [12, 45, 19, 10, 39, 15],
        '2013': [12, 45, 19, 10, 39, 16],
        'symbol': ['a','a','a','b','b','b']}
df = pd.DataFrame(data)
print (df)


dft = df.transpose()
dft

result:

              0       1       2       3       4       5
Period:  Value1  Value2  Value3  Value1  Value2  Value3
2011         10      21      20      10      39      15
2012         12      45      19      10      39      15
2013         12      45      19      10      39      16
symbol        a       a       a       b       b       b

Note that the sympol column is transposed too.