Pandas: I want make a new column based on a series

Question

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.

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]}

@mozway - ya, `InvalidIndexError: Reindexing only valid with uniquely valued Index objects` — jezrael, Sep 23 '22 at 05:20
@jezrael you need to drop duplicates (but I see you added an answer), still a dupe in my opinion ;) — mozway, Sep 23 '22 at 05:21

jezrael · Accepted Answer · 2022-09-23T05:48:21.180

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

Pandas: I want make a new column based on a series

1 Answers1

Linked