Concatenate single row dataframe with multiple row dataframe

Question

I have a dataframe with large number of columns but single row as df1:

Col1 Col2   Price   Qty
A    B      16      5

I have another dataframe as follows, df2:

Price   Qty
8       2.5
16      5
6       1.5

I want to achieve the following:

Col1    Col2    Price   Qty
A       B       8       2.5
A       B       16      5
A       B       6       1.5

Where essentially I am taking all rows of df1 and repeat it while concatenating with df2 but bring the Price and Qty columns from df2 and replace the ones present originally in df1.

I am not sure how to proceed with above.

`pd.concat([df1, df2], ignore_index=True).ffill().drop_duplicates()` ? — Timeless, Nov 20 '22 at 14:41
@abokey is right! When using `pd.concat` with `ignore_index=True`, the labels of the columns vanish, but you can restore them as I do in my solution to your previous question: In sum, to the new dataframe, you have to set the column names to `df.columns`. — PaulS, Nov 20 '22 at 15:45
This question seems to be similar to yours: https://stackoverflow.com/questions/27126511/add-columns-different-length-pandas — PaulS, Nov 20 '22 at 15:53

score 1 · Accepted Answer · answered Nov 20 '22 at 15:59

I believe the following approach will work,

# first lets repeat the single row df1 as many times as there are rows in df2
df1 = pd.DataFrame(np.repeat(df1.values, len(df2.index), axis=0), columns=df1.columns)

# lets reset the indexes of both DataFrames just to be safe
df1.reset_index(inplace=True)
df2.reset_index(inplace=True)

# now, lets merge the two DataFrames based on the index
# after dropping the Price and Qty columns from df1
df3 = pd.merge(df1.drop(['Price', 'Qty'], axis=1), df2, left_index=True, right_index=True)

# finally, lets drop the index columns
df3.drop(['index_x', 'index_y'], inplace=True, axis=1)

Concatenate single row dataframe with multiple row dataframe

1 Answers1