Loop around and select rows by a subset of the multi-index

3021 views python
4

I have a data frame with multiple indexes and I want to loop around this data frame pulling out groups of rows for processing.

I want to loop over all of the combinations in the multi-index but for a subset of the index, not all of them. I have no knowledge before hand what the key/index values will be, but I do know how many there are.

For example:

                  data1
key1  key2  key3
A     A     A      10
A     A     B      11
A     B     A      12
A     B     C      13
A     C     A      14

Assume I am only interested in key1 + key2.

There are 3 unique combinations of key1 + key2:

(A A)
(A B)
(A C)

First time around the loop I would want to extract:

                  data1
key1  key2  key3
A     A     A      10
A     A     B      11

Second time around the loop I would want to extract:

                  data1
key1  key2  key3
A     B     A      12
A     B     C      13

Third time around the loop I would want to extract:

                  data1
key1  key2  key3
A     C     A      14

How do I do this? I am a COMPLETE newbie at python so the more explanation the better.

Thanks

answered question

1 Answer

8

You need to think about how you are going to store your dataframes. I would recommend a dictionary. In order to populate your dictionary, you can use groupby, with the level argument set to your keys of interest.

keys = ['key1','key2']

dfs = {f'df{i}': data for i, (g,data) in enumerate(df.groupby(level=keys))}

Here, you have grouped by key1 and key2, and then, you are creating a dictionary that holds a dataframe for each combination of those keys. They will be labeled df0, df1, etc... You can see all of the dataframes you created using:

>>> dfs.keys()
dict_keys(['df0', 'df1', 'df2'])

And you can access them as you would any normal dictionary values:

>>> dfs['df0']
                data1
key1 key2 key3       
A    A    A        10
          B        11

>>> dfs['df1']
                data1
key1 key2 key3       
A    B    A        12
          C        13

....

posted this

Have an answer?

JD

Please login first before posting an answer.