Pandas DataFrame moving values from rows to another rows

Question

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?

Naveed · Accepted Answer · 2022-10-11 15:28:24Z

1

here is one way to do it

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

    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

edited Oct 11, 2022 at 15:28

answered Oct 11, 2022 at 14:34

Naveed

11.7k2 gold badges16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ynjxsjmh · Accepted Answer · 2022-10-11 14:34:01Z

0

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

answered Oct 11, 2022 at 14:34

Ynjxsjmh

30.3k7 gold badges43 silver badges64 bronze badges

Comments

R. Baraiya · Accepted Answer · 2022-10-11 14:40:49Z

0

df['new_Col'] = df['MY_ID'].apply(lambda x: ' '.join(map(str,df2.loc[df2['MY_ID'] == x,'UNITS'].values)))

answered Oct 11, 2022 at 14:40

R. Baraiya

1,5281 gold badge6 silver badges20 bronze badges

Comments

SomeDude · Accepted Answer · 2022-10-11 14:43:03Z

0

You can use groupby, transform and ' '.join

df["DESC"].str.cat(
    df.assign(units_str=df["UNITS"].astype(str))
    .groupby("MY_ID")["units_str"]
    .transform(lambda x: " ".join(list(x))),
    sep=" ",
)

edited Oct 11, 2022 at 14:43

answered Oct 11, 2022 at 14:31

SomeDude

14.3k5 gold badges26 silver badges49 bronze badges

Collectives™ on Stack Overflow

Pandas DataFrame moving values from rows to another rows

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related