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?
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
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']
