Create new column with data that has same column

Question

I have DataFrame similat to this. How to add new column with names of rows that have same value in one of the column? For example:

Have this:

  name  building 
  a     blue
  b     white
  c     blue
  d     red
  e     blue
  f     red

How to get this?

  name  building  in_building_with
  a     blue      [c, e]
  b     white     []
  c     blue      [a, e]
  d     red       [f]
  e     blue      [a, c]
  f     red       [d]

Answer 1

This is approach(worst) I can only think of :

r = df.groupby('building')['name'].agg(dict)
df['in_building_with'] = df.apply(lambda  x: [r[x['building']][i] for i in (r[x['building']].keys()-[x.name])], axis=1)

---

df:

name    building    in_building_with
0   a   blue    [c, e]
1   b   white   []
2   c   blue    [a, e]
3   d   red     [f]
4   e   blue    [a, c]
5   f   red     [d]

---

Approach:

1. Make a dictionary which will give your indices where the building occurs.

---

building
blue     {0: 'a', 2: 'c', 4: 'e'}
red              {3: 'd', 5: 'f'}
white                    {1: 'b'}
dtype: object

---

2. subtract the index of the current building from the list since you are looking at the element other than it to get the indices of appearance.

---

r[x['building']].keys()-[x.name]

---

3. Get the values at those indices and make them into a list.

Answer 2

If order is not important, you could do:

# create groups
groups = df.groupby('building').transform(dict.fromkeys).squeeze()

# remove value from each group
df['in_building_with'] = [list(group.keys() - (e,)) for e, group in zip(df['name'], groups)]

print(df)

Output

  name building in_building_with
0    a     blue           [e, c]
1    b    white               []
2    c     blue           [e, a]
3    d      red              [f]
4    e     blue           [a, c]
5    f      red              [d]