pandas combining 2 dataframe and filling all rows

Question

i have 2 dataframe

df1:                             df2:
A  B  C  D                       X  Y  Z
1  2  3  4                       p2 p3 p4
5  6  7  8
9  10 11 12

How can i concat the 2 dataframe such that the output is

A   B   C   D   X   Y   Z
1   2   3   4   p2  p3  p4
5   6   7   8   p2  p3  p4
9   10  11  12  p2  p3  p4

Currently if i use concat only the first row will be applied with the df2 values but other rows are filled with NaN.

score 1 · Accepted Answer · answered Oct 20 '22 at 08:59

Use a cross merge:

out = df1.merge(df2, how='cross')

output:

   A   B   C   D   X   Y   Z
0  1   2   3   4  p2  p3  p4
1  5   6   7   8  p2  p3  p4
2  9  10  11  12  p2  p3  p4

You will however lose the original index of df1. If it is important to keep use:

out = df1.merge(df2, how='cross').set_axis(df1.index)

# or
out = df1.reset_index().merge(df2, how='cross').set_index('index')

1 Answers1