我正在Pandas / Python中使用DataFrame,每一行都有一个ID(不是唯一的),我想修改数据框,为具有多个匹配ID的每一行添加一个具有第二名称的列.
Starting with:
ID Name Rate
0 1 A 65.5
1 2 B 67.3
2 2 C 78.8
3 3 D 65.0
4 4 E 45.3
5 5 F 52.0
6 5 G 66.0
7 6 H 34.0
8 7 I 2.0
Trying to get to:
ID Name Rate Secondname
0 1 A 65.5 None
1 2 B 67.3 C
2 2 C 78.8 B
3 3 D 65.0 None
4 4 E 45.3 None
5 5 F 52.0 G
6 5 G 66.0 F
7 6 H 34.0 None
8 7 I 2.0 None
我的代码:
import numpy as np
import pandas as pd
mydict = {'ID':[1,2,2,3,4,5,5,6,7],
'Name':['A','B','C','D','E','F','G','H','I'],
'Rate':[65.5,67.3,78.8,65,45.3,52,66,34,2]}
df=pd.DataFrame(mydict)
df['Newname']='None'
for i in range(0, df.shape[0]-1):
if df.irow(i)['ID']==df.irow(i+1)['ID']:
df.irow(i)['Newname']=df.irow(i+1)['Name']
导致以下错误:
A value is trying to be set on a copy of a slice from a DataFrame
See the the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
df.irow(i)['Newname']=df.irow(i+1)['Secondname']
C:\Users\L\Anaconda3\lib\site-packages\pandas\core\series.py:664: SettingWithCopyWarning:
A value is trying to be set on a copy of a slice from a DataFrame
See the the caveats in the documentation: http://pandas.pydata.org/pandas- docs/stable/indexing.html#indexing-view-versus-copy
self.loc[key] = value
任何帮助将非常感激.
解决方法:
def f(x):
#print x
x['Secondname'] = x['Name'].shift(1).combine_first(x['Name'].shift(-1))
return x
print df.groupby('ID').apply(f)
ID Name Rate Secondname
0 1 A 65.5 NaN
1 2 B 67.3 C
2 2 C 78.8 B
3 3 D 65.0 NaN
4 4 E 45.3 NaN
5 5 F 52.0 G
6 5 G 66.0 F
7 6 H 34.0 NaN
8 7 I 2.0 NaN
您可以避免groupby并找到duplicated,然后在loc的帮助器列中填充名称列,然后在shift和combine_first以及最后的drop辅助器列中填充:
print df.duplicated('ID', keep='first')
0 False
1 False
2 True
3 False
4 False
5 False
6 True
7 False
8 False
dtype: bool
print df.duplicated('ID', keep='last')
0 False
1 True
2 False
3 False
4 False
5 True
6 False
7 False
8 False
dtype: bool
df.loc[ df.duplicated('ID', keep='first'), 'first'] = df['Name']
df.loc[ df.duplicated('ID', keep='last'), 'last'] = df['Name']
print df
ID Name Rate first last
0 1 A 65.5 NaN NaN
1 2 B 67.3 NaN B
2 2 C 78.8 C NaN
3 3 D 65.0 NaN NaN
4 4 E 45.3 NaN NaN
5 5 F 52.0 NaN F
6 5 G 66.0 G NaN
7 6 H 34.0 NaN NaN
8 7 I 2.0 NaN NaN
df['SecondName'] = df['first'].shift(-1).combine_first(df['last'].shift(1))
df = df.drop(['first', 'l1'], axis=1)
print df
ID Name Rate SecondName
0 1 A 65.5 NaN
1 2 B 67.3 C
2 2 C 78.8 B
3 3 D 65.0 NaN
4 4 E 45.3 NaN
5 5 F 52.0 G
6 5 G 66.0 F
7 6 H 34.0 NaN
8 7 I 2.0 NaN
测试:(在Roman Kh的测试解决方案中有错误的输出)
len(df)= 9:
In [154]: %timeit jez(df1)
100 loops, best of 3: 15 ms per loop
In [155]: %timeit jez2(df2)
100 loops, best of 3: 3.45 ms per loop
In [156]: %timeit rom(df)
100 loops, best of 3: 3.55 ms per loop
len(df)= 90k:
In [158]: %timeit jez(df1)
10 loops, best of 3: 57.1 ms per loop
In [159]: %timeit jez2(df2)
10 loops, best of 3: 36.4 ms per loop
In [160]: %timeit rom(df)
10 loops, best of 3: 40.4 ms per loop
import pandas as pd
mydict = {'ID':[1,2,2,3,4,5,5,6,7],
'Name':['A','B','C','D','E','F','G','H','I'],
'Rate':[65.5,67.3,78.8,65,45.3,52,66,34,2]}
df=pd.DataFrame(mydict)
print df
df = pd.concat([df]*10000).reset_index(drop=True)
df1 = df.copy()
df2 = df.copy()
def jez(df):
def f(x):
#print x
x['Secondname'] = x['Name'].shift(1).combine_first(x['Name'].shift(-1))
return x
return df.groupby('ID').apply(f)
def jez2(df):
#print df.duplicated('ID', keep='first')
#print df.duplicated('ID', keep='last')
df.loc[ df.duplicated('ID', keep='first'), 'first'] = df['Name']
df.loc[ df.duplicated('ID', keep='last'), 'last'] = df['Name']
#print df
df['SecondName'] = df['first'].shift(-1).combine_first(df['last'].shift(1))
df = df.drop(['first', 'last'], axis=1)
return df
def rom(df):
# cpIDs = True if the next row has the same ID
df['cpIDs'] = df['ID'][:-1] == df['ID'][1:]
# fill in the last row (get rid of NaN)
df.iloc[-1,df.columns.get_loc('cpIDs')] = False
# ShiftName == Name of the next row
df['ShiftName'] = df['Name'].shift(-1)
# fill in SecondName
df.loc[df['cpIDs'], 'SecondName'] = df.loc[df['cpIDs'], 'ShiftName']
# remove columns
del df['cpIDs']
del df['ShiftName']
return df
print jez(df1)
print jez2(df2)
print rom(df)
print jez(df1)
ID Name Rate Secondname
0 1 A 65.5 NaN
1 2 B 67.3 C
2 2 C 78.8 B
3 3 D 65.0 NaN
4 4 E 45.3 NaN
5 5 F 52.0 G
6 5 G 66.0 F
7 6 H 34.0 NaN
8 7 I 2.0 NaN
print jez2(df2)
ID Name Rate SecondName
0 1 A 65.5 NaN
1 2 B 67.3 C
2 2 C 78.8 B
3 3 D 65.0 NaN
4 4 E 45.3 NaN
5 5 F 52.0 G
6 5 G 66.0 F
7 6 H 34.0 NaN
8 7 I 2.0 NaN
print rom(df)
ID Name Rate SecondName
0 1 A 65.5 NaN
1 2 B 67.3 C
2 2 C 78.8 NaN
3 3 D 65.0 NaN
4 4 E 45.3 NaN
5 5 F 52.0 G
6 5 G 66.0 NaN
7 6 H 34.0 NaN
8 7 I 2.0 NaN
编辑:
如果存在更多具有相同名称的重复对,请使用shift创建第一列和最后一列:
df.loc[ df['ID'] == df['ID'].shift(), 'first'] = df['Name']
df.loc[ df['ID'] == df['ID'].shift(-1), 'last'] = df['Name']
标签:pandas,python-3-x,python