join two df with unequal count of levels in column

user_13428079 0
Viewed 40

I would like to join two dataframes (df1,df2), but i can't figure it.

import pandas as pd  
data = {'Step_ID': ["Step1", "Step1", "Step1", "Step2", "Step2", "Step3", "Step3"],    
        'value_01': [2, 2.3, 2.2, 0, 0, 5, 5.2]}  
df1 = pd.DataFrame(data) 

data = {'Step_ID': ["Step1", "Step1", "Step1", "Step1", "Step2", "Step2", "Step2", "Step3", "Step3", "Step3"],    
        'value_02': [2.3, 2.5, 2.1, 2.5, 0, 0, 0, 5.1, 5.6, 5.8]}  
df2 = pd.DataFrame(data)

I would like to merge the on the column "Step_ID" as follows:

enter image description here

I tried several merges and its settings, but without any sucess.

pd.merge(df1, df2, left_on = ['Step_ID'], right_on = ['Step_ID'], how = 'outer')

The closest solution i have done with the following code, but it is not as required:

df1.combine_first(df2)

Is there any possibility to join those two dataframe in the required way? See the picture above.

pandas dataframe merge join
1 Answers

We can try with cumcount + merge

new_df = df1.assign(index_count=df1.groupby('Step_ID').cumcount())\
            .merge(df2.assign(index_count=df2.groupby('Step_ID').cumcount()),
                   on=['Step_ID', 'index_count'], how='outer')\
            .sort_values(['Step_ID', 'index_count'])\
            .drop('index_count', axis=1)

print(new_df)

  Step_ID  value_01  value_02
0   Step1       2.0       2.3
1   Step1       2.3       2.5
2   Step1       2.2       2.1
7   Step1       NaN       2.5
3   Step2       0.0       0.0
4   Step2       0.0       0.0
8   Step2       NaN       0.0
5   Step3       5.0       5.1
6   Step3       5.2       5.6
9   Step3       NaN       5.8
user_118842370
Related