python - 使用melt重构Dataframe时出现KeyError

Question

我有一个数据框，当前如下所示，有 2628 行和 101 列。我想将与数字 0.08333 0.16666 0.249999 等关联的 years 行转换为一列:

years      Currency  0.08333333  0.16666666  0.24999999  0.33333332  \
2005-01-04      GBP    4.709456    4.633861    4.586271    4.567017   
2005-01-05      GBP    4.713099    4.649220    4.606802    4.588313   
2005-01-06      GBP    4.707237    4.646861    4.609294    4.593076

代码如下，其中combined_data是数据帧。我使用 melt 来执行此操作，但收到错误 KeyError: 'years' 并且不知道如何处理此问题:

from pandas.io.excel import read_excel
import pandas as pd
import numpy as np

url = 'http://www.bankofengland.co.uk/statistics/Documents/yieldcurve/uknom05_mdaily.xls'

# check the sheet number, spot: 9/9, short end 7/9
spot_curve = read_excel(url, sheetname=8)
short_end_spot_curve = read_excel(url, sheetname=6)

# do some cleaning, keep NaN for now, as forward fill NaN is not recommended for yield curve
spot_curve.columns = spot_curve.loc['years:']
spot_curve.columns.name = 'years'
valid_index = spot_curve.index[4:]
spot_curve = spot_curve.loc[valid_index]
# remove all maturities within 5 years as those are duplicated in short-end file
col_mask = spot_curve.columns.values > 5
spot_curve = spot_curve.iloc[:, col_mask]


short_end_spot_curve.columns = short_end_spot_curve.loc['years:']
short_end_spot_curve.columns.name = 'years'
valid_index = short_end_spot_curve.index[4:]
short_end_spot_curve = short_end_spot_curve.loc[valid_index]

# merge these two, time index are identical
# ==============================================
combined_data = pd.concat([short_end_spot_curve, spot_curve], axis=1, join='outer')
# sort the maturity from short end to long end
combined_data.sort_index(axis=1, inplace=True)

def filter_func(group):
    return group.isnull().sum(axis=1) <= 50

combined_data = combined_data.groupby(level=0).filter(filter_func)

idx = 0
values = ['GBP'] * len(combined_data.index)
combined_data.insert(idx, 'Currency', values)
print combined_data

pd.melt(combined_data,id_vars=['years']) #ERROR!

编辑:所需结果:

years                     Currency  
0.08333333   2005-01-04      GBP    4.709456    4.633861    4.586271    4.567017   
0.16666666   2005-01-05      GBP    4.713099    4.649220    4.606802    4.588313   
0.24999999   2005-01-06      GBP    4.707237    4.646861    4.609294    4.593076

Answer 1

这可能需要根据您相对于列的行数进行调整，但会给您所需的结果(或多或少):

        years Currency  0.08333333  0.16666666  0.24999999  0.33333332
0  2005-01-04      GBP    4.709456    4.633861    4.586271    4.567017
1  2005-01-05      GBP    4.713099    4.649220    4.606802    4.588313
2  2005-01-06      GBP    4.707237    4.646861    4.609294    4.593076

df['x'] = df.columns.values[-4:-1]
df = df.set_index('x',drop=True)
df.columns = ['years','Currency','v1','v2','v3','v4']

                 years Currency        v1        v2        v3        v4
x                                                                      
0.08333333  2005-01-04      GBP  4.709456  4.633861  4.586271  4.567017
0.16666666  2005-01-05      GBP  4.713099  4.649220  4.606802  4.588313
0.24999999  2005-01-06      GBP  4.707237  4.646861  4.609294  4.593076