How to split a dataframe with Multiindexes into different dataframes?-CodePudding

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 :)

CodePudding user response：

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

CodePudding user response：

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