# 3198. Find Cities in Each State ðŸ”’

## Description

Table: cities

+-++
\| state       \| varchar \|
\| city        \| varchar \|
+-++
\| state       \| city          \|
+-++


Output:

+-++
\| California  \| Los Angeles, San Diego, San Francisco \|
\| New York    \| Buffalo, New York City, Rochester     \|
\| Texas       \| Austin, Dallas, Houston               \|
+-+---+


Explanation:

• California: All cities ("Los Angeles", "San Diego", "San Francisco") are listed in a comma-separated string.
• New York: All cities ("Buffalo", "New York City", "Rochester") are listed in a comma-separated string.
• Texas: All cities ("Austin", "Dallas", "Houston") are listed in a comma-separated string.

Note: The output table is ordered by the state name in ascending order.

</div>

## Solutions

### Solution 1: Grouping and Aggregation

We can first group by the state field, then sort the city field within each group, and finally use the GROUP_CONCAT function to concatenate the sorted city names into a comma-separated string.

• import pandas as pd

def find_cities(cities: pd.DataFrame) -> pd.DataFrame:
result = (
cities.groupby("state")["city"]
.apply(lambda x: ", ".join(sorted(x)))
.reset_index()
)
result.columns = ["state", "cities"]
return result


• # Write your MySQL query statement below
SELECT
state,
GROUP_CONCAT(city ORDER BY city SEPARATOR ', ') cities
FROM cities
GROUP BY 1
ORDER BY 1;