replace None values in DataFrame using cell's values of another DataFrame

user_11248505 0
Viewed 72

there are two pandas DataFrames:

df1 = pd.DataFrame({
    'name': ['ann', 'maxim', 'ann', 'maxim'],
    'surname': [ 'smith', 'shwarz','smith', 'shwarz'],
    'date': ['2020.01.01',  '2020.01.01', '2020.03.05','2020.03.05'],
    'mark_1': [None,'B', 'A', None],
    'mark_2': [None,'B', None,'A'],
    'mark_3': [None,None, 'A', 'C']
       })
name surname date mark_1 mark_2 mark_3
ann smith 2020.01.01 None None None
maxim shwarz 2020.01.01 B B None
ann smith 2020.03.05 A None A
maxim shwarz 2020.03.05 None A C 
df2 = pd.DataFrame({
    'name': ['ann', 'maxim'],
    'surname': [ 'smith', 'shwarz'],
    'mark_1': ['Z','X'],
    'mark_2': ['H','F'],
    'mark_3': ['P','Y']
       })
name surname mark_1 mark_2 mark_3
ann smith Z H P
maxim shwarz X F Y

I need:

name surname date mark_1 mark_2 mark_3
ann smith 2020.01.01 Z H P
maxim shwarz 2020.01.01 B B Y
ann smith 2020.03.05 A H A
maxim shwarz 2020.03.05 X A C

But functiondf1.isnull(df2) replaces only first rows with the similar names and surnames:

name surname date mark_1 mark_2 mark_3
ann smith 2020.01.01 Z H P
maxim shwarz 2020.01.01 B B Y
ann smith 2020.03.05 A None A
maxim shwarz 2020.03.05 None A C

As I understand, it should be something like CASE statement from SQL, but I can't find the answer.

Special respect if you can explain the same function for two PySpark DataFrames!

pyspark pandas apache-spark dataframe python-3.x
2 Answers

Try with set_index + combine_first:

new_df = (
    df1.set_index(['name', 'surname'])
        .combine_first(df2.set_index(['name', 'surname']))
        .reset_index()
)

new_df:

    name surname        date mark_1 mark_2 mark_3
0    ann   smith  2020.01.01      Z      H      P
1    ann   smith  2020.03.05      A      H      A
2  maxim  shwarz  2020.01.01      B      B      Y
3  maxim  shwarz  2020.03.05      X      A      C

Optional sort_values:

new_df = (
    df1.set_index(['name', 'surname'])
        .combine_first(df2.set_index(['name', 'surname']))
        .reset_index()
        .sort_values('date')
)

new_df:

    name surname        date mark_1 mark_2 mark_3
0    ann   smith  2020.01.01      Z      H      P
2  maxim  shwarz  2020.01.01      B      B      Y
1    ann   smith  2020.03.05      A      H      A
3  maxim  shwarz  2020.03.05      X      A      C
user_154978880

Using Spark you must join the dataframes and use coalesce function to replace null values:

import pandas as pd
import pyspark.sql.functions as f


df1 = pd.DataFrame({
    'name': ['ann', 'maxim', 'ann', 'maxim'],
    'surname': [ 'smith', 'shwarz','smith', 'shwarz'],
    'date': ['2020.01.01',  '2020.01.01', '2020.03.05','2020.03.05'],
    'mark_1': [None,'B', 'A', None],
    'mark_2': [None,'B', None,'A'],
    'mark_3': [None,None, 'A', 'C']
})
df1 = spark.createDataFrame(df1)

df2 = pd.DataFrame({
    'name': ['ann', 'maxim'],
    'surname': [ 'smith', 'shwarz'],
    'mark_1': ['Z','X'],
    'mark_2': ['H','F'],
    'mark_3': ['P','Y']
})
df2 = spark.createDataFrame(df2)

df3 = df1.alias('l').join(df2.alias('r'), on=['name', 'surname'], how='left')
df3 = (df3
       .select('name', 
               'surname', 
               'date', 
               f.coalesce('l.mark_1', 'r.mark_1').alias('mark_1'), 
               f.coalesce('l.mark_2', 'r.mark_2').alias('mark_2'), 
               f.coalesce('l.mark_3', 'r.mark_3').alias('mark_3')))

(df3
 .sort('date')
 .show(truncate=False))
# +-----+-------+----------+------+------+------+
# |name |surname|date      |mark_1|mark_2|mark_3|
# +-----+-------+----------+------+------+------+
# |ann  |smith  |2020.01.01|Z     |H     |P     |
# |maxim|shwarz |2020.01.01|B     |B     |Y     |
# |ann  |smith  |2020.03.05|A     |H     |A     |
# |maxim|shwarz |2020.03.05|X     |A     |C     |
# +-----+-------+----------+------+------+------+
user_60802760
Related