Pandas interpolation: dataframe vs series

Question

Let's say I have a pandas dataframe df, and I want to interpolate the missing values.

Case 1

The first case, I try to interpolate the whole dataframe df. But somehow I got a warning message and it failed.

[In] interpolateList = [x for x in xlsx_df.columns if x not in ['Date', 'Time', 'DateTime', 'Year', 'YearMonth']]

# interpolation
[In] xlsx_df[interpolateList].interpolate(method='linear', inplace=True) # axis: default 0, which means col by col
print('Whether there are any NaN value: ', xlsx_df.isnull().values.any())

[Out] Whether there are any NaN value: True

/home/usrname/.local/lib/python3.6/site-packages/ipykernel_launcher.py:4: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy after removing the cwd from sys.path.

Case 2

In another case, I try to interpolate each column (which means it's a pandas series) and it works as I expect.

I used visualization tool to double check the result, and it looks great.

[In] interpolateList = [x for x in xlsx_df.columns if x not in ['Date', 'Time', 'DateTime', 'Year', 'YearMonth']]

# interpolation
[In] for col in interpolateList:
         xlsx_df[col].interpolate(method='linear', inplace=True) # axis: default 0, which means col by col
     print('Are there any NaN value: ', xlsx_df.isnull().values.any())

[Out] Whether there are any NaN value: False

My Question is

Why does the case 1 fail? Is it because I select the columns of dataframe in the wrong way?

Reference

• pandas.DataFrame.interpolate

Answer 1

The problem is that you are trying to assign new values to a subset of the original data frame, as the warning message indicates: "A value is trying to be set on a copy of a slice from a DataFrame."

You need to explicitly specify the slice of the data frame that you want to redefine using xlsx_df[interpolateList] = xlsx_df[interpolateList].interpolate(method='linear'), as in the following:

# Generate sample data
xlsx_df = pd.DataFrame(np.random.rand(10,7), columns = ['Date', 'Time', 'DateTime', 'Year', 'YearMonth', 'A', 'B'])
xlsx_df.loc[5, 'A'] = np.nan
xlsx_df.loc[7, 'B'] = np.nan
print(xlsx_df)

       Date      Time  DateTime      Year  YearMonth         A         B
0  0.141867  0.361801  0.381639  0.947309   0.264126  0.472429  0.811379
1  0.815618  0.750343  0.287834  0.494972   0.186212  0.188400  0.435841
2  0.738592  0.526584  0.886683  0.830909   0.031605  0.568419  0.609161
3  0.961575  0.023237  0.531104  0.204781   0.053663  0.587489  0.772604
4  0.774865  0.030288  0.406946  0.044510   0.247839  0.192881  0.215183
5  0.339118  0.277418  0.962280  0.352407   0.894173       NaN  0.763747
6  0.061346  0.462761  0.005510  0.810291   0.950486  0.035107  0.933846
7  0.773854  0.358862  0.908877  0.296257   0.409295  0.096711       NaN
8  0.029601  0.484905  0.683192  0.821238   0.149941  0.754090  0.719077
9  0.559571  0.584645  0.091271  0.600471   0.381522  0.867581  0.313099

interpolateList = [x for x in xlsx_df.columns if x not in ['Date', 'Time', 'DateTime', 'Year', 'YearMonth']]

# Explicitly re-define the data frame slice:
xlsx_df[interpolateList] = xlsx_df[interpolateList].interpolate(method='linear') # axis: default 0, which means col by col
print('Any NaN values:', xlsx_df.isnull().values.any())
print(xlsx_df)

Any NaN values: False
       Date      Time  DateTime      Year  YearMonth         A         B
0  0.461639  0.367251  0.939078  0.222373   0.553542  0.054498  0.191202
1  0.604027  0.662184  0.580996  0.869601   0.993405  0.763862  0.465092
2  0.833935  0.120387  0.683271  0.518338   0.178066  0.972934  0.338437
3  0.615423  0.878594  0.506689  0.115138   0.818739  0.353214  0.983067
4  0.883825  0.853449  0.356253  0.757130   0.986329  0.526322  0.470732
5  0.014666  0.700204  0.858478  0.247457   0.315025  0.751298  0.072080
6  0.089642  0.421111  0.261219  0.406037   0.454386  0.976273  0.944260
7  0.139730  0.882586  0.080207  0.507635   0.163910  0.365892  0.633583
8  0.424505  0.806951  0.718816  0.942839   0.534156  0.802148  0.322907
9  0.345746  0.400510  0.410916  0.103253   0.519099  0.096803  0.889762