Read csv file with many named column labels with pandas

Question

I'm brand new to pandas for python. I have a data file that has multiple row labels (per row) and column labels (per column) like the following data of observation counts for 3 different animals (dog,bat,ostrich) at multiple recording times (monday morning, day, night):

   ''    ,    ''      , colLabel:name    , dog   ,    bat     , Ostrich
   ''    ,    ''      , colLabel:genus   , Canis , Chiroptera , Struthio,
   ''    ,    ''      , colLabel:activity, diurnal,  nocturnal,  diurnal
   day   , time of day,  ''              ,        ,           ,         
  Monday , morning    ,    ''            , 17     ,  5        , 2
  Monday , day        ,    ''            , 63     ,  0        , 34
  Monday , night      ,    ''            , 21     ,  68       , 1
  Friday , day        ,    ''            , 72     ,  0        , 34

I'd like to read this data into Pandas where both the rows and columns are hierarchically organized. What is the best way of doing this?

note that the native format of ``to_csv`` is slightly different that this in that the column labels are in the first columns and you don't have that extra column) — Jeff, Sep 19 '13 at 21:55

Andy Hayden · Accepted Answer · 2013-09-19T21:57:07.470

9

You can use the header, index_col and tupleize_cols arguments of read_csv:

In [1]: df = pd.read_csv('foo.csv', header=[0, 1, 2], index_col=[0, 1], tupleize_cols=False, sep='\s*,\s+')

Note: in 0.13 tupelize=False will be the default, so you won't need to use that.

There's a little bit of hacking required to get out the column level names:

In [2]: df.columns.names = df.columns[0]

In [3]: del df[df.columns[0]]

In [4]: df
Out[4]:
colLabel:name           dog         bat    Ostrich
colLabel:genus        Canis  Chiroptera  Struthio,
colLabel:activity   diurnal   nocturnal    diurnal
day    time of day
Monday morning           17           5          2
       day               63           0         34
       night             21          68          1
Friday day               72           0         34

edited Sep 19 '13 at 21:57

answered Sep 19 '13 at 21:35

Andy Hayden

359,921
101
625
535

1

maybe `tupleize_cols=False` so that the resulting indices are actual `MultiIndex` objects? :) – Phillip Cloud Sep 19 '13 at 21:37
@PhillipCloud thanks! had just come into that issue :) One fewer hack! – Andy Hayden Sep 19 '13 at 21:39
2

``tupleize_cols=False`` is the default now in 0.13! (so you will get ``MultiIndex`` columns – Jeff Sep 19 '13 at 21:53

Read csv file with many named column labels with pandas

1 Answers1