Question: I have 2 tables in sql server:
Employee (Job Title, Hire Date)

* Person (FirstName, LastName)
And I need to return the E.JobTitle, E.HireDate,P.FirstName ,P.LastName along with the how many employees have the same job title.

so I used this query:

```sql
SELECT E.JobTitle,
       E.HireDate,
       P.FirstName,
       P.LastName,
       COUNT(E.JobTitle)
FROM AdventureWorks2019.HumanResources.Employee E
     JOIN AdventureWorks2019.Person.Person P ON E.BusinessEntityID = P.BusinessEntityID
GROUP BY E.JobTitle,
         E.HireDate,
         P.FirstName,
         P.LastName;

```

the problem is that the query returns 1 per the count, while I'd expect to get per each row the count of the num employees with that job title.
my question is how can I get the correct count?

Answer: 
Assuming "3, 2, 1" is a list, you can do this with :

```
df.A.apply(lambda x: x[1])

```

if this is a string, you can do this with :

```
df.A.apply(lambda x: x.split(", ")[1])

```
**If column A contain string values:**

```
import pandas as pd

data = {
    "A" :["3, 2, 1","3, 2, 1", "3, 2, 1"],
    "B" : ["5, 6, 7", "5, 6, 7", "5, 6, 7"]
}
df = pd.DataFrame(data)
output = df["A"].apply(lambda x:  (x.split(",")[1]).strip()).to_list()

print(output)

```

**Result:**

```
['2', '2', '2']

```