如何添加一个依赖于其他列的值,同时还涉及其他行的列?

huangapple go评论59阅读模式
英文:

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

问题

如您所见,TransactionNo的数值并不唯一,也就是说,并非每个条目都代表自己的订单。因此,我想创建一个列,以列出特定TransactionNo下购买咖啡时购买的所有其他物品。

举个例子,第7行(TransactionNo 5)显示购买了咖啡。因此,我想要创建一个名为'extras'的列,其中列出了与TransactionNo 5一起购买的其他物品。因此,在'extras'列下,第7行会显示一个列表['Pastry', 'Bread']。我尝试使用np.where,但我无法弄清楚应该放什么作为x或y。

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

我尝试了df['extras'] = np.where(df['Items'] == 'Coffee', x, y),但我无法弄清楚x或y应该填什么。

英文:

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.

答案1

得分: 0

你可以将除了Coffee以外的物品分组到一个列表中,然后将该列表分配给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
英文:

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

huangapple
  • 本文由 发表于 2023年2月16日 08:09:11
  • 转载请务必保留本文链接:https://go.coder-hub.com/75466616.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定