Splitting dataframe in smaller dataframe when value in column match and export them to excel fromat (pandas)

Question

I have a dataframe A. I need to divide it in smaller dataframes with matching street names.

Here is the dataframe I have:

id	Location_name	Street_Number	Street_name	Object_needed
1	McDonald's	4	Rue de Hollande	C400/P200
2	Hollande	175	Rue de Hollande	Porte socle simple S20
3	Stade	197	Rue des potiers	CCPI S20 6980138
4	King Jouet	33	Rue des potiers	Coffret CCPI CIBE Mono
5	CARIBBEAN CLEANING	6	Galisbay	Coffret CCPI CIBE Mono

The result I'm trying to have:

Data Frame 1 :

id	Location_name	Street_Number	Street_name	Object_needed
1	McDonald's	4	Rue de Hollande	C400/P200
2	Hollande	175	Rue de Hollande	Porte socle simple S20

Data Frame 2:

id	Location_name	Street_Number	Street_name	Object_needed
3	Stade	197	Rue des potiers	CCPI S20 6980138
4	King Jouet	33	Rue des potiers	Coffret CCPI CIBE Mono

DataFrame 3 :

id	Location_name	Street_Number	Street_name	Object_needed
5	CARIBBEAN CLEANING	6	Galisbay	Coffret CCPI CIBE Mono

The goal is to create as many dataframes as there is corresponding street names.

I'm using a groupby:

grouper = [g[1] for g in df.groupby(['Street_name'])]

Is there a way to name them after the street_name they are classing ? Instead of being name grouper[0] for the first one but Rue_de_Hollande in my example.

Lastly, do you know to export all the dataframe at once to an excel format ?

Answer 1

If you want your streets in seperate excel sheets in one file, you can use the Pandas ExcelWriter:

streets = set(df.Street_name)
with pd.ExcelWriter('all_streets_excel.xlsx', mode='w') as writer:
    for street_name in streets:
        street_df = df[df.loc[:, 'Street_name'] == street_name]
        street_df.to_excel(writer, street_name)