CODEWITHSUNDEEP
pythonpandas
thesydne
thesydne

Reputation: 127

Pandas DataFrame moving values from rows to another rows

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?

Upvotes: 0

Views: 45

Answers (4)

Naveed
Naveed

Reputation: 11650

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

Upvotes: 1

SomeDude
SomeDude

Reputation: 14238

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=" ",
)

Upvotes: 0

R. Baraiya
R. Baraiya

Reputation: 1530

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

Upvotes: 0

Ynjxsjmh
Ynjxsjmh

Reputation: 30032

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

Upvotes: 0

Related Questions