How to repeat a pandas Serie in a column

Question

I have two data frames, the first has the following structure:

month| u_id | other columns
1      1
1      2
2      1
2      2
3      1
3      2

The 2nd data frame has only 2 rows which are the name associated with u_id = 1 and u_id = 2:

id name
0  u1
1  u2

I would like to add a new column in the 1st dataframe to contain the names so the result would be:

month| u_id | u_name
1      1      u1
1      2      u2
2      1      u1 
2      2      u2
3      1      u1   
3      2      u2

I tried pandas concat function but I did not get the result I want, is there any function to achieve this ?

How are they exactly associated? Through the `id` or the `name` column? — yatu, Oct 06 '20 at 21:14

score 0 · Answer 1 · answered Oct 06 '20 at 21:13

One way:

 df.merge(df2.assign(u_id = df2['id'] + 1)).sort_values('Month')

Output:

   Month  u_id  id name
0      1     1   0   u1
3      1     2   1   u2
1      2     1   0   u1
4      2     2   1   u2
2      3     1   0   u1
5      3     2   1   u2

Another way:

df['name'] = (df['u_id'] - 1).map(df2.set_index('id')['name'])

Output:

   Month  u_id name
0      1     1   u1
1      1     2   u2
2      2     1   u1
3      2     2   u2
4      3     1   u1
5      3     2   u2

score 0 · Answer 2 · answered Oct 06 '20 at 21:18

If I understand you correctly, you want to merge by the number in name column:

out = pd.merge(df1, 
    df2.assign(tmp=df2['name'].str.extract(r'(\d+)').astype(int)), 
    left_on='u_id', 
    right_on='tmp', 
    how='left')[['month', 'u_id', 'name']]

print(out)

Prints:

   month  u_id name
0      1     1   u1
1      1     2   u2
2      2     1   u1
3      2     2   u2
4      3     1   u1
5      3     2   u2

How to repeat a pandas Serie in a column

2 Answers2