Append cell value in a df depending on a conditiontion

user_9674800 0
Viewed 26

I have a data frame "New_data" structured like this

Index ID Content
0 001 abc
1 001 def
2 002 ghi
3 002 lmn
4 002 opq
5 003 rst
6 001 uvz

As you can see the column "ID" contains multiple IDs that are equal. I would like to create an additional column ('Merged content') that, in each row, contains the values of all the "Content" cells with the same value in the "ID" column. I would like something like this:

Index ID Content Merged content
0 001 abc abc def uvz
1 001 def abc def uvz
2 002 ghi ghi lmn opq
3 002 lmn ghi lmn opq
4 002 opq ghi lmn opq
5 003 rst rst
6 001 uvz abc def uvz

I have tried with the for loops but I was not successful. I have created a new df num with unique values for the ID column to iterate over them and about the problem of duplicated indexes but still, I was not able to solve my problem.

This is the code I have tried to apply:

for i in range(len(num)):
    ID = num.iloc[i,0]
    for j in range(len(new_data)):
        ID_data = new_data.iloc[j,index]
        if ID == ID_data.iloc[j, 0]:
            new_data['Merged Content'] = new_data['Merged Content'].append(new_data['Content'])

I get this error: AttributeError: 'numpy.int64' object has no attribute 'iloc' Can you help me?

dataframe for-loop python
1 Answers

The easiest way I could come up with was by running groupby on the ID, and then merging the two frames again:

import pandas as pd

# df = pd.read_clipboard()

merged_col = df.groupby("ID").agg(merged=pd.NamedAgg("Content", " ".join))
res = df.merge(merged_col, how="left", on="ID")

print(res)

First, I group the original dataframe by "ID", and us " ".join as the aggregation function. This yields:

          merged
ID              
1    abc def uvz
2    ghi lmn opq
3            rst

Then I merge that table with the original on ID:

   Index  ID Content       merged
0      0   1     abc  abc def uvz
1      1   1     def  abc def uvz
2      2   2     ghi  ghi lmn opq
3      3   2     lmn  ghi lmn opq
4      4   2     opq  ghi lmn opq
5      5   3     rst          rst
6      6   1     uvz  abc def uvz

Generally try to avoid running a loop over all records in a dataframe. They tend to be much slower than operations acting on the whole column. The reason is because a loop runs in regular Python, looking at every element, whereas the column operations are typically optimized code using the libraries behind pandas and numpy.

user_66584220
Related