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