SettingWithCopyWarning when using groupby and transform('first') for fillna

I know there are lots of similar questions. I have been trying to use them for more than an hour, but couldn't.

I have a table like the following:

read_time	location_id	lat	lng
2019-02-01	1	43	-79.1
2019-02-01	2	43.1	-79.4
2019-02-01	3	43	-79.2
2020-03-01	2	nan	nan

I want to fill the empty lat and long based on the location_id. So I did so by:

df['lat'] = df.groupby('location_id')['lat'].transform('first')
df['lng'] = df.groupby('location_id')['lng'].transform('first')

and have it as follows:

read_time	location_id	lat	lng
2019-02-01	1	43	-79.1
2019-02-01	2	43.1	-79.4
2019-02-01	3	43	-79.2
2020-03-01	2	43.1	-79.4

But I am getting, the following warning:

SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

I do not want to ignore the warning, as many suggest. I would like to handle it. but I do not know how!

Solution

Try using pd.DataFrameGroupBy.ffill with bfill:

>>> df = df.copy()
>>> df.loc[:, ['lat', 'lng']] = df.groupby('location_id').ffill().bfill()[['lat', 'lng']]
>>> df
    read_time  location_id   lat   lng
0  2019-02-01            1  43.0 -79.1
1  2019-02-01            2  43.1 -79.4
2  2019-02-02            2  43.1 -79.4
3  2019-02-01            3  43.0 -79.2
>>>