Python熊猫——如何在列中平坦化层次索引

I have a data frame with a hierarchical index in axis 1 (columns) (from a groupby.agg operation):

我有一个在轴1(列)(来自groupby)中具有层次索引的数据框架。gg操作):

     USAF   WBAN  year  month  day  s_PC  s_CL  s_CD  s_CNT  tempf       
                                     sum   sum   sum    sum   amax   amin
0  702730  26451  1993      1    1     1     0    12     13  30.92  24.98
1  702730  26451  1993      1    2     0     0    13     13  32.00  24.98
2  702730  26451  1993      1    3     1    10     2     13  23.00   6.98
3  702730  26451  1993      1    4     1     0    12     13  10.04   3.92
4  702730  26451  1993      1    5     3     0    10     13  19.94  10.94

I want to flatten it, so that it looks like this (names aren't critical - I could rename):

我想把它弄平，让它看起来像这样(名字不重要——我可以重命名):

     USAF   WBAN  year  month  day  s_PC  s_CL  s_CD  s_CNT  tempf_amax  tmpf_amin   
0  702730  26451  1993      1    1     1     0    12     13  30.92          24.98
1  702730  26451  1993      1    2     0     0    13     13  32.00          24.98
2  702730  26451  1993      1    3     1    10     2     13  23.00          6.98
3  702730  26451  1993      1    4     1     0    12     13  10.04          3.92
4  702730  26451  1993      1    5     3     0    10     13  19.94          10.94

How do I do this? (I've tried a lot, to no avail.)

我该怎么做呢?(我试了很多，但都没用。)

Per a suggestion, here is the head in dict form

根据一项建议，这里有一个标题

{('USAF', ''): {0: '702730',
  1: '702730',
  2: '702730',
  3: '702730',
  4: '702730'},
 ('WBAN', ''): {0: '26451', 1: '26451', 2: '26451', 3: '26451', 4: '26451'},
 ('day', ''): {0: 1, 1: 2, 2: 3, 3: 4, 4: 5},
 ('month', ''): {0: 1, 1: 1, 2: 1, 3: 1, 4: 1},
 ('s_CD', 'sum'): {0: 12.0, 1: 13.0, 2: 2.0, 3: 12.0, 4: 10.0},
 ('s_CL', 'sum'): {0: 0.0, 1: 0.0, 2: 10.0, 3: 0.0, 4: 0.0},
 ('s_CNT', 'sum'): {0: 13.0, 1: 13.0, 2: 13.0, 3: 13.0, 4: 13.0},
 ('s_PC', 'sum'): {0: 1.0, 1: 0.0, 2: 1.0, 3: 1.0, 4: 3.0},
 ('tempf', 'amax'): {0: 30.920000000000002,
  1: 32.0,
  2: 23.0,
  3: 10.039999999999999,
  4: 19.939999999999998},
 ('tempf', 'amin'): {0: 24.98,
  1: 24.98,
  2: 6.9799999999999969,
  3: 3.9199999999999982,
  4: 10.940000000000001},
 ('year', ''): {0: 1993, 1: 1993, 2: 1993, 3: 1993, 4: 1993}}

11 个解决方案

#1

I think the easiest way to do this would be to set the columns to the top level:

我认为最简单的方法是将列设置为顶层:

df.columns = df.columns.get_level_values(0)

Note: if the to level has a name you can also access it by this, rather than 0.

注意:如果to级有一个名称，您也可以通过这个而不是0访问它。

.

。

If you want to combine/join your MultiIndex into one Index (assuming you have just string entries in your columns) you could:

如果你想把你的多索引合并成一个索引(假设你的列中只有字符串项)，你可以:

df.columns = [' '.join(col).strip() for col in df.columns.values]

Note: we must strip the whitespace for when there is no second index.

注意:当没有第二个索引时，我们必须删除空格。

In [11]: [' '.join(col).strip() for col in df.columns.values]
Out[11]: 
['USAF',
 'WBAN',
 'day',
 'month',
 's_CD sum',
 's_CL sum',
 's_CNT sum',
 's_PC sum',
 'tempf amax',
 'tempf amin',
 'year']

#2

pd.DataFrame(df.to_records()) # multiindex become columns and new index is integers only

#3

Andy Hayden's answer is certainly the easiest way -- if you want to avoid duplicate column labels you need to tweak a bit

安迪·海登的答案当然是最简单的方法——如果你想避免重复的列标签，你需要稍微调整一下

In [34]: df
Out[34]: 
     USAF   WBAN  day  month  s_CD  s_CL  s_CNT  s_PC  tempf         year
                               sum   sum    sum   sum   amax   amin      
0  702730  26451    1      1    12     0     13     1  30.92  24.98  1993
1  702730  26451    2      1    13     0     13     0  32.00  24.98  1993
2  702730  26451    3      1     2    10     13     1  23.00   6.98  1993
3  702730  26451    4      1    12     0     13     1  10.04   3.92  1993
4  702730  26451    5      1    10     0     13     3  19.94  10.94  1993


In [35]: mi = df.columns

In [36]: mi
Out[36]: 
MultiIndex
[(USAF, ), (WBAN, ), (day, ), (month, ), (s_CD, sum), (s_CL, sum), (s_CNT, sum), (s_PC, sum), (tempf, amax), (tempf, amin), (year, )]


In [37]: mi.tolist()
Out[37]: 
[('USAF', ''),
 ('WBAN', ''),
 ('day', ''),
 ('month', ''),
 ('s_CD', 'sum'),
 ('s_CL', 'sum'),
 ('s_CNT', 'sum'),
 ('s_PC', 'sum'),
 ('tempf', 'amax'),
 ('tempf', 'amin'),
 ('year', '')]

In [38]: ind = pd.Index([e[0] + e[1] for e in mi.tolist()])

In [39]: ind
Out[39]: Index([USAF, WBAN, day, month, s_CDsum, s_CLsum, s_CNTsum, s_PCsum, tempfamax, tempfamin, year], dtype=object)

In [40]: df.columns = ind




In [46]: df
Out[46]: 
     USAF   WBAN  day  month  s_CDsum  s_CLsum  s_CNTsum  s_PCsum  tempfamax  tempfamin  \
0  702730  26451    1      1       12        0        13        1      30.92      24.98   
1  702730  26451    2      1       13        0        13        0      32.00      24.98   
2  702730  26451    3      1        2       10        13        1      23.00       6.98   
3  702730  26451    4      1       12        0        13        1      10.04       3.92   
4  702730  26451    5      1       10        0        13        3      19.94      10.94   




   year  
0  1993  
1  1993  
2  1993  
3  1993  
4  1993

#4

df.columns = ['_'.join(tup).rstrip('_') for tup in df.columns.values]

#5

And if you want to retain any of the aggregation info from the second level of the multiindex you can try this:

如果你想从multiindex的第二层次保留任何聚合信息你可以试试

In [1]: new_cols = [''.join(t) for t in df.columns]
Out[1]:
['USAF',
 'WBAN',
 'day',
 'month',
 's_CDsum',
 's_CLsum',
 's_CNTsum',
 's_PCsum',
 'tempfamax',
 'tempfamin',
 'year']

In [2]: df.columns = new_cols

#6

In case you want to have a separator in the name between levels, this function works well.

如果您希望在级别之间的名称中有一个分隔符，这个函数可以很好地工作。

def flattenHierarchicalCol(col,sep = '_'):
    if not type(col) is tuple:
        return col
    else:
        new_col = ''
        for leveli,level in enumerate(col):
            if not level == '':
                if not leveli == 0:
                    new_col += sep
                new_col += level
        return new_col

df.columns = df.columns.map(flattenHierarchicalCol)

#7

A bit late maybe, but if you are not worried about duplicate column names:

可能有点晚，但如果你不担心重复的列名:

df.columns = df.columns.tolist()

#8

A general solution that handles multiple levels and mixed types:

一个通用的解决方案，可以处理多个级别和混合类型:

df.columns = ['_'.join(tuple(map(str, t))) for t in df.columns.values]

#9

Following @jxstanford and @tvt173, I wrote a quick function which should do the trick, regardless of string/int column names:

在@jxstanford和@tvt173之后，我编写了一个快速函数，无论字符串/int列名如何，都应该执行这个技巧:

def flatten_cols(df):
    df.columns = [
        '_'.join(tuple(map(str, t))).rstrip('_') 
        for t in df.columns.values
        ]
    return df

#10

After reading through all the answers, I came up with this:

在通读了所有的答案后，我想到了这个:

def __my_flatten_cols(self, how="_".join, reset_index=True):
    how = (lambda iter: list(iter)[-1]) if how == "last" else how
    self.columns = [how(filter(None, map(str, levels))) for levels in self.columns.values] \
                    if isinstance(self.columns, pd.MultiIndex) else self.columns
    return self.reset_index() if reset_index else self
pd.DataFrame.my_flatten_cols = __my_flatten_cols

Usage:

Given a data frame:

给定一个数据帧:

df = pd.DataFrame({"grouper": ["x","x","y","y"], "val1": [0,2,4,6], 2: [1,3,5,7]}, columns=["grouper", "val1", 2])

  grouper  val1  2
0       x     0  1
1       x     2  3
2       y     4  5
3       y     6  7

• Single aggregation method: resulting variables named the same as source:

  单聚合方法:结果变量名称与来源相同:

  df.groupby(by="grouper").agg("min").my_flatten_cols()
  

  • Same as df.groupby(by="grouper", as_index=False) or .agg(...).reset_index()
  • df一样。groupby(=“石斑鱼”,as_index = False)或.agg(…).reset_index()

  • ----- before -----
               val1  2
      grouper         
    
    ------ after -----
      grouper  val1  2
    0       x     0  1
    1       y     4  5
    

  • - - - - - - - - - - - -之前

• Single source variable, multiple aggregations: resulting variables named after statistics:

  单源变量、多聚合:统计信息命名的结果变量:

  df.groupby(by="grouper").agg({"val1": [min,max]}).my_flatten_cols("last")
  

  • Same as a = df.groupby(..).agg(..); a.columns = a.columns.droplevel(0); a.reset_index().
  • 与a = df.groupby(.. .).agg(.. .)相同;一个。列= a.columns.droplevel(0);a.reset_index()。

  • ----- before -----
                val1    
               min max
      grouper         
    
    ------ after -----
      grouper  min  max
    0       x    0    2
    1       y    4    6
    

  • - - - - - - - - - - - -之前

• Multiple variables, multiple aggregations: resulting variables named (varname)_(statname):

  多个变量，多个聚合:结果变量命名(varname)_(statname):

  df.groupby(by="grouper").agg({"val1": min, 2:[sum, "size"]}).my_flatten_cols()
  # you can combine the names in other ways too, e.g. use a different delimiter:
  #df.groupby(by="grouper").agg({"val1": min, 2:[sum, "size"]}).my_flatten_cols(" ".join)
  

  • Same as a.columns = "_".join(filter(None, map(str, levels))) for levels in a.columns.values]
  • 同样的作为。列=“_”。join(filter(None, map(str, level))用于a.columns.values中的级别)。
  • Same as a.columns = ["_".join(tup).rstrip("_") for tup in a.columns.values] if you don't have numeric labels on columns
  • 同样的作为。列= ["_".join(tup).rstrip("_")在a栏中。值]如果列上没有数字标签
  • Same as a.columns = ["_".join(tuple(map(str, t))).rstrip("_") for t in a.columns.values] if you do have numeric labels on columns
  • 同样的作为。列=(“_”。在a列中为t连接(tuple(map(str, t)) .rstrip("_")。如果您在列上有数字标签。

  • ----- before -----
               val1           2     
               min       sum    size
      grouper              
    
    ------ after -----
      grouper  val1_min  2_sum  2_size
    0       x         0      4       2
    1       y         4     12       2
    

  • - - - - - - - - - - - -之前

• You want to name the resulting variables manually: (this is deprecated since pandas 0.20.0 with no adequate alternative as of 0.23)

  您需要手动命名结果变量:(由于熊猫0.20.0没有足够的替代值为0.23，所以不建议这样做)

  df.groupby(by="grouper").agg({"val1": {"sum_of_val1": "sum", "count_of_val1": "count"},
                                     2: {"sum_of_2":    "sum", "count_of_2":    "count"}}).my_flatten_cols("last")
  

  • Other suggestions include: setting the columns manually: res.columns = ['A_sum', 'B_sum', 'count'] or .join()ing multiple groupby statements.
  • 其他建议包括:手动设置列:res.columns = ['A_sum'、'B_sum'、'count']或.join()，使用多个groupby语句。

  • ----- before -----
                       val1                      2         
              count_of_val1 sum_of_val1 count_of_2 sum_of_2
      grouper                                              
    
    ------ after -----
      grouper  count_of_val1  sum_of_val1  count_of_2  sum_of_2
    0       x              2            2           2         4
    1       y              2           10           2        12
    

  • - - - - - - - - - - - -之前

Cases handled by the helper function

• level names can be non-string, e.g. Index pandas DataFrame by column numbers, when column names are integers, so we have to convert with map(str, ..)
• 级别名称可以是非字符串，例如按列号索引熊猫DataFrame，当列名是整数时，因此我们必须使用map(str .. .)进行转换。
• they can also be empty, so we have to filter(None, ..)
• 它们也可以是空的，所以我们必须过滤(无，..)
• for single-level columns (i.e. anything except MultiIndex), columns.values returns the names (str, not tuples)
• 对于单级列(即除多索引外的任何列)，列。值返回名称(str，不是元组)
• depending on how you used .agg() you may need to keep the bottom-most label for a column or concatenate multiple labels
• 根据您如何使用.agg()，您可能需要保留一个列的最底部的标签或连接多个标签。
• (since I'm new to pandas?) more often than not, I want reset_index() to be able to work with the group-by columns in the regular way, so it does that by default
• (因为我对熊猫不熟吗?)通常，我希望reset_index()能够以常规方式处理组-by列，因此默认情况下是这样做的

#11

You could also do as below. Consider df to be your dataframe and assume a two level index (as is the case in your example)

您也可以如下所示。将df作为您的dataframe，并假设一个两个级别的索引(在您的示例中是这样)

df.columns = [(df.columns[i][0])+'_'+(datadf_pos4.columns[i][1]) for i in range(len(df.columns))]