Pandas DataFrame moving values from rows to another rows

October 11, 2022

I am trying to figure out if is easy to make the following transformation of a DataFrame:

My current DataFrame is as follows:

  MY_ID UNITS         DESC
0    A1    10  DESCRIPTION
1    A1    15  DESCRIPTION
2    A1     0  DESCRIPTION
3    A1    45  DESCRIPTION
4    A2    20  DESCRIPTION
5    A2    22  DESCRIPTION
6    A2    28  DESCRIPTION
7    A2    30  DESCRIPTION

I have 3 columns (MY_ID, UNITS and DESC) and what I am trying to achieve is to add all unit values of the same id (MY_ID column) at the end of the description of all rows of that id.

The out I am expecting is the next:

  MY_ID UNITS                     DESC
0    A1    10   DESCRIPTION 10 15 0 45
1    A1    15   DESCRIPTION 10 15 0 45
2    A1     0   DESCRIPTION 10 15 0 45
3    A1    45   DESCRIPTION 10 15 0 45
4    A2    20  DESCRIPTION 20 22 28 30
5    A2    22  DESCRIPTION 20 22 28 30
6    A2    28  DESCRIPTION 20 22 28 30
7    A2    30  DESCRIPTION 20 22 28 30

As you can see it is about concatenating at the end of the description the whole units of that id, "10 15 0 45" for A1 and "20 22 28 30" for A2.

Is there any way to achieve this with pandas?

>Solution :

Let’s try

df['DESC'] = df['DESC'] + ' ' + df.astype({'UNITS': str}).groupby('MY_ID')['UNITS'].transform(' '.join)

print(df)

  MY_ID  UNITS                     DESC
0    A1     10   DESCRIPTION 10 15 0 45
1    A1     15   DESCRIPTION 10 15 0 45
2    A1      0   DESCRIPTION 10 15 0 45
3    A1     45   DESCRIPTION 10 15 0 45
4    A2     20  DESCRIPTION 20 22 28 30
5    A2     22  DESCRIPTION 20 22 28 30
6    A2     28  DESCRIPTION 20 22 28 30
7    A2     30  DESCRIPTION 20 22 28 30