1

I have the following Multi-Index table:

A B C D
t_1 t_2 t_1 t_2 t_1 t_2 t_1 t_2
x y x y x y x y x y x y x y x y
2.2 5.1 3.4 1.8 1.5 6.7 8.1 7.5 6.1 2.1 9.3 7.1 8.2 1.1 1.4 2.5
7.9 3.2 1.1 5.3 9.3 3.1 0.9 3.2 4.1 5.1 7.7 4.3 8.1 0.4 2.4 4.1

Data Points (x, y) have been randomly assigned to columns A - D. I want to re-order them by the x-value at t_1 - shown in italics. The other values don't matter for re-ordering, but are carried along to their new column by the x-value at t_1. This means each row will be re-ordered differently.

I want some code which processes the above table to produce:

A B C D
t_1 t_2 t_1 t_2 t_1 t_2 t_1 t_2
x y x y x y x y x y x y x y x y
1.5 6.7 8.1 7.5 2.2 5.1 3.4 1.8 6.1 2.1 9.3 7.1 8.2 1.1 1.4 2.5
4.1 5.1 7.7 4.3 7.9 3.2 1.1 5.3 8.1 0.4 2.4 4.1 9.3 3.1 0.9 3.2
Improve this question
3
  • I didn't get the question. Please give more explanation. how you got that result table. Commented Mar 11, 2021 at 11:09
  • It seems all x (t_1) are in asc order. and y (t_1) are in desc order. Commented Mar 11, 2021 at 11:13
  • @Pygirl I have added some more explanation. Only x(t_1) is used for the sorting, with y(t_1), x(t_2), and y(t_2) values not being relevant, just being 'carried' by the x(t_1) sorting. Commented Mar 11, 2021 at 11:21

2 Answers 2

Reset to default
2

Here's an option involving mostly meddling with the shape of the data, sorting and then using the re-shaped values and original df columns (a MultiIndex) to create the final dataframe:

df2 = df.T.unstack(level=0).T.reset_index(level=0, col_fill='row')
df2 = df2.sort_values([('level_0', 'row'), ('t_1', 'x')], ignore_index=True)
values = df2.drop(('level_0', 'row'), axis=1).values.reshape(2, -1)
df3 = pd.DataFrame(data=values, columns=df.columns)  # using original df's columns

Output:

                    A                   B                   C                   D
        t_1       t_2       t_1       t_2       t_1       t_2       t_1       t_2
     x    y    x    y    x    y    x    y    x    y    x    y    x    y    x    y
0  1.5  6.7  8.1  7.5  2.2  5.1  3.4  1.8  6.1  2.1  9.3  7.1  8.2  1.1  1.4  2.5
1  4.1  5.1  7.7  4.3  7.9  3.2  1.1  5.3  8.1  0.4  2.4  4.1  9.3  3.1  0.9  3.2

In a more readable but inaccurate table format:

A B C D
t_1 t_2 t_1 t_2 t_1 t_2 t_1 t_2
x y x y x y x y x y x y x y x y
1.5 6.7 8.1 7.5 2.2 5.1 3.4 1.8 6.1 2.1 9.3 7.1 8.2 1.1 1.4 2.5
4.1 5.1 7.7 4.3 7.9 3.2 1.1 5.3 8.1 0.4 2.4 4.1 9.3 3.1 0.9 3.2
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I was having hard time finding the correct syntax for sorting them by A,B,C,D blocks row element. +1. It looks good
I will suggest you to use this awesome.tool for showing table:tablesgenerator.com/markdown_tables
@Pygirl Thanks. I generally use df.to_markdown when needed but that doesn't handle MultiIndex columns well, at all. The tool you linked gets closer, but would still need to manually adjust the headers, remove the duplicated header labels, etc. which is not ideal. Which is the same issue in OP's table. Hmm, I may add that in if it helps readability.
2

try with unstack and groupby: (only solution I can think of right now)

df1 = df.unstack().unstack()
for col in df1.columns:
    a = []
    for i,g in df1[col].groupby(level=0):
        a.append((i,g.iloc[0]))
    get_sortedli = sorted(a, key=lambda x: x[1])
    order_col = [f1 for f1,f2 in get_sortedli]
    val = (df.iloc[col].reindex(order_col, axis=1, level=0))
    df.iloc[col] = val

df:

enter image description here

I have imagined this as a 4 block arrangement(A,B,C,D) problem. After arranging get the values and assign it to the real dataframe.

df1:

enter image description here

Improve this answer

1 Comment

@Benjamin wrt your edit of adding val.index = df1[col].index - it's not necessary as you can see, the result is the same without that line. val's index is already that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.