0

Let's imagine as a starting point I have a dataframe, similar to the one below (using a tuple for the composite columns):

Index, (ATop,AMiddle,ABottom,filt1_1, filt2_1), (ATop,AMiddle,ABottom,filt1_2,filt2_2)
  1,                    Value 1,                                Value 2

I want to reshape my df, such that the last 2 levels of the composite columns become columns in their own right. So:

Index, (ATop,AMiddle,ABottom), Filter1,   Filter2
  1,         Value 1,          filt1_1,   filt2_2,
  1,         Value 2,          filt2_1,   filt2_2,

You can see how the (ATop,AMiddle,ABottom) got extracted into a single column and we ended up with the filter columns to disambiguate the duplicates. The problem I am working on has more than one such groupings, e.g. (BTop,BMiddle,BBottom), etc.

I could appreciate this might sound a little weird, but let's imagine I have a UX grid displayer that wants the data in the latter form and I have my input given as shown above.

Additional info

The input / expected dfs from above:

import pandas as pd

input_df = pd.DataFrame({
    'Index': [1],
    ("ATop", "AMiddle", "ABottom" ,"filt1_1", "filt2_1"): ["Value 1"],
    ("ATop", "AMiddle", "ABottom" ,"filt1_2", "filt2_2"): ["Value 2"]
})

# ... some processing here ...

expected_df = pd.DataFrame({
    'Index': [1, 1],
    ("ATop", "AMiddle", "ABottom"): ["Value 1", "Value 2"],
    "Filter1": ["filt1_1", "filt1_2"],
    "Filter2": ["filt2_1", "filt2_2"]
})

Deeply appreciated!

5
  • This operation reminds me of df.melt, with the key differences: (1) we're dealing with a composite column here and we want to melt just one of its constituents; (2) columns mind end up merging after the operation, as we can see above. Commented Apr 27 at 13:36
  • For uncommon shaped data frames, such as multi-index, you will need to provide your input sample in code so that we can run the code to generate it.
    – Panda Kim
    Commented Apr 27 at 13:39
  • @Panda Kim, done hope that helps! Commented Apr 27 at 13:56
  • I'm not clear on the Filters. What sort of manipulation do you intend to do with them and what rule do you want to apply that determines this? Commented Apr 28 at 8:27
  • @climate-coder to answer your question at a high-level: those are used for filtering rows for display in the UI. Imagine the data is displayed ("ATop", "AMiddle", "ABottom") is a column, under which we will be seeing the values (the 3-tuple defines a column hierarchy, so it's not really relevant here). And we will have something like 2 drop downs for the filters, where we'd be able to choose specific values. Hope it makes sense and as to why the particular data transform is required: imagine it as an implementation detail we have the input data as I have shown above in denormalized form. Commented Apr 28 at 11:11

0

Browse other questions tagged or ask your own question.