How to contact two dataframes on axis 1 when row size is different-CodePudding

1st dataframe is this (df1):

Col1  Col2
abc   5
xyz   153
yyy   24
zzz   90

2nd dataframe is like this (df2):

col3  col
233   533

This is the output I want (Concat df2 on df1 on axis=1 on all rows):

Col1    Col2    col3    col
abc     5       233     533
xyz     153     233     533
yyy     24      233     533
zzz     90      233     533

But when I do pd.concat([df1,df2],axis=1), I get this result:

Col1    Col2    col3    col
abc     5       233.0   533.0
xyz     153     NaN     NaN
yyy     24      NaN     NaN
zzz     90      NaN     NaN

CodePudding user response：

Transform your second DataFrame as a dict and recreate a DataFrame before concatenating them:

>>> pd.concat([df1, pd.DataFrame(df2.to_dict('list'), index=df1.index)], axis=1)
  Col1  Col2  col3  col
0  abc     5   233  533
1  xyz   153   233  533
2  yyy    24   233  533
3  zzz    90   233  533

CodePudding user response：

Perfect use case for a cross merge:

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

Output:

  Col1  Col2  col3  col
0  abc     5   233  533
1  xyz   153   233  533
2  yyy    24   233  533
3  zzz    90   233  533

CodePudding user response：

Use:

output = df1.join(df2).ffill()

Output:

  Col1  Col2   col3    col
0  abc     5  233.0  533.0
1  xyz   153  233.0  533.0
2  yyy    24  233.0  533.0
3  zzz    90  233.0  533.0

CodePudding user response：

One fast method is expand_grid from pyjanitor:

# pip install pyjanitor
import pandas as pd
import janitor as jn

others = {'df1':df1, 'df2':df2}

jn.expand_grid(others = others).droplevel(axis = 1, level = 0)

  Col1  Col2  col3  col
0  abc     5   233  533
1  xyz   153   233  533
2  yyy    24   233  533
3  zzz    90   233  533