Merging 2 pandas dataframes on key with duplicates but do not want it to result in two lines

Question

I am trying to merge a "roster" where there are supposed to be several positions with one person per office. However, I have come across two instances where there are two people in one position for one office. I am storing this information into a preset "key" for what the offices are... so when there are two individuals for one position in one office it just duplicates the information except for where there was the NAME1/NAME2. The first line will use NAME1 and the second line will use NAME2.

For example:

key1  ...    NAME1
key1  ...    NAME2
key2  ...    NAME3
key3  ...    NAME4

I'd like to make this end up as:

key1  ...    NAME1/NAME2
key2  ...    NAME3
key3  ...    NAME4

So that the duplicates are merged by adding a / ... This should be as simple as string concatenation. But I am struggling without making a bunch of loops to do this.

Answer 1

Without a reproducible exmple is difficult but I would go with something like this:

df = pd.DataFrame({"key_column":["key1","key1","key2","key3"],"name_column":["NAME1","NAME2","NAME3","NAME4"]})

df.groupby("key_column").name_column.apply(lambda x: "/".join(list(x)))

Result:

key_column
key1    NAME1/NAME2
key2          NAME3
key3          NAME4
Name: name_column, dtype: object