Adding a another level of columns for a pandas data frame

Question

I have a data frame that consists of 60 columns; and for the sake of illustration I will be showing an example data frame that looks like what I have but much shorter. the example data frame looks like this:

       0         1     ...     0         1     ...
0 -0.611064 -0.032586  ... -0.102049  1.582183 ...

what I want is to add another level of columns on top of the existing columns so it becomes something like this:

           A                       B
      0         1     ...     0         1      ... 
0 -0.611064 -0.032586 ...  -0.102049  1.582183 ...

I have tried the following:

df.columns = pd.MultiIndex.from_product([['A','B'], df.columns])

but I got an error that says

ValueError: Length mismatch: Expected axis has 60 elements, new values have 120 elements

after some search I knew that error was due to that the number of columns that I am trying to assign is larger than the existing columns but still I haven't been able so solve this problem.

I also tried several method like pd.MultiIndex.from_tuples and pd.MultiIndex.from_arrays with other error produced.

edit:
here is an reproducible example

import numpy as np
import pandas as pd
df = pd.DataFrame(np.random.randn(1,4), columns=[0,1,0,1])
df.columns = pd.MultiIndex.from_product([['A','B'], df.columns])
print(df)

can any one point to a solution to this problem? thanks in advance.

Please edit your question to include a minimal reproducible example showing samples of your input dataframes and expected output so we can recreate your example. — Paul
– Paul, Commented Oct 26, 2022 at 6:29

AlexK · Accepted Answer · 2022-11-18 02:41:12Z

2

IIUC, you just need to create a repeating sequence of A's and B's, with the length of the sequence equal to the number of your current columns. You can do it using numpy:

import numpy as np

df.columns = pd.MultiIndex.from_arrays(
    [np.repeat(['A','B'], df.shape[1] // 2), df.columns]
)

The first array above will look like ['A','A','B','B',...].

Update:

In response to your follow-up question in the comments below, if you have an odd number of columns, you can utilize np.resize to create a full array of alternating A's and B's (i.e. ['A','A','B','B','A','A',...]) with the length equal to the number of columns in your dataframe. Then, you can pass that array to pd.MultiIndex.from_arrays():

df.columns = pd.MultiIndex.from_arrays(
    [np.resize(np.repeat(['A','B'], 2), df.shape[1]), df.columns]
)

edited Nov 18, 2022 at 2:41

answered Oct 26, 2022 at 6:35

AlexK

3,0119 gold badges19 silver badges29 bronze badges

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

4 Comments

Adel Moustafa Over a year ago

Thank you @AlexK for your answer, I just have another question! How to add this level if the number of columns was odd? I know it will rias an error, I wondering if there is workaround that ?

AlexK Over a year ago

@AdelMoustafa Do you mean there would be for example 61 columns, or do you mean there would be three values, like A, B and C? And what result would you need? Can you provide an example? You can edit your question and include it there.

Adel Moustafa Over a year ago

I mean the number of columns as you said, I imagine I would go about this with a simple if-else to check the number of columns ( if it's even proceed as your solution and if not add a dummy column to make the number of columns even and proceed with your solution). I wondering if there is a better way to deal with this? thanks

AlexK Over a year ago

@AdelMoustafa, please see the update above in response to your follow-up question.

Collectives™ on Stack Overflow

Adding a another level of columns for a pandas data frame

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related