Pandas: Split df into multiple dfs based on multiple columns

Question

I have a df that I want to split into several dfs based on the values in columns 'Name' and 'Plan'. for below df, I am looking to split into 6 dfs where rows 1 & 6 will be in the same df

df:

City    State       Name     Plan   Price
 A        CA     Star Inn     CTS    50
 B        CA      1 Inn       KVG    100
 C        IN     GS Hotel     KHA    25
 D        FL     HJ Resort    2QN    45
 E        AL     PQ Inn       POI    55
 A        CA     Star Inn     CTS    80
 A        CA     Star Inn     MNB    65

desired outputs

df1:

City    State       Name     Plan   Price
 A        CA     Star Inn     CTS    50
 A        CA     Star Inn     CTS    80

df2:

City    State       Name     Plan   Price
 B        CA      1 Inn       KVG    100

and so on until df6...

Andrej Kesely · Accepted Answer · 2021-03-30 22:36:19Z

This exampel will split the dataframe by Name and Plan and print them:

dataframes = []
for _, d in df.groupby(["Name", "Plan"]):
    dataframes.append(d)

# print it:
for d in dataframes:
    print(d)
    print("-" * 80)

Prints:

  City State   Name Plan  Price
1    B    CA  1_Inn  KVG    100
--------------------------------------------------------------------------------
  City State      Name Plan  Price
2    C    IN  GS_Hotel  KHA     25
--------------------------------------------------------------------------------
  City State       Name Plan  Price
3    D    FL  HJ_Resort  2QN     45
--------------------------------------------------------------------------------
  City State    Name Plan  Price
4    E    AL  PQ_Inn  POI     55
--------------------------------------------------------------------------------
  City State      Name Plan  Price
0    A    CA  Star_Inn  CTS     50
5    A    CA  Star_Inn  CTS     80
--------------------------------------------------------------------------------
  City State      Name Plan  Price
6    A    CA  Star_Inn  MNB     65
--------------------------------------------------------------------------------

Cheukting · Accepted Answer · 2021-03-30 23:00:36Z

1

using group_by in pandas you will get a Grouper object:

grouped = df.groupby(["Name","Plan"])

Which when you iterated through, it will give you a tuple where the 1st element is the groups (in this case, ("Name","Plan")) and the 2nd element, the split dfs:

grouped = df.groupby(["Name","Plan"])
for _, split_df in grouped:
    print(split_df)
    print("-----")

Will give you:

  City State   Name Plan  Price
1    B    CA  1 Inn  KVG    100
-----
  City State      Name Plan  Price
2    C    IN  GS Hotel  KHA     25
-----
  City State       Name Plan  Price
3    D    FL  HJ Resort  2QN     45
-----
  City State    Name Plan  Price
4    E    AL  PQ Inn  POI     55
-----
  City State      Name Plan  Price
0    A    CA  Star Inn  CTS     50
5    A    CA  Star Inn  CTS     80
-----
  City State      Name Plan  Price
6    A    CA  Star Inn  MNB     65
-----

answered Mar 30, 2021 at 23:00

Cheukting

2641 silver badge6 bronze badges

2 Comments

TheCluelessCoder Over a year ago

is there a way to store these dfs separately?

Cheukting Over a year ago

yes, you can append them (the split_df) to a list in the for loop or assign them to different variables if you like.

Collectives™ on Stack Overflow

Pandas: Split df into multiple dfs based on multiple columns

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related