Sort data into multiindex dataframe

Question

How do I sort data from a DataFrame into a DataFrame that uses a MultiIndex for both the indices and columns?

For example, convert from this:

     0    1  2     3     4
0  foo  two  A  2.30  0.01
1  foo  one  A  4.12  0.13
2  bar  two  B  9.89  3.66
3  foo  one  A  2.11  9.48
4  bar  two  A  1.07  5.55

to this:

            A           B      
            1     2     1     2
foo one  2.11  9.48   NaN   NaN
    two   2.3  0.01   NaN   NaN
bar one   NaN   NaN   NaN   NaN
    two  1.07  5.55  9.89  3.66

Currently I am iterating over each row in df1 and updating the values in df2, but I'd like a more efficient method than this:

for index, row in df1.iterrows():
    df2.loc[(row[0], row[1]), row[2]] = list(row[3:])

jezrael · Accepted Answer · 2018-04-27 06:44:16Z

2

You can use:

def f(x):
    return pd.DataFrame({'a':x.values.ravel()}).rename(lambda x: x + 1)

df = df.groupby([0,1,2])[3,4].apply(f)['a'].unstack([2,3]).sort_index(level=0, axis=1)
df = df.rename_axis((None, None),axis=1).reindex(pd.MultiIndex.from_product(df.index.levels))
print (df)
            A                       B      
            1     2     3     4     1     2
bar one   NaN   NaN   NaN   NaN   NaN   NaN
    two  1.07  5.55   NaN   NaN  9.89  3.66
foo one  4.12  0.13  2.11  9.48   NaN   NaN
    two  2.30  0.01   NaN   NaN   NaN   NaN

Explanation:

For each group by first 3 columns apply custom function with DataFrame, also increment index value for start from 1
Reshape by unstack and sort Multiindex in columns by sort_index
Remove columns names (2 in left corner) and add missing catagories to MultiIndex in index by reindex and MultiIndex.from_product

edited Apr 27, 2018 at 6:44

answered Apr 27, 2018 at 6:35

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

tog Over a year ago

Could you briefly explain how this works to speed up performance?

jezrael Over a year ago

@user2970608 - Not sure if understand, last explanation is not enough?

tog Over a year ago

I mean why this is faster

jezrael Over a year ago

@user2970608 - Because iterrows is obviously very rare used, because very slow - check this.

Collectives™ on Stack Overflow

Sort data into multiindex dataframe

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related