I have the following data in a dataframe:
Product t_Proj CFType1 CFType2 CFType3
0 Product1 0 270 193 130
1 Product1 1 233 197 362
2 Product1 2 130 278 375
3 Product1 3 162 365 225
4 Product1 4 278 138 214
5 Product1 5 167 331 231
6 Product2 0 204 326 339
7 Product2 1 322 293 393
8 Product2 2 144 301 253
9 Product2 3 303 397 174
10 Product2 4 112 178 317
11 Product2 5 204 216 120
12 Product3 0 154 322 137
13 Product3 1 222 110 205
14 Product3 2 110 199 188
15 Product3 3 226 178 247
16 Product3 4 128 356 109
17 Product3 5 323 277 226
I want to transform this into the following format:
0 1 2 3 4 5
Product1 CFType1 270 233 130 162 278 167
Product1 CFType2 193 197 278 365 138 331
Product1 CFType3 130 362 375 225 214 231
Product2 CFType1 204 322 144 303 112 204
Product2 CFType2 326 293 301 397 178 216
Product2 CFType3 339 393 253 174 317 120
Product3 CFType1 154 222 110 226 128 323
Product3 CFType2 322 110 199 178 356 277
Product3 CFType3 137 205 188 247 109 226
I've tried a number of different around pivot, stack/unstack etc.
This is the closest I've managed so far:
df.pivot(index='Product',columns='t_Proj',values['CFType1','CFType2','CFType3'])
Any suggestions on how to get it to the desired format would be much appreciated.
When I add .stack(0) to your pivot() then I get
t_Proj 0 1 2 3 4 5
Product
Product1 CFType1 270 233 130 162 278 167
CFType2 193 197 278 365 138 331
CFType3 130 362 375 225 214 231
Product2 CFType1 204 322 144 303 112 204
CFType2 326 293 301 397 178 216
CFType3 339 393 253 174 317 120
Product3 CFType1 154 222 110 226 128 323
CFType2 322 110 199 178 356 277
CFType3 137 205 188 247 109 226
And if I add also .reset_index() then I get (if you don't want multi-level index)
t_Proj Product level_1 0 1 2 3 4 5
0 Product1 CFType1 270 233 130 162 278 167
1 Product1 CFType2 193 197 278 365 138 331
2 Product1 CFType3 130 362 375 225 214 231
3 Product2 CFType1 204 322 144 303 112 204
4 Product2 CFType2 326 293 301 397 178 216
5 Product2 CFType3 339 393 253 174 317 120
6 Product3 CFType1 154 222 110 226 128 323
7 Product3 CFType2 322 110 199 178 356 277
8 Product3 CFType3 137 205 188 247 109 226
And this may need only some small changes
ie. rename t_Proj to index, and level_1 to something else.
Minimal working code:
I use io.StringIO only to simulate file-object in memory.
text = """index Product t_Proj CFType1 CFType2 CFType3
0 Product1 0 270 193 130
1 Product1 1 233 197 362
2 Product1 2 130 278 375
3 Product1 3 162 365 225
4 Product1 4 278 138 214
5 Product1 5 167 331 231
6 Product2 0 204 326 339
7 Product2 1 322 293 393
8 Product2 2 144 301 253
9 Product2 3 303 397 174
10 Product2 4 112 178 317
11 Product2 5 204 216 120
12 Product3 0 154 322 137
13 Product3 1 222 110 205
14 Product3 2 110 199 188
15 Product3 3 226 178 247
16 Product3 4 128 356 109
17 Product3 5 323 277 226"""
import pandas as pd
import io
df = pd.read_csv(io.StringIO(text), sep=r"\s+", index_col="index")
result = df.pivot(
index="Product",
columns="t_Proj",
values=["CFType1", "CFType2", "CFType3"]
).stack(0) #.reset_index()
print(result)