我有一个示例数据集:
import pandas as pd
df = {
'rank1':[1,2,3,4,5,6,7,8],
'rank12':[1,2,3,4,8,9,37,15],
'rank13':[1,2,3,4,12,6,24,14],
'N':['','','','','','','',''],
'code#':[1945, 13060, 610, 402, 1067, 180, 411, 93],
'score1':[100,97,95,92,87,85,80,79],
'score2':['yes','yes','no','no','yes','yes','no','yes'],
'score3':[10,9,10,9,9,8,9,9],
'score4':['yes','yes','no','no','yes','yes','no','yes'],
'score5':[2,3,2,2,2,2,2,2]
}
df = pd.DataFrame(df)
看起来像:
df
Out[130]:
N code# rank1 rank12 rank13 score1 score2 score3 score4 score5
0 1945 1 1 1 100 yes 10 yes 2
1 13060 2 2 2 97 yes 9 yes 3
2 610 3 3 3 95 no 10 no 2
3 402 4 4 4 92 no 9 no 2
4 1067 5 8 12 87 yes 9 yes 2
5 180 6 9 6 85 yes 8 yes 2
6 411 7 37 24 80 no 9 no 2
7 93 8 15 14 79 yes 9 yes 2
我想将代码# = 93 的最后一行与此处的其余行(仅数字列)进行比较。如果任何值 < 最后一行，将该值替换为 1，如果 >= 最后一行，将该值替换为 0。
期望的输出:
Out[130]:
N code# rank1 rank12 rank13 score1 score2 score3 score4 score5
0 1945 1 1 1 0 yes 0 yes 0
1 13060 1 1 1 0 yes 0 yes 0
2 610 1 1 1 0 no 0 no 0
3 402 1 1 1 0 no 0 no 0
4 1067 1 1 1 0 yes 0 yes 0
5 180 1 1 1 0 yes 1 yes 0
6 411 1 0 0 0 no 0 no 0
7 93 8 15 14 79 yes 9 yes 0
我的想法: 1.创建一个字典，以列名作为键，最后一行的值作为值 2.遍历每一行并与字典值进行比较
我的尝试:
baserow = df[df['code#'] == 93] #get the last row
dict=baserow.to_dict(orient='list') #make the last row into a dictionary
try: #i'm using a try except here because there are non-numeric columns here, this will not raise errors.
for index, row in df.iterrows(): #iterating through each row
for key, value in dict.items(): #iterating through the dictionary
Othervals=df.ix[index][key] #individual value for compare data
vals = dict.get(key)
vals= vals[0] #get dictionary value
if vals>Othervals: #if the dictionary value > other row value then make the cell 1, else 0
df[[index][key]] == 1
else:
df[[index][key]] == 0
except:
pass
但 df 没有改变，它仍然具有相同的旧值。
请您参考如下方法:
借用@Psidom 的df_numeric
df_numeric = df.drop('code#',1).select_dtypes(include=[pd.np.number])
v = df_numeric.values
df.loc[df.index[:-1], df_numeric.columns] = np.where(v[:-1] < v[-1], 1, 0)
df
N code# rank1 rank12 rank13 score1 score2 score3 score4 score5
0 1945 1 1 1 0 yes 0 yes 0
1 13060 1 1 1 0 yes 0 yes 0
2 610 1 1 1 0 no 0 no 0
3 402 1 1 1 0 no 0 no 0
4 1067 1 1 1 0 yes 0 yes 0
5 180 1 1 1 0 yes 1 yes 0
6 411 1 0 0 0 no 0 no 0
7 93 8 15 14 79 yes 9 yes 2
