Create new column with data that has same column

user_14780015 0
Viewed 103

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]
pandas dataframe python
5 Answers

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
  1. 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]
  1. Get the values at those indices and make them into a list.
user_66603730

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]
user_40015920

May be a little late but this is more concise way and without iterating over objects(for-loops).

With thanks to @Pygirl answer and as an improvement to it:

r = df.groupby('building')['name'].agg(set)
df['in_building_with']= df.apply( lambda x: list(r[x['building']] - {x['name']}) , axis=1)

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]
user_64786450

Let's use boolean indexing with loc inside a list comprehension to get the names from the rows that have same value of building:

df['in_building_with'] = [
    [*df.loc[df['building'].eq(y) & df['name'].ne(x), 'name']] for x, y in df.to_numpy()]

Result:

  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]
user_128331660

Code:

import pandas as pd
# creating an extra column in the data frame
df['in_building_with'] = "";
for index, row in df.iterrows():
   data = []
   # below line will return the index that has the same color as that of row
   buildingList = df[df['building'] == row['building']].index.tolist();
   for i in buildingList:
      # the list also contains the current row index, hence adding a check below
      if(df['name'][i] != row['name']):
          data.append(df['name'][i]);
   df['in_building_with'][index] = data;

print(df)

Result:

  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]
user_118063930
Related