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!
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.("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.