How to add dataframe to multiindex dataframe at specific location

user_4682839 0
Viewed 45

I'm organizing data from separate files into one portable, multiindex dataframe, with multiindex ("A", "B", "C"). Some of the info is gathered from the filenames read in, and should populate the "A", and "B", of the multiindex. "C" should take the form of the index of the file read in. The columns should take the form of the columns read in.

Let's say the files read in become:

df1
   0  1  2  3  4
0  0  9  9  8  5
1  0  8  2  1  2
2  9  1  6  4  3
3  1  4  1  4  4
4  5  4  6  6  2

df2
   0  1  2  3  4
0  4  5  0  7  3
1  8  2  9  1  0
2  5  9  1  6  6
3  4  1  4  6  5
4  3  0  0  8  8

How do I get to this end result:

multiindex_df
         0  1  2  3  4
A  B  C
1  1  0  0  9  9  8  5
      1  0  8  2  1  2
      2  9  1  6  4  3
      3  1  4  1  4  4
      4  5  4  6  6  2
1  2  0  4  5  0  7  3
      1  8  2  9  1  0
      2  5  9  1  6  6
      3  4  1  4  6  5
      4  3  0  0  8  8

Starting from:

import pandas as pd
import numpy as np


multiindex_df = pd.DataFrame(
    index=pd.MultiIndex.from_arrays(
        [[], [], []], names=["A", "B", "C"]))

df1 = pd.DataFrame(np.random.randint(10, size=(5, 5)))
df1_a = 1
df1_b = 1

df2 = pd.DataFrame(np.random.randint(10, size=(5, 5)))
df2_a = 1
df2_b = 2

breakpoint()

This is what I have in mind, but gives a key error:

multiindex_df.loc[(df1_a, df1_b, slice(None))] = df1
multiindex_df.loc[(df2_a, df2_b, slice(None))] = df2
multi-index pandas dataframe python
2 Answers

You could do this as follows:

multiindex_df  = pd.concat([df1, df2], keys=[1,2])
multiindex_df  = pd.concat([multiindex_df], keys=[1])
multiindex_df.index.names = ['A','B','C']

print(multiindex_df)

       0  1  2  3  4
A B C               
1 1 0  0  9  9  8  5
    1  0  8  2  1  2
    2  9  1  6  4  3
    3  1  4  1  4  4
    4  5  4  6  6  2
  2 0  4  5  0  7  3
    1  8  2  9  1  0
    2  5  9  1  6  6
    3  4  1  4  6  5
    4  3  0  0  8  8

Alternatively, you could do it like below:

# collect your dfs inside a dict
dfs = {'1': df1, '2': df2}

# create list for index tuples
multi_index = []
for idx, val in enumerate(dfs):
    for x in dfs[val].index:
        # append tuple to row, e.g (1,1,0), (1,1,1) etc.
        multi_index.append((1,idx+1,x))

# concat your dfs
multiindex_df_two = pd.concat([df1, df2])
# create multiindex from tuples, and add names
multiindex_df_two.index = pd.MultiIndex.from_tuples(multi_index, names=['A','B','C'])

# check
multiindex_df.equals(multiindex_df_two) # True
user_184706920

Ouroboros' answer made me realize that instead of trying to fit the read-in file dfs into a formatted df, the cleaner solution is to format each individual file df, then concat.

In order to do that, I have to re-format the file df indices into a multiindex, which prompted this question and answer.

Having that down, and using Ouroboros' answer, the solution becomes:

import pandas as pd
import numpy as np


df1 = pd.DataFrame(np.random.randint(10, size=(5, 5)))
df1_a = 1
df1_b = 1

df1.index = pd.MultiIndex.from_product(
    [[df1_a], [df1_b], df1.index], names=["A", "B", "C"])

df2 = pd.DataFrame(np.random.randint(10, size=(5, 5)))
df2_a = 1
df2_b = 2

df2.index = pd.MultiIndex.from_product(
    [[df2_a], [df2_b], df2.index], names=["A", "B", "C"])

multiindex_df = pd.concat([df1, df2])

Which is obviously well suited for a loop.

Output:

df1
   0  1  2  3  4
0  5  3  1  1  3
1  8  9  7  5  6
2  8  6  6  7  7
3  3  4  9  7  2
4  3  2  1  6  2

df2
   0  1  2  3  4
0  5  0  6  9  3
1  7  5  5  9  6
2  2  1  9  6  3
3  9  4  3  7  0
4  5  9  5  9  6

multiiindex_df
       0  1  2  3  4
A B C
1 1 0  5  3  1  1  3
    1  8  9  7  5  6
    2  8  6  6  7  7
    3  3  4  9  7  2
    4  3  2  1  6  2
  2 0  5  0  6  9  3
    1  7  5  5  9  6
    2  2  1  9  6  3
    3  9  4  3  7  0
    4  5  9  5  9  6
user_46828390
Related