Row-wise concat with sample 2 dataframes

user_14401290 0
Viewed 22

I've got 2 dataframes,

df1
      col1    col2   col3
    0  ABC  XYZ123  RA100
    1  DEF  YHG753  RA200
    2  ABC  XYZ123  RA100
    3  DEF  YHG753  RA200
    4  ABC  XYZ123  RA100
    5  DEF  YHG753  RA200

df2
      col5 col6
    0  TU1  DUM1
    1  TU2  DUM2
    2  TU3  DUM3

I'm trying to concat each row of df1 with a randomly selected row from df2.

I've been trying with pd.concat(frames, axis=1), but concat wont work for me as shape of the dataframes are not matching. So I'm wondering whether there's a better way to do to do this rather than increasing the rows of df2 to match it with that of df1, then do concat.

Reference: Randomly concat data frames by row

Expected sample output:

  col1    col2   col3  col5 col6 
0  ABC  XYZ123  RA100  TU3  DUM3
1  DEF  YHG753  RA200  TU1  DUM1
2  ABC  XYZ123  RA100  TU2  DUM2
3  DEF  YHG753  RA200  TU2  DUM2
4  ABC  XYZ123  RA100  TU3  DUM3
5  DEF  YHG753  RA200  TU1  DUM1
pandas python-2.7 dataframe
1 Answers

First idea is create helper column with random assign index value of df2.index and then use DataFrame.join:

#for test
np.random.seed(2002)

df = (df1.assign(id=np.random.choice(df2.index, size=len(df1)))
         .join(df2, on='id')
         .drop('id', axis=1))
print (df)
  col1    col2   col3 col5  col6
0  ABC  XYZ123  RA100  TU2  DUM2
1  DEF  YHG753  RA200  TU3  DUM3
2  ABC  XYZ123  RA100  TU1  DUM1
3  DEF  YHG753  RA200  TU2  DUM2
4  ABC  XYZ123  RA100  TU1  DUM1
5  DEF  YHG753  RA200  TU1  DUM1

Another idea is merge by numpy array in DataFrame.merge:

np.random.seed(2002)

idx = np.random.choice(df2.index, size=len(df1))
         
df = df1.merge(df2, left_on=idx, right_index=True).drop('key_0', axis=1)
print (df)
  col1    col2   col3 col5  col6
0  ABC  XYZ123  RA100  TU2  DUM2
3  DEF  YHG753  RA200  TU2  DUM2
1  DEF  YHG753  RA200  TU3  DUM3
2  ABC  XYZ123  RA100  TU1  DUM1
4  ABC  XYZ123  RA100  TU1  DUM1
5  DEF  YHG753  RA200  TU1  DUM1
user_29010020
Related