10
$\begingroup$

I have a pandas DataFrame which has the following columns:

n_0
n_1
p_0
p_1
e_0
e_1

I want to transform it to have columns and sub-columns:

0
    n
    p
    e
1
    n
    p
    e

I've searched in the documentation, and I'm completely lost on how to implement this. Does anyone have any suggestions?

$\endgroup$
2
$\begingroup$
columns=[('0', 'n'), ('0', 'p'), ('0', 'e'), ('1', 'n'), ('1', 'p'), ('1', 'e')]

df.columns = pd.MultiIndex.from_tuples(columns)
$\endgroup$
6
  • 1
    $\begingroup$ This does nothing to transform the existing data frame. $\endgroup$
    – Stephen Rauch
    Nov 10 '19 at 16:57
  • $\begingroup$ @StephenRauch It adds multi-level columns to existing dataframe. Isn't that what you want? $\endgroup$
    – Leopold
    Nov 11 '19 at 1:37
  • $\begingroup$ The question was about transforming an existing dataframe. $\endgroup$
    – Stephen Rauch
    Nov 11 '19 at 2:09
  • $\begingroup$ Yes, the df in df.columns is your existing dataframe. Try it. $\endgroup$
    – Leopold
    Nov 11 '19 at 3:54
  • 1
    $\begingroup$ Um, I do not need to try it, as I am very familiar with pandas. Your code is a hard coded solution. It make NO attempt to look at the existing dataframe. If the column names are different your code DOES NOT work. $\endgroup$
    – Stephen Rauch
    Nov 11 '19 at 4:38
1
$\begingroup$

Finally, I found a solution.

You can find the example script below.

#!/usr/bin/env python3
import pickle
import pandas as pd
import itertools
import numpy as np

data = pd.DataFrame(np.random.randn(10, 5), columns=('0_n', '1_n', '0_p', '1_p', 'x'))

indices = set()
groups = set()
others = set()
for c in data.columns:
    if '_' in c:
        (i, g) = c.split('_')
        c2 = pd.MultiIndex.from_tuples((i, g),)
        indices.add(int(i))
        groups.add(g)
    else:
        others.add(c)
columns = list(itertools.product(groups, indices))
columns = pd.MultiIndex.from_tuples(columns)
ret = pd.DataFrame(columns=columns)
for c in columns:
    ret[c] = data['%d_%s' % (int(c[1]), c[0])]
for c in others:
    ret[c] = data['%s' % c]
ret.rename(columns={'total': 'total_indices'}, inplace=True)

print("Before:")
print(data)
print("")
print("After:")
print(ret)

Sorry for this...

$\endgroup$
1
$\begingroup$

I had to adjust victor's sort to get OP's specific column format:

df = df.sort_index(level=0, axis=1)
0                                     1
    e           n           p           e           n           p
0   -0.995452   -3.237846   1.298927    -0.269253   -0.857724   -0.461103```
$\endgroup$
-2
$\begingroup$

There is a simpler solution:

  data.columns = data.columns.str.split('_', expand=True)

To arrange column names one can also do:

 data.sort_index(axis=1, inplace=True)

To change column levels:

 data = data.reorder_levels([1,0], axis=1)
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.