Pandas: split the index of a dafaframe out of a nested dictionary and add a number to the new split-CodePudding

Let's say I have this small nested dictionary:

dict = {'f1': {'A 1': 'A1', 'B 1': 'B1'}, 'f2': {'A 1': 'A2', 'B 1': 'B2'}, "f3": {'A 1': 'A3', 'B 1': 'B3'}}

with: df = pd.DataFrame.from_dict(dict)

it outputs:

     f1  f2  f3
A 1  A1  A2  A3
B 2  B1  B2  B3

I would like to split the index using the " " separator and add a X number to the second entry of the index. The desired output, with x=1 should be:

L N  f1  f2  f3
A 2  A1  A2  A3
B 3  B1  B2  B3

I have tried:

df.index = df.index.str.split(" ")

and the output is:

        f1  f2  f3
[A, 1]  A1  A2  A3
[B, 2]  B1  B2  B3

From here I don't know what to do to increase the number in the index.

Thank you!

CodePudding user response：

Try this:

df.index = df.index.str.split(' ')
df.index = df.index.str[0]   ' '   (df.index.str[1].astype(int)   1).astype(str)

Output:

>>> df
     f1  f2  f3
A 2  A1  A2  A3
B 3  B1  B2  B3

CodePudding user response：

You could also use a programmatic way:

X = zip(*((a, int(b) 1) for a,b in df.index.map(str.split)))
df.index = pd.MultiIndex.from_arrays(X, names=['L', 'N'])

output:

     f1  f2  f3
L N            
A 2  A1  A2  A3
B 3  B1  B2  B3