What I want is just to add a column that copy the value of tmp
with respect to serial number of c2
and map to c1
.
tmp c1 c2 0 0 0 0 0 0 0 1 0 0 2 0 0 3 0 0 4 0 0 0 1 50 0 2 60 0 3 0 0 4 0 0 0 0 0 0 0 0 0 0 1 0 0 2 0 0 3 0 0 4 0 40 0 1 50 0 2 60 0 3 70 0 4 0 0 0 0 0 0 0 0 0
Expected result:
tmp c1 c2 tmp2 0 0 0 0 0 0 0 0 0 1 0 0 0 2 0 50 0 3 0 60 0 4 0 0 0 0 1 0 50 0 2 0 60 0 3 0 0 0 4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 40 0 2 0 50 0 3 0 60 0 4 0 70 40 0 1 0 50 0 2 0 60 0 3 0 70 0 4 0 0 0 0 0 0 0 0 0 0 0 0 0
The length of c1 sequence and c2 sequence are the same.
Longer sequence for reproduct:
{'tmp': [0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 4342.0, 4352.0, 4258.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 4978.0, 4890.0, 4622.0, 4442.0, 2528.0, 2524.0, 2252.0, 2245.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 2565.0, 2194.0, 2145.0, 2199.0, 2185.0, 2239.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0], 'c1': [0, 0, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 2, 3, 4, 5], 'c2': [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]}
Advertisement
Answer
Use Series.map
with DataFrame.drop_duplicates
, because c2
has duplicates:
df['tmp2'] = df['c1'].map(df.drop_duplicates('c2').set_index('c2')['tmp']) print (df) tmp c1 c2 tmp2 0 0 0 0 0 1 0 0 0 0 2 0 1 0 0 3 0 2 0 50 4 0 3 0 60 5 0 4 0 0 6 0 0 1 0 7 50 0 2 0 8 60 0 3 0 9 0 0 4 0 10 0 0 0 0 11 0 0 0 0 12 0 0 0 0
Details:
print (df.drop_duplicates('c2').set_index('c2')['tmp']) c2 0 0 1 0 2 50 3 60 4 0 Name: tmp, dtype: int64
Solution with merge
:
df = (df[['tmp','c1']].merge(df[['c2','tmp']] .drop_duplicates('c2') .rename(columns={'tmp':'tmp2'}),how='left',left_on='c1',right_on='c2')) print (df) tmp c1 c2 tmp2 0 0 0 0 0 1 0 0 0 0 2 0 1 1 0 3 0 2 2 50 4 0 3 3 60 5 0 4 4 0 6 0 0 0 0 7 50 0 0 0 8 60 0 0 0 9 0 0 0 0 10 0 0 0 0 11 0 0 0 0 12 0 0 0 0
EDIT: If need mapping duplicated sequences add GroupBy.cumcount
for both DataFrames:
df['g1'] = df.groupby('c1').cumcount() df['g2'] = df.groupby('c2').cumcount() df = (df[['tmp','c1', 'g1']].merge(df[['c2','tmp', 'g2']] .drop_duplicates(['c2', 'g2']) .rename(columns={'tmp':'tmp2'}), how='left', left_on=['c1','g1'], right_on=['c2','g2']) .drop(['g1','g2'], axis=1))
print (df) tmp c1 c2 tmp2 0 0 0 0 0 1 0 0 0 0 2 0 1 1 0 3 0 2 2 50 4 0 3 3 60 5 0 4 4 0 6 0 0 0 0 7 50 0 0 0 8 60 0 0 0 9 0 0 0 0 10 0 0 0 0 11 0 0 0 0 12 0 0 0 0 13 0 1 1 40 14 0 2 2 50 15 0 3 3 60 16 0 4 4 70 17 40 0 0 0 18 50 0 0 0 19 60 0 0 0 20 70 0 0 0 21 0 0 0 0 22 0 0 0 0 23 0 0 0 0