Pandas interpolate missing values based on other columns criteria

user_15871722 0
Viewed 139

I have the current dataframe:

Date Source Type Visits Sales
01/01/2020 Source1 Type1 100 10
01/01/2020 Source2 Type1 150 5
02/01/2020 Source1 Type1 NaN NaN
02/01/2020 Source2 Type1 125 15
03/01/2020 Source1 Type2 150 18
03/01/2020 Source2 Type2 NaN NaN
04/01/2020 Source1 Type2 150 25
04/01/2020 Source2 Type2 120 05

What I'd like to do is a simple .interpolate() for the missing data however I need to group it by the Source and Type to keep the data as accurate as possible rather than basing it on the above and below rows which aren't relevant.

I've got to this stage:

df_fixed = df[['Source','Type','Visits','Sales']].loc[(df['Source'] == 'Source1') & (df['Type'] == 'Type1')].interpolate()

Which does the first step but can't get any further and feel like there's an easier way.

What would be the most elegant way to complete this?

missing-data pandas interpolation python
1 Answers

One idea is change the NAN - of median or mean.

df['visits'].fillna(df['visits'].median(), inplace=True)
df.fillna(df.mean())

enter image description here

Edit:

If You decide to use .interpolate() - so

need to group it by the Source and Type:

You can use groupBy() - method for group by Source and Type:

df.groupby(['Source', 'Type'])
user_94559020
Related