我有一個 pandas 數(shù)據(jù)框，其中第一行有多個條目，但第二行有重復(fù)的列。 A B CDate open r close open r close open r close 2000-07-03 19.7 5 17.1 66.26 4 6.22 23.26. 1 9.92000-07-05 49.8 2 8.3 78.81 6 4.34 39.81 5 5.12000-07-15 89.5 3 4.1 43.45 7 2.45 29.3 8 1.22000-08-13 74.7 6 7.4 34.26 8 6.4 72.26 9 5.42000-08-25 39.84 1 8.4 95.43 3 4.3 69.81. 0 5.22000-08-28 61.8 4 4.2 43.81 1 2.2 129.81 6 1.32000-09-11 82.79 7 7.4 66.26 1 6.5 72.25 6 5.62000-09-16 64.8 8 8.7 73.45 5 4.7 69.45 4 5.42000-09-22 58.5 9 3.3 13.81 8 2.9 777.8 8 1.4我想提取 2000 年第 7 個月的數(shù)據(jù)，并找出 A 或 B 或 C 中哪個最低（開盤價 - 收盤價）？我的計劃：s=data.stack(level=0)values = s[s.index.get_level_values(1)]['open', 'close'].reset_index()values['Date'] = pd.to_datetime(values['Date'])start_date = 2000-07-01end_date = 2000-08-01mask = (data['date'] > start_date) & (data['date'] <= end_date)df = data.loc[mask]df['Val_Diff'] = df['open'] - df['close']print(df['Val_Diff'].max()) 我得到錯誤KeyError: "None of [Index are in the [columns]"為什么 multiindex 是這段代碼的問題？
1 回答
慕神8447489
TA貢獻1780條經(jīng)驗 獲得超1個贊
我認為這是由于堆棧垂直變形時索引中未命名的列引起的。
工藝流程：
展平多索引的列名。
使用 wide_to_long 函數(shù)從水平轉(zhuǎn)換為垂直
將日期序列轉(zhuǎn)換為“日期時間”格式以進行條件提取。
import pandas as pd
import numpy as np
import io
import datetime
data = '''
Date open r close open r close open r close
2000-07-03 19.7 5 17.1 66.26 4 6.22 23.26 1 9.9
2000-07-05 49.8 2 8.3 78.81 6 4.34 39.81 5 5.1
2000-07-15 89.5 3 4.1 43.45 7 2.45 29.3 8 1.2
2000-08-13 74.7 6 7.4 34.26 8 6.4 72.26 9 5.4
2000-08-25 39.84 1 8.4 95.43 3 4.3 69.81 0 5.2
2000-08-28 61.8 4 4.2 43.81 1 2.2 129.81 6 1.3
2000-09-11 82.79 7 7.4 66.26 1 6.5 72.25 6 5.6
2000-09-16 64.8 8 8.7 73.45 5 4.7 69.45 4 5.4
2000-09-22 58.5 9 3.3 13.81 8 2.9 777.8 8 1.4
'''
data = pd.read_csv(io.StringIO(data), sep='\s+')
idx = pd.MultiIndex.from_arrays([['','A','A','A','B','B','B','C','C','C'], ['Date','open','r','close','open','r','close','open','r','close']])
data.columns = idx
new_cols = [k[1]+'_'+k[0] for k in data.columns[1:]]
new_cols.insert(0, 'Date')
data.columns = new_cols
data = pd.wide_to_long(data,['open','r','close'], i='Date', j='item', sep='_', suffix='\\w+')
data.reset_index(inplace=True)
data['Date'] = pd.to_datetime(data['Date'])
start_date = datetime.datetime(2000,7,1)
end_date = datetime.datetime(2000,8,1)
mask = (data.Date > start_date) & (data.Date <= end_date)
data = data.loc[mask]
data
Date item open r close
0 2000-07-03 A 19.70 5 17.10
1 2000-07-05 A 49.80 2 8.30
2 2000-07-15 A 89.50 3 4.10
9 2000-07-03 B 66.26 4 6.22
10 2000-07-05 B 78.81 6 4.34
11 2000-07-15 B 43.45 7 2.45
18 2000-07-03 C 23.26 1 9.90
19 2000-07-05 C 39.81 5 5.10
20 2000-07-15 C 29.30 8 1.20
data['Val_Diff'] = data['open'] - data['close']
print(data['Val_Diff'].max())
85.4
添加回答
舉報
0/150
提交
取消