join two dataframes in cells with a condition

Hagen 0
Viewed 54

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

pandas python
3 Answers

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
ThePyGuy0

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')
user2uNj0

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
Shahab Rahnama0
Related