I have a .csv file that looks like this:
Male, Male, Male, Female, Female
R, R, L, R, R
.86, .67, .88, .78, .81
I want to read that into a df, so that I have:
Male Female
R L R
0 .86 .67 .88 .78 .81
I did:
df = pd.read_csv('file.csv', header=[0,1])
But headers does not cut it. Which results in
Empty DataFrame
Columns: [(Male, R), (Male, R), (Male, L), (Female, R), (Female, R)]
Index: []
Yet, the docs on headers says:
(...)Can be a list of integers that specify row
locations for a multi-index on the columns E.g. [0,1,3]
What am I doing wrong? How can I possibly make it work?
I think the problem is that you have duplicated columns: two ( Female, R).
Not sure whether it's a bug or the duplicated columns are unacceptable. Here's a workaround for you:
First read the csv with tupleize_cols=True
In [61]: df = pd.read_csv('test.csv', header=[0, 1], skipinitialspace=True, tupleize_cols=True)
In [62]: df
Out[62]:
(Male, R) (Male, R) (Male, L) (Female, R) (Female, R)
0 0.67 0.67 0.88 0.81 0.81
[1 rows x 5 columns]
Then convert the type of the column from Index to MultiIndex
In [63]: df.columns = pd.MultiIndex.from_tuples(df.columns)
In [64]: df
Out[64]:
Male Female
R R L R R
0 0.67 0.67 0.88 0.81 0.81
[1 rows x 5 columns]
As of version 0.21 of pandas, MultiIndexes are created by default so df = pd.read_csv('file.csv', header=[0,1]) should do the job.
