<pre><code>import pandas as pd
import numpy as np
# just to create a string buffer, you don't need this if you have csv file
from io import StringIO
# replicate your csv file structure
line1 = ','.join(['slot' + str(x) for x in range(3)]) + '\n'
line2 = 'Serial,Timestamp,' + ','.join(['Height', 'Width', 'Score']*3) + '\n'
np.random.seed(0)
data = np.random.randint(100, 1000, size=9)
line3 = 'FA125,2015_5,'+','.join([str(x) for x in data]) + '\n'
csv_buffer = line1+line2+line3
Out[40]: 'slot0,slot1,slot2\nSerial,Timestamp,Height,Width,Score,Height,Width,Score,Height,Width,Score\nFA125,2015_5,784,659,729,292,935,863,807,459,109\n'
# read your file, set the first 2 columns as index, the rest using multi-level column index
level1 = ['slot'+str(x) for x in range(3)]
level2 = ['Height', 'Width', 'Score']
multi_level_columns = pd.MultiIndex.from_product([level1, level2])
df = pd.read_csv(StringIO(csv_buffer), index_col=[0,1], skiprows=[0], header=0)
df.columns = multi_level_columns
Out[62]:
slot0 slot1 slot2
Height Width Score Height Width Score Height Width Score
Serial Timestamp
FA125 2015_5 784 659 729 292 935 863 807 459 109
# you can reshape the original df
df.stack(level=0)
Out[63]:
Height Score Width
Serial Timestamp
FA125 2015_5 slot0 784 729 659
slot1 292 863 935
slot2 807 109 459
</code></pre>