Compare and update pandas dataframe column lists with strings from another column

Question

I have a pandas data frame:

+--------+---------+------+----------------+
|  Name  | Address |  ID  |   Linked_To    |
+--------+---------+------+----------------+
| Name A | ABC     | 1233 | 1234;1235      |
| Name B | DEF     | 1234 | 1233;1236;1237 |
| Name C | GHI     | 1235 | 1234;1233;2589 |
+--------+---------+------+----------------+

Some of the ID's in the Linked_To column are records, under the Name column. I can create a dictionary and pass the data in the Linked_To column as a list. However, I am unsure how to proceed. Ideally I would like to see something like:

+--------+---------+------+-------------------------+
|  Name  | Address |  Id  |        Linked To        |
+--------+---------+------+-------------------------+
| Name A | ABC     | 1233 | Name B;Name C           |
| Name B | DEF     | 1234 | Name A;Name D; Name E   |
| Name C | HIJ     | 1235 | Name B;Name A; None     |
+--------+---------+------+-------------------------+

How does your Name 1 and Name A relate? Are these two dataframes related? — Scott Boston
– Scott Boston, Commented Dec 8, 2017 at 5:32
@ScottBoston Edited the tables for clarity. Name A = Name 1. I've edited the above accordingly. — Owais Arshad
– Owais Arshad, Commented Dec 8, 2017 at 16:56

Brad Solomon · Accepted Answer · 2017-12-08 18:20:53Z

1

It seems tough to do this without some looping:

linked = df.Linked_To.str.split(';')

def pull_name(iden):
    try:
        return df[df.ID == int(iden)].Name.iat[0]
    except:
        return str(None)

res = linked.apply(lambda ids: '; '.join([pull_name(i) for i in ids]))

print(res)
0         Name B; Name C
1     Name A; None; None
2    Name B; Name A; ...
Name: Linked_To, dtype: object

answered Dec 8, 2017 at 18:20

Brad Solomon

41.2k39 gold badges167 silver badges260 bronze badges

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

Collectives™ on Stack Overflow

Compare and update pandas dataframe column lists with strings from another column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related