Pandas, merge rows only in same groups

user_8560167 0
Viewed 39

Input DF:

df = pd.DataFrame({'A': ['one',np.nan,'two',np.nan],
                   'B': [np.nan,22,np.nan,44],
                   'group':[0,0,1,1]
                   })
print(df)
     A     B  group
0  one   NaN      0
1  NaN  22.0      0
2  two   NaN      1
3  NaN  44.0      1

I want to merge those rows in one, all cells in one in same column. But taking into account groups.

Currently have:

df=df.agg(lambda x: ','.join(x.dropna().astype(str))
            ).to_frame().T
print(df)
         A          B    group
0  one,two  22.0,44.0  0,0,1,1

but this way is taking all rows, not only groups

Expected Output:

     A     B    
0  one     22.0
1  two     44.0
pandas python
2 Answers

If possible simplify solution for first non missing value per group use:

df = df.groupby('group').first()
print(df)
         A     B
group           
0      one  22.0
1      two  44.0

If not and need general solution:

df = pd.DataFrame({'A': ['one',np.nan,'two',np.nan],
                   'B': [np.nan,22,np.nan,44],
                   'group':[0,0,0,1]
                   })

def f(x):
    return x.apply(lambda x: pd.Series(x.dropna().to_numpy()))
    

df = df.set_index('group').groupby('group').apply(f).reset_index(level=1, drop=True).reset_index()
print(df)
   group    A     B
0      0  one  22.0
1      0  two   NaN
2      1  NaN  44.0
user_29010020 
df_a = df.drop('B', axis=1).dropna()   
df_b = df.drop('A', axis=1).dropna() 
pd.merge(df_a, df_b, on='group')
user_138133650
Related