需要带有 read_csv 的行和列的 MultiIndex [英] Want MultiIndex for rows and columns with read_csv

问题描述

我的 .csv 文件如下所示:

My .csv file looks like:

Area    When    Year    Month   Tickets
City    Day     2015    1       14
City    Night   2015    1       5
Rural   Day     2015    1       18
Rural   Night   2015    1       21
Suburbs Day     2015    1       15
Suburbs Night   2015    1       21
City    Day     2015    2       13

包含 75 行.我想要一个行多索引和列多索引，看起来像:

containing 75 rows. I want both a row multiindex and column multiindex that looks like:

Area         City        Rural         Suburbs
When         Day Night   Day Night     Day Night
Year Month
2015 1       5.0   3.0  22.0  11.0    13.0   2.0
     2      22.0   8.0   4.0  16.0     6.0  18.0
     3      26.0  25.0  22.0  23.0    22.0   2.0
2016 1      20.0  25.0  39.0  14.0     3.0  10.0
     2       4.0  14.0  16.0  26.0     1.0  24.0
     3      22.0  17.0   7.0  24.0    12.0  20.0 

我在 https 上阅读了 .read_csv 文档://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html

我可以通过以下方式获取行多索引:

I can get the row multiindex with:

df2 = pd.read_csv('c:\\Data\Tickets.csv', index_col=[2, 3])

我试过了:

df2 = pd.read_csv('c:\\Data\Tickets.csv', index_col=[2, 3], header=[1, 3, 5])

thinking [1, 3, 5] 获取城市"、农村"和郊区".如何获得上面显示的所需列多索引?

thinking [1, 3, 5] fetches 'City', 'Rural', and 'Suburbs'. How do I get the desired column multiindex shown above?

推荐答案

似乎您需要pivot_table 具有多个索引和多个列.

Seems like you need to pivot_table with multiple indexes and multiple columns.

从简单地读取您的 csv 开始

Start with just reading you csv plainly

df = pd.read_csv('Tickets.csv')

然后

df.pivot_table(index=['Year', 'Month'], columns=['Area', 'When'], values=['Tickets'])

使用您提供的输入数据，您将获得

With the input data you provided, you'd get

Area             City           Rural            Suburbs
When             Day    Night   Day     Night    Day    Night
Year    Month                       
2015    1        14.0   5.0     18.0    21.0     15.0   21.0
        2        13.0   NaN     NaN     NaN      NaN    NaN

这篇关于需要带有 read_csv 的行和列的 MultiIndex的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！