How to use if statement with pd.Series object in pandas agg function

Question

I'm trying to aggregate a dataset in which one of the columns contains some URLs. Consider the following dataset

import pandas as pd

df = pd.DataFrame({"ID": [1, 1, 1, 2, 2], 
                   "Website": ["https://www.auctionbid.com",
                               "https://www.google.com",
                               "https://www.awesomeauctions.net",
                               "https://www.awesomeauctions.net",
                               "http://www.auctionnoitcua.com"
                              ]
                 })

I would like to perform the following analysis:

(
df
.groupby("ID")
.agg({"Website": lambda x: 
      "; ".join([site for site in x if x.str.contains("auction")])
    })
)

This results in a ValueError stating that the truth value of a Series is ambiguous. The accepted answer of this question states that if can implicitly convert the operands to bool, and suggests using "bitwise" operators.

My question, then, is how do I implement the equivalent of & and | for if?

`x` is a Series (for each ID you have a different Series in agg). pandas doesn't know whether you want to join if all the items contain that word, or if any of them would suffice. I also don't know how you want to join but if you want to join only the websites which contain the string auction, then instead of a condition on x, just change the comprehension to `site for site in x if 'auction' in site` — ayhan, Feb 22 '17 at 15:39
@ayhan Thank you for this explanation, it definitely helps explain what was going wrong. I was interpreting the procedure as looping through each value of `x` and selecting only those where "auction" was found. I now understand what's actually happening. — tblznbits, Feb 22 '17 at 16:49

pansen · Accepted Answer · 2017-02-22T16:15:07.333

3

You can use pandas builtin pd.Series.str.contains and pd.Series.str.cat methods to explicitly accomplish this:

join_func = lambda x: x[x.str.contains("auction")].str.cat(sep="; ")
df.groupby("ID").agg({"Website": join_func})

edited Feb 22 '17 at 16:15

answered Feb 22 '17 at 15:44

pansen

6,433
4
19
32

This doesn't work as it will result in a series inside the lambda function and not a single value. Pandas will complain. – languitar Feb 22 '17 at 15:47
Right, the join was missing, just added it. – pansen Feb 22 '17 at 15:53
I'm going to accept this answer because I think it's a little bit clearer what the function is doing. – tblznbits Feb 22 '17 at 16:49

score 2 · Answer 2 · answered Feb 22 '17 at 15:42

2

Your comparison cannot work because x is the whole series and not just the item you are processing. This works:

df.groupby("ID")['Website'].agg(lambda x: "; ".join([site for site in x.values if "auction" in site]))

answered Feb 22 '17 at 15:42

languitar

6,554
2
37
62

How to use if statement with pd.Series object in pandas agg function

2 Answers2