Load CSV to Pandas MultiIndex DataFrame

Question

I have a 719mb CSV file that looks like:

from, to, dep, freq, arr, code, mode   (header row)

RGBOXFD,RGBPADTON,127,0,27,99999,2

RGBOXFD,RGBPADTON,127,0,33,99999,2

RGBOXFD,RGBRDLEY,127,0,1425,99999,2

RGBOXFD,RGBCHOLSEY,127,0,52,99999,2

RGBOXFD,RGBMDNHEAD,127,0,91,99999,2

RGBDIDCOTP,RGBPADTON,127,0,46,99999,2

RGBDIDCOTP,RGBPADTON,127,0,3,99999,2

RGBDIDCOTP,RGBCHOLSEY,127,0,61,99999,2

RGBDIDCOTP,RGBRDLEY,127,0,1430,99999,2

RGBDIDCOTP,RGBPADTON,127,0,115,99999,2

and so on... 

I want to load into a pandas DataFrame. Now I know there is a load from csv method:

 r = pd.DataFrame.from_csv('test_data2.csv')

But I specifically want to load it as a 'MultiIndex' DataFrame where from and to are the indexes:

So ending up with:

                   dep, freq, arr, code, mode

RGBOXFD RGBPADTON  127     0   27  99999    2

        RGBRDLEY   127     0   33  99999    2

        RGBCHOLSEY 127     0 1425  99999    2

        RGBMDNHEAD 127     0 1525  99999    2

etc. I'm not sure how to do that?

Answer 1

You could use pd.read_csv, in which you can use the skipinitialspace=True to get rid of those annoying spaces in the header row.:

>>> df = pd.read_csv("test_data2.csv", index_col=[0,1], skipinitialspace=True)

>>> df

                       dep  freq   arr   code  mode

from       to                                      

RGBOXFD    RGBPADTON   127     0    27  99999     2

           RGBPADTON   127     0    33  99999     2

           RGBRDLEY    127     0  1425  99999     2

           RGBCHOLSEY  127     0    52  99999     2

           RGBMDNHEAD  127     0    91  99999     2

RGBDIDCOTP RGBPADTON   127     0    46  99999     2

           RGBPADTON   127     0     3  99999     2

           RGBCHOLSEY  127     0    61  99999     2

           RGBRDLEY    127     0  1430  99999     2

           RGBPADTON   127     0   115  99999     2