Pandas concatenate Multiindex columns with same row index

Question

I want to concatenate the multidimensional output of a NumPy computation matching in dimensions the shape of the input (with regards to rows and respective selected columns). But it fails with: NotImplementedError: Can only union MultiIndex with MultiIndex or Index of tuples, try mi.to_flat_index().union(other) instead.

I do not want to flatten the indices first - so is there another way to get it to work?

import pandas as pd
from pandas import Timestamp

df = pd.DataFrame({('metrik_0', Timestamp('2020-01-01 00:00:00')): {(1, 1): 2.5393693602911447, (1, 5): 4.316896324314225, (1, 6): 4.271001191238499, (1, 9): 2.8712588011247377, (1, 11): 4.0458495954752545}, ('metrik_0', Timestamp('2020-01-01 01:00:00')): {(1, 1): 4.02779063729038, (1, 5): 3.3849606155101224, (1, 6): 4.284114856052976, (1, 9): 3.980919941298365, (1, 11): 5.042488191587525}, ('metrik_0', Timestamp('2020-01-01 02:00:00')): {(1, 1): 2.374592085569529, (1, 5): 3.3405503781564487, (1, 6): 3.4049690284720366, (1, 9): 3.892686173978996, (1, 11): 2.1876998087043127}})

def compute_return_columns_to_df(df, colums_to_process,axis=0):
    method = 'compute_result'
    renamed_base_levels = map(lambda x: f'{x}_{method}', colums_to_process.get_level_values(0).unique())
    renamed_columns = colums_to_process.set_levels(renamed_base_levels, level=0)

    #####
    # perform calculation in numpy here
    # for the sake of simplicity (and as the actual computation is irrelevant - it is omitted in this minimal example)
    result = df[colums_to_process].values
    #####
    
    result = pd.DataFrame(result, columns=renamed_columns)
    display(result)    
    return pd.concat([df, result], axis=1) # fails with: NotImplementedError: Can only union MultiIndex with MultiIndex or Index of tuples, try mi.to_flat_index().union(other) instead.

# I do not want to flatten the indices first - so is there another way to get it to work?

compute_return_columns_to_df(df[df.columns[0:3]].head(), df.columns[0:2])

Pretty similar to stackoverflow.com/questions/49277682/… I just want to add it as columns. It would certainly be possible to iterate all the columns and add it - but I would prefer to add ll the columns at once to the source data frame. — Georg Heiler
– Georg Heiler, Commented Nov 11, 2020 at 4:58
I.e. for col in result.columns: df[col] = result[col] return df certainly works. But requires to iterate. — Georg Heiler
– Georg Heiler, Commented Nov 11, 2020 at 8:13

Valdi_Bo · Accepted Answer · 2020-11-11 08:55:43Z

1

The reason why your code failed is in:

result = df[colums_to_process].values
result = pd.DataFrame(result, columns=renamed_columns)

Note that result has:

column names with the top index level renamed to metrik_0_compute_result (so far OK),
but the row index is the default single level index, composed of consecutive numbers.

Then, when you concatenate df and result, Pandas attempts to align both source DataFrames on the row index, but they are incompatible (df has a MultiIndex, whereas result has an "ordinary" index).

Change this part of your code to:

result = df[colums_to_process]
result.columns = renamed_columns

This way result keeps the original index and concat raises no exception.

Another remark: Your function contains axis parameter, which is never used. Consider removing it.

Another possible approach

Since result has a default (single level) index, you can leave the previous part of code as is, but reset the index in df before joining:

return pd.concat([df.reset_index(drop=True), result], axis=1)

This way both DataFrames have the same indices and you can concatenate them as well.

edited Nov 11, 2020 at 8:55

answered Nov 11, 2020 at 8:26

Valdi_Bo

31.1k4 gold badges29 silver badges45 bronze badges

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

1 Comment

Georg Heiler Over a year ago

This does not work - as I said the operation is working on numpy (= implicitly the original index of pandas is deleted). So you are right - but unfortunately, the outline solution only works here in the simple pandas based dummy example.

Collectives™ on Stack Overflow

Pandas concatenate Multiindex columns with same row index

1 Answer 1

Another possible approach

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Another possible approach

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related