Combine 2 dataframes of different length to form a new dataframe that has a length equal to the max length of the 2 dataframes

Question

I have a dataframe:

t = pd.Series([2,4,6,8,10,12],index= index)
df1 = pd.DataFrame(s,columns = ["MUL1"])
df1["MUL2"] =t

   MUL1  MUL2
0     1     2
1     2     4
2     2     6
3     3     8
4     3    10
5     6    12

and another dataframe:

u = pd.Series([1,2,3,6],index= index)
v = pd.Series([2,8,10,12],index= index)
df2 = pd.DataFrame(u,columns = ["MUL3"])
df2["MUL4"] =v

Now I want a new dataframe which looks like the following:

   MUL6  MUL7
0     1     2
1     2     8
2     2     8
3     3    10
4     3    10
5     6    12

By combining the first 2 dataframes.

I have tried the following:

X1 = df1.to_numpy()
X2 = df2.to_numpy()

list = []
for i in range(X1.shape[0]):
  for j in range(X2.shape[0]):
    if X1[i, -1] == X2[j, -1]:
      list.append(X2[X1[i, -1]==X2[j, -1], -1])

I was trying to convert the dataframes to numpy arrays so I can iterate through them to get a new array that I can convert back to a dataframe. But the size of the new dataframe is not equal to size of the first dataframe. Please I would appreciate any help. Thanks.

We cannot reproduce `dataframe1` nor `dataframe2` with the given code. There are too many errors/missing variables... — BeRT2me, Oct 07 '22 at 16:39
What is `index`? What is `s`? There are still missing variables. — BeRT2me, Oct 07 '22 at 16:45
Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) — BeRT2me, Oct 07 '22 at 17:00

score 0 · Answer 1 · answered Oct 07 '22 at 16:55

Although the details of the logic are cryptic, I believe that you want a merge:

(df1[['MUL1']].rename(columns={'MUL1': 'MUL6'})
    .merge(df2.rename(columns={'MUL3': 'MUL6', 'MUL4': 'MUL7'}),
           on='MUL6', how='left')
)

output:

   MUL6  MUL7
0     1     2
1     2     8
2     2     8
3     3    10
4     3    10
5     6    12

Combine 2 dataframes of different length to form a new dataframe that has a length equal to the max length of the 2 dataframes

1 Answers1