Pandas equivalent of SQL Group By while concatenating columns-CodePudding

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()

CodePudding user response：

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