Pandas DataFrame moving values from rows to another rows-CodePudding

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?

CodePudding user response：

You can use groupby, transform and ' '.join

df['DESC'] = df['DESC']   df.groupby('MY_ID')['UNITS'].transform(lambda x: ' '.join(list(x)))

CodePudding user response：

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

CodePudding user response：

here is one way to do it

# using groupby and transform, combine the values with a space inbetween
df['joined']=df.groupby('MY_ID')['UNITS'].transform(lambda x: ' '.join(x.astype(str)))
df

MY_ID   UNITS   DESC    joined
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