How can I add a column that depends on another columns value, but also involves other rows?

Question

As you can see, the TransactionNo's are not unique, in other words not every entry is its own order. So I want to make a column that have a list of all items for a particular TransactionNo when Coffee was bought with that TransactionNo.

For example, on row 7(TransactionNo 5) you see coffee is bought. So I want a column that called 'extras' that puts the other items bought with TransactionNo 5 on it. So under the column 'extras', on row 7, you would see a list ['Pastry', 'Bread']. I've tried using np.where but I cannot figure this out.

TransactionNo   Items
0   1   Bread
1   2   Scandinavian
2   2   Scandinavian
3   3   Hot chocolate
4   3   Jam
5   3   Cookies
6   4   Muffin
7   5   Coffee
8   5   Pastry
9   5   Bread

I tried df['extras'] = np.where(df['Items'] == 'Coffee', x, y) but couldn't figure out what to put for x or y.

Your question needs a minimal reproducible example consisting of sample input, expected output, actual output, and only the relevant code necessary to reproduce the problem. See [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) for best practices related to Pandas questions. — itprorh66, Feb 16 '23 at 00:40

score 0 · Accepted Answer · answered Feb 16 '23 at 01:17

You can group the items except Coffee into list then assign the list to the Coffee

m = df['Items'].eq('Coffee')

dct = df.loc[~m, 'Items'].groupby(df['TransactionNo']).agg(list)

df['extras'] = df['TransactionNo'].map(dct).where(m, '')

$ print(dct)

TransactionNo
1                          [Bread]
2     [Scandinavian, Scandinavian]
3    [Hot chocolate, Jam, Cookies]
4                         [Muffin]
5                  [Pastry, Bread]
Name: Items, dtype: object

$ print(df)

   TransactionNo          Items           extras
0              1          Bread
1              2   Scandinavian
2              2   Scandinavian
3              3  Hot chocolate
4              3            Jam
5              3        Cookies
6              4         Muffin
7              5         Coffee  [Pastry, Bread]
8              5         Pastry
9              5          Bread

How can I add a column that depends on another columns value, but also involves other rows?

1 Answers1