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
.
JavaScript
x
26
26
1
tmp c1 c2
2
0 0 0
3
0 0 0
4
0 1 0
5
0 2 0
6
0 3 0
7
0 4 0
8
0 0 1
9
50 0 2
10
60 0 3
11
0 0 4
12
0 0 0
13
0 0 0
14
0 0 0
15
0 1 0
16
0 2 0
17
0 3 0
18
0 4 0
19
40 0 1
20
50 0 2
21
60 0 3
22
70 0 4
23
0 0 0
24
0 0 0
25
0 0 0
26
Expected result:
JavaScript
1
26
26
1
tmp c1 c2 tmp2
2
0 0 0 0
3
0 0 0 0
4
0 1 0 0
5
0 2 0 50
6
0 3 0 60
7
0 4 0 0
8
0 0 1 0
9
50 0 2 0
10
60 0 3 0
11
0 0 4 0
12
0 0 0 0
13
0 0 0 0
14
0 0 0 0
15
0 1 0 40
16
0 2 0 50
17
0 3 0 60
18
0 4 0 70
19
40 0 1 0
20
50 0 2 0
21
60 0 3 0
22
70 0 4 0
23
0 0 0 0
24
0 0 0 0
25
0 0 0 0
26
The length of c1 sequence and c2 sequence are the same.
Longer sequence for reproduct:
JavaScript
1
601
601
1
{'tmp': [0.0,
2
0.0,
3
0.0,
4
0.0,
5
0.0,
6
0.0,
7
0.0,
8
0.0,
9
0.0,
10
0.0,
11
0.0,
12
0.0,
13
0.0,
14
0.0,
15
0.0,
16
4342.0,
17
4352.0,
18
4258.0,
19
0.0,
20
0.0,
21
0.0,
22
0.0,
23
0.0,
24
0.0,
25
0.0,
26
0.0,
27
0.0,
28
0.0,
29
0.0,
30
0.0,
31
0.0,
32
0.0,
33
0.0,
34
0.0,
35
0.0,
36
0.0,
37
0.0,
38
0.0,
39
0.0,
40
0.0,
41
0.0,
42
0.0,
43
0.0,
44
0.0,
45
0.0,
46
0.0,
47
0.0,
48
0.0,
49
0.0,
50
0.0,
51
0.0,
52
0.0,
53
0.0,
54
0.0,
55
0.0,
56
0.0,
57
0.0,
58
0.0,
59
0.0,
60
0.0,
61
0.0,
62
0.0,
63
0.0,
64
4978.0,
65
4890.0,
66
4622.0,
67
4442.0,
68
2528.0,
69
2524.0,
70
2252.0,
71
2245.0,
72
0.0,
73
0.0,
74
0.0,
75
0.0,
76
0.0,
77
0.0,
78
0.0,
79
0.0,
80
0.0,
81
0.0,
82
0.0,
83
0.0,
84
0.0,
85
0.0,
86
0.0,
87
0.0,
88
0.0,
89
0.0,
90
0.0,
91
0.0,
92
0.0,
93
0.0,
94
0.0,
95
0.0,
96
0.0,
97
0.0,
98
0.0,
99
0.0,
100
0.0,
101
0.0,
102
0.0,
103
0.0,
104
0.0,
105
0.0,
106
0.0,
107
0.0,
108
0.0,
109
0.0,
110
0.0,
111
0.0,
112
0.0,
113
0.0,
114
0.0,
115
0.0,
116
0.0,
117
0.0,
118
0.0,
119
0.0,
120
0.0,
121
0.0,
122
0.0,
123
0.0,
124
0.0,
125
0.0,
126
0.0,
127
0.0,
128
0.0,
129
0.0,
130
0.0,
131
0.0,
132
0.0,
133
0.0,
134
0.0,
135
0.0,
136
0.0,
137
0.0,
138
0.0,
139
0.0,
140
0.0,
141
0.0,
142
0.0,
143
0.0,
144
0.0,
145
0.0,
146
0.0,
147
0.0,
148
0.0,
149
0.0,
150
0.0,
151
0.0,
152
0.0,
153
0.0,
154
0.0,
155
0.0,
156
0.0,
157
0.0,
158
0.0,
159
0.0,
160
2565.0,
161
2194.0,
162
2145.0,
163
2199.0,
164
2185.0,
165
2239.0,
166
0.0,
167
0.0,
168
0.0,
169
0.0,
170
0.0,
171
0.0,
172
0.0,
173
0.0,
174
0.0,
175
0.0,
176
0.0,
177
0.0,
178
0.0,
179
0.0,
180
0.0,
181
0.0,
182
0.0,
183
0.0,
184
0.0,
185
0.0,
186
0.0,
187
0.0,
188
0.0,
189
0.0,
190
0.0,
191
0.0,
192
0.0,
193
0.0,
194
0.0,
195
0.0,
196
0.0,
197
0.0,
198
0.0,
199
0.0,
200
0.0],
201
'c1': [0,
202
0,
203
0,
204
1,
205
2,
206
3,
207
4,
208
5,
209
6,
210
7,
211
8,
212
9,
213
10,
214
0,
215
0,
216
0,
217
0,
218
0,
219
0,
220
0,
221
0,
222
0,
223
0,
224
0,
225
0,
226
0,
227
0,
228
0,
229
0,
230
0,
231
0,
232
0,
233
0,
234
0,
235
0,
236
0,
237
0,
238
0,
239
0,
240
0,
241
0,
242
0,
243
0,
244
0,
245
0,
246
0,
247
0,
248
0,
249
0,
250
0,
251
0,
252
1,
253
2,
254
3,
255
4,
256
5,
257
6,
258
7,
259
8,
260
9,
261
10,
262
0,
263
0,
264
0,
265
0,
266
0,
267
0,
268
0,
269
0,
270
0,
271
0,
272
0,
273
0,
274
0,
275
0,
276
0,
277
0,
278
0,
279
0,
280
0,
281
0,
282
0,
283
0,
284
0,
285
0,
286
0,
287
0,
288
0,
289
0,
290
0,
291
0,
292
0,
293
0,
294
0,
295
0,
296
0,
297
0,
298
0,
299
0,
300
1,
301
2,
302
3,
303
4,
304
5,
305
6,
306
7,
307
8,
308
9,
309
10,
310
0,
311
0,
312
0,
313
0,
314
0,
315
0,
316
0,
317
0,
318
0,
319
0,
320
0,
321
0,
322
0,
323
0,
324
0,
325
0,
326
0,
327
0,
328
0,
329
0,
330
0,
331
0,
332
0,
333
0,
334
0,
335
0,
336
0,
337
0,
338
0,
339
0,
340
0,
341
0,
342
0,
343
0,
344
0,
345
0,
346
0,
347
0,
348
1,
349
2,
350
3,
351
4,
352
5,
353
6,
354
7,
355
8,
356
9,
357
10,
358
0,
359
0,
360
0,
361
0,
362
0,
363
0,
364
0,
365
0,
366
0,
367
0,
368
0,
369
0,
370
0,
371
0,
372
0,
373
0,
374
0,
375
0,
376
0,
377
0,
378
0,
379
0,
380
0,
381
0,
382
0,
383
0,
384
0,
385
0,
386
0,
387
0,
388
0,
389
0,
390
0,
391
0,
392
0,
393
0,
394
0,
395
0,
396
1,
397
2,
398
3,
399
4,
400
5],
401
'c2': [0,
402
0,
403
0,
404
0,
405
0,
406
0,
407
0,
408
0,
409
0,
410
0,
411
0,
412
0,
413
0,
414
1,
415
2,
416
3,
417
4,
418
5,
419
6,
420
7,
421
8,
422
9,
423
10,
424
0,
425
0,
426
0,
427
0,
428
0,
429
0,
430
0,
431
0,
432
0,
433
0,
434
0,
435
0,
436
0,
437
0,
438
0,
439
0,
440
0,
441
0,
442
0,
443
0,
444
0,
445
0,
446
0,
447
0,
448
0,
449
0,
450
0,
451
0,
452
0,
453
0,
454
0,
455
0,
456
0,
457
0,
458
0,
459
0,
460
0,
461
0,
462
1,
463
2,
464
3,
465
4,
466
5,
467
6,
468
7,
469
8,
470
9,
471
10,
472
0,
473
0,
474
0,
475
0,
476
0,
477
0,
478
0,
479
0,
480
0,
481
0,
482
0,
483
0,
484
0,
485
0,
486
0,
487
0,
488
0,
489
0,
490
0,
491
0,
492
0,
493
0,
494
0,
495
0,
496
0,
497
0,
498
0,
499
0,
500
0,
501
0,
502
0,
503
0,
504
0,
505
0,
506
0,
507
0,
508
0,
509
0,
510
1,
511
2,
512
3,
513
4,
514
5,
515
6,
516
7,
517
8,
518
9,
519
10,
520
0,
521
0,
522
0,
523
0,
524
0,
525
0,
526
0,
527
0,
528
0,
529
0,
530
0,
531
0,
532
0,
533
0,
534
0,
535
0,
536
0,
537
0,
538
0,
539
0,
540
0,
541
0,
542
0,
543
0,
544
0,
545
0,
546
0,
547
0,
548
0,
549
0,
550
0,
551
0,
552
0,
553
0,
554
0,
555
0,
556
0,
557
0,
558
1,
559
2,
560
3,
561
4,
562
5,
563
6,
564
7,
565
8,
566
9,
567
10,
568
0,
569
0,
570
0,
571
0,
572
0,
573
0,
574
0,
575
0,
576
0,
577
0,
578
0,
579
0,
580
0,
581
0,
582
0,
583
0,
584
0,
585
0,
586
0,
587
0,
588
0,
589
0,
590
0,
591
0,
592
0,
593
0,
594
0,
595
0,
596
0,
597
0,
598
0,
599
0,
600
0]}
601
Advertisement
Answer
Use Series.map
with DataFrame.drop_duplicates
, because c2
has duplicates:
JavaScript
1
17
17
1
df['tmp2'] = df['c1'].map(df.drop_duplicates('c2').set_index('c2')['tmp'])
2
print (df)
3
tmp c1 c2 tmp2
4
0 0 0 0 0
5
1 0 0 0 0
6
2 0 1 0 0
7
3 0 2 0 50
8
4 0 3 0 60
9
5 0 4 0 0
10
6 0 0 1 0
11
7 50 0 2 0
12
8 60 0 3 0
13
9 0 0 4 0
14
10 0 0 0 0
15
11 0 0 0 0
16
12 0 0 0 0
17
Details:
JavaScript
1
9
1
print (df.drop_duplicates('c2').set_index('c2')['tmp'])
2
c2
3
0 0
4
1 0
5
2 50
6
3 60
7
4 0
8
Name: tmp, dtype: int64
9
Solution with merge
:
JavaScript
1
20
20
1
df = (df[['tmp','c1']].merge(df[['c2','tmp']]
2
.drop_duplicates('c2')
3
.rename(columns={'tmp':'tmp2'}),how='left',left_on='c1',right_on='c2'))
4
5
print (df)
6
tmp c1 c2 tmp2
7
0 0 0 0 0
8
1 0 0 0 0
9
2 0 1 1 0
10
3 0 2 2 50
11
4 0 3 3 60
12
5 0 4 4 0
13
6 0 0 0 0
14
7 50 0 0 0
15
8 60 0 0 0
16
9 0 0 0 0
17
10 0 0 0 0
18
11 0 0 0 0
19
12 0 0 0 0
20
EDIT: If need mapping duplicated sequences add GroupBy.cumcount
for both DataFrames:
JavaScript
1
11
11
1
df['g1'] = df.groupby('c1').cumcount()
2
df['g2'] = df.groupby('c2').cumcount()
3
4
df = (df[['tmp','c1', 'g1']].merge(df[['c2','tmp', 'g2']]
5
.drop_duplicates(['c2', 'g2'])
6
.rename(columns={'tmp':'tmp2'}),
7
how='left',
8
left_on=['c1','g1'],
9
right_on=['c2','g2'])
10
.drop(['g1','g2'], axis=1))
11
JavaScript
1
28
28
1
print (df)
2
3
tmp c1 c2 tmp2
4
0 0 0 0 0
5
1 0 0 0 0
6
2 0 1 1 0
7
3 0 2 2 50
8
4 0 3 3 60
9
5 0 4 4 0
10
6 0 0 0 0
11
7 50 0 0 0
12
8 60 0 0 0
13
9 0 0 0 0
14
10 0 0 0 0
15
11 0 0 0 0
16
12 0 0 0 0
17
13 0 1 1 40
18
14 0 2 2 50
19
15 0 3 3 60
20
16 0 4 4 70
21
17 40 0 0 0
22
18 50 0 0 0
23
19 60 0 0 0
24
20 70 0 0 0
25
21 0 0 0 0
26
22 0 0 0 0
27
23 0 0 0 0
28