Python Pandas - Replace NaN values of a column with respect to another column using interpolate()

user_16432438 0
Viewed 208

I am facing problem while dealing with NaN values in Temperature column with respect to column City by using interpolate().

The df is:

data ={
    'City':['Greenville','Charlotte', 'Los Gatos','Greenville','Carson City','Greenville','Greenville' ,'Charlotte','Carson City',
                'Greenville','Charlotte','Fort Lauderdale', 'Rifle', 'Los Gatos','Fort Lauderdale'],
    'Rec_times':['2019-05-21 08:29:55','2019-01-27 17:43:09','2020-12-13 21:53:00','2019-07-17 11:43:09','2018-04-17 16:51:23',
             '2019-10-07 13:28:09','2020-01-07 11:38:10','2019-11-03 07:13:09','2020-11-19 10:45:23','2020-10-07 15:48:19','2020-10-07 10:53:09',
            '2017-08-31 17:40:49','2016-08-31 17:40:49','2021-11-13 20:13:10','2016-08-31 19:43:29'],
    'Temperature':[30,45,26,33,50,None,29,None,48,32,47,33,None,None,28],
    'Pressure':[30,None,26,43,50,36,29,None,48,32,None,35,23,49,None]
}
df =pd.DataFrame(data)
df

Output:

    City              Rec_times            Temperature   Pressure
0   Greenville      2019-05-21 08:29:55        30.0        30.0
1   Charlotte       2019-01-27 17:43:09        45.0         NaN
2   Los Gatos       2020-12-13 21:53:00        26.0        26.0
3   Greenville      2019-07-17 11:43:09        33.0        43.0
4   Carson City     2018-04-17 16:51:23        50.0        50.0
5   Greenville      2019-10-07 13:28:09        NaN         36.0
6   Greenville      2020-01-07 11:38:10        29.0        29.0
7   Charlotte       2019-11-03 07:13:09        NaN         NaN
8   Carson City     2020-11-19 10:45:23        48.0        48.0
9   Greenville      2020-10-07 15:48:19        32.0        32.0
10  Charlotte       2020-10-07 10:53:09        47.0        NaN
11  Fort Lauderdale 2017-08-31 17:40:49        33.0        35.0
12  Rifle           2016-08-31 17:40:49        NaN         23.0
13  Los Gatos       2021-11-13 20:13:10        NaN         49.0
14  Fort Lauderdale 2016-08-31 19:43:29        28.0        NaN

I want you to deal the NaN values in the column Temperature by grouping them based on City using interpolate(method='time').

Ex:

Consider City as 'Greenville' it has 5 temperatures (30,33,NaN,29 and 32) recorded at different times. The NaN value in Temperature is replaced by a value by grouping the records by the City and using interpolate(method='time').

Note: If you know any other optimal method to replace NaN in Temperature you can use as 'Other solution'.

pandas nan interpolation python
2 Answers

Use a lambda function with DatetimeIndex created by DataFrame.set_index with GroupBy.transform:

df["Rec_times"] = pd.to_datetime(df["Rec_times"])

df['Temperature'] = (df.set_index('Rec_times')
                       .groupby("City")['Temperature']
                       .transform(lambda x: x.interpolate(method='time')).to_numpy())

One possible idea for replacing missing values after interpolate is to replace them by the mean of all values like:

df1.Temperature = df1.Temperature.fillna(df1.Temperature.mean())
user_29010020

My understanding is that you want to replace the NaN in column temperature by an interpolation of the temperature in that specific city.

I would have to think about a more sophisticated solution. But here is a simple hack:

df["Rec_times"] = pd.to_datetime(df["Rec_times"]) # .interpolate requires datetime
df["idx"] = df.index # to restore original ordering
df_new = pd.DataFrame() # will hold new data
for (city,group) in df.groupby("City"):
    group = group.set_index("Rec_times", drop=False)
    df_new = pd.concat((df_new, group.interpolate(method='time')))
    
df_new = df_new.set_index("idx").sort_index() # Restore original ordering
df_new

Note that interpolation for Rifle will yield NaN given there is only one data point which is NaN.

user_62127180
Related