Pandas equivalent of SQL Group By while concatenating columns

Question

Suppose I have table having following columns: firstname, surname, tel

something like this :

firstname    surname    tel
alex         topol      1234
jim          jimix      2312
alex         topol      2344

now I want to find numberof tel per person and sort, so I write this in SQL:

select concat(firstname,' ',surname),count(*) from wp_eqra_requests group by concat(firstname,' ',surname) order by count(*) desc

But do I write this in Python Pandas? I tried using groupby but had no sucess in concatening two columns:

numUsers = df.groupby(by=["firstname", "surname")["tel"].count()

Shubham Sharma · Accepted Answer · 2023-02-05 07:02:10Z

3

Similar to SQL you can use the add operator to concat two columns then groupby and count the values

df.groupby(df['firstname'] + ' ' + df['surname'])['tel'].count()

alex topol    2
jim jimix     1
Name: tel, dtype: int64

answered Feb 5, 2023 at 7:02

Shubham Sharma

71.8k6 gold badges26 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1