join two dataframes in cells with a condition-CodePudding

I have a two dataframe that have the same rows and columns in python, I want to join with the following condition.

Mainteain the same structure of df_a, introduce de value of the df_b just in the cells with "x" value of the df_a

df_a =

2022	2023	2024	2025	2026	2027
x	x	x	nan	nan	nan
nan	nan	x	x	nan	nan
nan	x	x	x	x	x

df_b =

1	2	3	4	5	6
4	7	8	nan	nan	nan
5	8	nan	nan	nan	nan
6	9	4	3	2	nan

The final dataframe would be

df_final =

2022	2023	2024	2025	2026	2027
4	7	8	nan	nan	nan
nan	nan	5	8	nan	nan
nan	6	9	4	3	2

Many thanks in advance

CodePudding user response：

Check for equality for value x in df_a then assign the values of df_b

>>> df_a[df_a.eq('x')]=df_b.values

# df_a
  2022 2023 2024 2025 2026 2027
0    4  7.0    8  NaN  NaN  NaN
1  NaN  NaN    5  8.0  NaN  NaN
2  NaN  6.0    9  4.0  3.0  2.0

CodePudding user response：

If you make the column names of df_b the same as df_a, you can use the update functionality and leverage the filter_func parameter to update only 'x' values.

df_b.columns = df_a.columns
df_a.update(df_b, filter_func=lambda x: x=='x')

CodePudding user response：

This is the way ;) :

import pandas as pd
import numpy as np
a = {
    2022:['x', None, None] ,2023:['x', None, 'x'] ,2024:['x','x','x'] ,2025:[None, 'x', 'x'] ,2026:[None,None,'x'] ,2027:[None,None,'x']
}

b={
    1:[4,5,6],2:[7,8,9],3:[8, None, 4], 4:[None,None,3],5:[None,None,2],6:[None,None,None]
    
}
 
df_a = pd.DataFrame(a)
df_b = pd.DataFrame(b)
 
 
l = []
for i in df_b.values.tolist():
    
    for j in i:
        if(type(j)==int)or(type(j)==float):
            try:
                l.append(int(j))
            except:
                pass
    

df_a[df_a.eq("x")] = l
df_a

Output:

   2022  2023 2024  2025  2026  2027
0     4     7    8  None  None  None
1  None  None    5     8  None  None
2  None     6    9     4     3     2