If I have a document like this, with the column names repeated in row 1 and row 2 and the unit of the parameter in row 3, how do I call pd.read_csv such that it creates a data frame with the headers having the column name and unit and the values?
Time Speed Torque
time speed torque
seconds m/s Nm
1 4000 229,5
2 4000 228,7
3 4000 230,1
If want MultiIndex in columns use parameters header=[0,1] for convert first and second row without skipped row(s):
import pandas as pd
temp=u"""Time Speed Torque
time speed torque
seconds m/s Nm
1 4000 229,5
2 4000 228,7
3 4000 230,1"""
#after testing replace 'pd.compat.StringIO(temp)' to 'filename.csv'
df = pd.read_csv(pd.compat.StringIO(temp), sep="\s+", header=[0,1], skiprows=[0])
print (df)
time speed torque
seconds m/s Nm
0 1 4000 229,5
1 2 4000 228,7
2 3 4000 230,1
print (df.columns)
MultiIndex(levels=[['speed', 'time', 'torque'], ['Nm', 'm/s', 'seconds']],
labels=[[1, 0, 2], [2, 1, 0]])
import pandas as pd
temp=u"""Time Speed Torque
time speed torque
seconds m/s Nm
1 4000 229,5
2 4000 228,7
3 4000 230,1"""
#after testing replace 'pd.compat.StringIO(temp)' to 'filename.csv'
df = pd.read_csv(pd.compat.StringIO(temp), sep="\s+", header=[0,1], skiprows=[1])
print (df)
Time Speed Torque
seconds m/s Nm
0 1 4000 229,5
1 2 4000 228,7
2 3 4000 230,1
If want omit second and third rows use only skiprows parameter:
import pandas as pd
temp=u"""Time Speed Torque
time speed torque
seconds m/s Nm
1 4000 229,5
2 4000 228,7
3 4000 230,1"""
#after testing replace 'pd.compat.StringIO(temp)' to 'filename.csv'
df = pd.read_csv(pd.compat.StringIO(temp), sep="\s+", skiprows=[1, 2])
print (df)
Time Speed Torque
0 1 4000 229,5
1 2 4000 228,7
2 3 4000 230,1
