Concatenate a list of dfs pandas

May 25, 2023

I have a df_list

  [0  1  2  3
0  X  X  0  0
1  X  X  X  X,
   0  1  2  3
0  X  X  X  X
1  0  0  X  X,
   0  1  2  3
0  X  X  X  X]

I would like to concatenate the dfs such that the final df has 12 columns and 5 rows. For example, the first two rows and first 4 cols will have the values of the first df in the df_list and the first two rows and last 8 columns should have the value 0. Like so

    0   1   2   3   4   5   6   7   8   9   10  11
0   X   X   0   0   0   0   0   0   0   0   0   0
1   X   X   X   X   0   0   0   0   0   0   0   0
2   0   0   0   0   X   X   X   X   0   0   0   0
3   0   0   0   0   0   0   X   X   0   0   0   0
4   0   0   0   0   0   0   0   0   X   X   X   X

How can I do this?

>Solution :

Assuming lst the input list, you can change the index of the successive DataFrames:

lst2 = []
start_idx = 0
start_col = 0
for d in lst:
    lst2.append(d.set_axis(range(start_idx, start_idx+len(d)))
                 .set_axis(range(start_col, start_col+d.shape[1]), axis=1)
               )
    start_idx += len(d)
    start_col += d.shape[1]

out = pd.concat(lst2).fillna(0)

Or using reshaping:

out = pd.concat(lst, keys=range(len(lst)))
out.index = pd.MultiIndex.from_arrays([out.index.get_level_values(0),
                                       range(len(out))])

out = out.stack().unstack([0, -1], fill_value=0)
out = out.set_axis(range(out.shape[1]), axis=1)

Output:

  0  1  2  3  4  5  6  7  8  9  10 11
0  X  X  0  0  0  0  0  0  0  0  0  0
1  X  X  X  X  0  0  0  0  0  0  0  0
2  0  0  0  0  X  X  X  X  0  0  0  0
3  0  0  0  0  0  0  X  X  0  0  0  0
4  0  0  0  0  0  0  0  0  X  X  X  X