Python - merge dataframes to replace missing values with the actual ones

Question

I have a list of 20 dataframes with sports results with this structure:

Key1	Key2	Variable1	Variable2
TeamA	TeamB	20	Nan
TeamC	TeamA	Nan	25
TeamA	TeamD	17	Nan

Answer 1

you can try it like this... This is just for creating your datafiles (I saved them as .csv and then read them in). Keep in mind that I read in your 'Nan'-values so that pandas recognizes them:

import pandas as pd
import os

path = r'C:...'
df1_fl = r'2020-12-31_df1.csv'
df2_fl = r'2020-12-31_df2.csv'
df3_fl = r'2020-12-31_df3.csv'

df1 = pd.read_csv(os.path.join(path, df1_fl), sep=';', na_values='Nan')
df2 = pd.read_csv(os.path.join(path, df2_fl), sep=';', na_values='Nan')
df3 = pd.read_csv(os.path.join(path, df3_fl), sep=';', na_values='Nan')

Then I just replace the nan-values with a zero value and aggregate all your data together in one dataframe:

df = pd.concat([df1, df2, df3]).fillna(0)

Then the interesting part starts, grouping the data by the columns 'Key1' and 'Key2', finding the max over the group (this fills up the nan values). In the end, you need to extract out the now existing multi-index in two columns as given in the beginning dataframes with reset_index.

df_agg = df.groupby(by=['Key1', 'Key2']).max().reset_index()