How to split a dataframe with Multiindexes into different dataframes?

Question

I have the following script:

 data = {
       'File':[1,1,2,2,3],
       'Sheet':['Sheet 1','Sheet 1','Sheet 3','Sheet3','Sheet5'],
       'Duration':[2,5,3,7,9],
       'Cost':[7,5,8,9,3],
       'Distance':[2,4,5,7,5]
}
 df=pd.DataFrame(data)
 df.columns=[['Set X','Set X','Set Y','Set Y','Set Z'],['File','Sheet','Duration','Cost','Distance']]

I would like to separate this single dataframe into 3 dataframes based on the 'File' type and 'Sheet' type, such that the 3 separate dataframes looks like this:

How should I script with the multiindex? Thank you in advance :)

Note the typo 'Sheet3' vs 'Sheet 3'.

Scott Boston
– Scott Boston

2022-07-07 02:14:37 +00:00
Commented Jul 7, 2022 at 2:14 — Scott Boston
– Scott Boston, Commented Jul 7, 2022 at 2:14

BENY · Accepted Answer · 2022-07-07 02:05:22Z

2

Try with groupby and save the dfs into dict

d = {x : y for x , y in df.groupby(('Set X','File'))}
d[1]
Out[190]: 
  Set X             Set Y         Set Z
   File    Sheet Duration Cost Distance
0     1  Sheet 1        2    7        2
1     1  Sheet 1        5    5        4

answered Jul 7, 2022 at 2:05

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark · Accepted Answer · 2022-07-07 02:06:24Z

If you groupby the tuple of keys, you can iterate through them and pull out each group into dataframes:

data = {
    'File':[1,1,2,2,3],
    'Sheet':['Sheet 1','Sheet 1','Sheet 3','Sheet3','Sheet5'],
    'Duration':[2,5,3,7,9],
    'Cost':[7,5,8,9,3],
    'Distance':[2,4,5,7,5]
}
df = pd.DataFrame(data) 
df.columns=[['Set X','Set X','Set Y','Set Y','Set Z'],['File','Sheet','Duration','Cost','Distance']]

groups = df.groupby(('Set X', 'File'))

df1, df2, df3, = (groups.get_group(g) for g in groups.groups)

This will give expected results like, df1:

    Set X           Set Y              Set Z
    File    Sheet   Duration    Cost   Distance
0   1       Sheet 1 2           7      2
1   1       Sheet 1 5           5      4

Collectives™ on Stack Overflow

How to split a dataframe with Multiindexes into different dataframes?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related