How to transpose/pivot and group columns to rows in Pandas Dataframe?-CodePudding

I have data that looks like this:

Date Sales1 Sales2 Sales3

date1 1.1  1.2  1.3
date2 2.1  2.2  2.3
date3 3.1  3.2  3.3

The desired output is to add a second column for better visibility and pivot the columns

Date SalesType Sales

date1 Sales1 1.1
date1 Sales2 1.2
date1 Sales3 1.3
date2 Sales1 2.1
date2 Sales2 2.2
date2 Sales3 2.3
date3 Sales1 3.1
date3 Sales2 3.2
date3 Sales3 3.3

Is there a way to get that type of pivot?

CodePudding user response：

Try this:

res = (df.melt(id_vars='Date', var_name='Sales_Type',value_name='Sales')
       .sort_values('Date')
       .reset_index(drop=True))

print(res)

    Date Sales_Type  Sales
0  date1     Sales1    1.1
1  date1     Sales2    1.2
2  date1     Sales3    1.3
3  date2     Sales1    2.1
4  date2     Sales2    2.2
5  date2     Sales3    2.3
6  date3     Sales1    3.1
7  date3     Sales2    3.2
8  date3     Sales3    3.3

CodePudding user response：

Here you go:

df = df.set_index('Date').stack().reset_index()
df.columns=['Date', 'SalesType', 'Sales']

Full test code:

import pandas as pd
df = pd.DataFrame({'Date':['date1','date2','date3'], 'Sales1':[1.1,2.1,3.1], 'Sales2':[1.2,2.2,3.2], 'Sales3':[1.3,2.3,3.3]})
print(df)
df = df.set_index('Date').stack().reset_index()
df.columns=['Date', 'SalesType', 'Sales']
print(df)

Input:

    Date  Sales1  Sales2  Sales3
0  date1     1.1     1.2     1.3
1  date2     2.1     2.2     2.3
2  date3     3.1     3.2     3.3

Output:

    Date SalesType  Sales
0  date1    Sales1    1.1
1  date1    Sales2    1.2
2  date1    Sales3    1.3
3  date2    Sales1    2.1
4  date2    Sales2    2.2
5  date2    Sales3    2.3
6  date3    Sales1    3.1
7  date3    Sales2    3.2
8  date3    Sales3    3.3

UPDATE:

For fun, if your version of python supports the walrus operator (technically, the "conditional operator") :=, you can do it in one line like this:

(df := df.set_index('Date').stack().reset_index()).columns=['Date', 'SalesType', 'Sales']