user308827 user308827 - 2 months ago 13
Python Question

Error while trying to interpolate across rows in pandas dataframe

I would like to interpolate the dataframe in the attached csv by row (i.e. fill NaNs by row). csv is here: https://www.dropbox.com/s/v7ee5d25tvxo625/pd_fill.csv?dl=0

However, after reading the csv into a pandas dataframe, with the foll. code I get an error:

df.interpolate(axis=1)

*** TypeError: Cannot interpolate with all NaNs.


How to fix this and get interpolation across rows?

Answer

you can interpolate only corresponding (numeric) columns.

we will use .filter(regex=<RE>) function in order to filter the columns:

In [33]: df.filter(regex='^19|20').columns
Out[33]:
Index(['1974', '1975', '1976', '1977', '1978', '1979', '1980', '1981', '1982',
       '1983', '1984', '1985', '1986', '1987', '1988', '1989', '1990', '1991',
       '1992', '1993', '1994', '1995', '1996', '1997', '1998', '1999', '2000',
       '2001', '2002', '2003', '2004', '2005', '2006', '2007', '2008', '2009',
       '2010', '2011', '2012', '2013', '2014', '2015'],
      dtype='object')

so you can use:

df.filter(regex='^19|20').interpolate(axis=1)

Example (just first 5 rows):

In [34]: df.filter(regex='^19|20').interpolate(axis=1).head()
Out[34]:
       1974           1975           1976           1977           1978  \
0       0.0     250.000000     500.000000     750.000000    1000.000000
1  100000.0  130000.000000  160000.000000  190000.000000  220000.000000
2       0.0       6.871795      13.743590      20.615385      27.487179
3       0.0    9230.769231   18461.538462   27692.307692   36923.076923
4       0.0   19000.000000   38000.000000   57000.000000   92100.000000

            1979           1980           1981           1982           1983  \
0    1250.000000    1500.000000    1750.000000    2000.000000    2800.000000
1  250000.000000  280000.000000  310000.000000  340000.000000  370000.000000
2      34.358974      41.230769      48.102564      54.974359      61.846154
3   46153.846154   55384.615385   64615.384615   73846.153846   83076.923077
4  127200.000000  162300.000000  197400.000000  232500.000000  316000.000000

      ...              2006          2007          2008          2009  \
0     ...      2.516660e+07  2.770800e+07  2.753100e+07  2.735400e+07
1     ...      1.166667e+07  1.433333e+07  1.700000e+07  1.416962e+07
2     ...      2.198974e+02  2.267692e+02  2.336410e+02  2.405128e+02
3     ...      6.394286e+05  7.060000e+05  7.060000e+05  7.060000e+05
4     ...      2.550200e+07  2.655350e+07  2.760500e+07  2.865650e+07

           2010          2011          2012          2013        2014  \
0  2.717700e+07  2.700000e+07  2.809050e+07  2.918100e+07  29181000.0
1  1.133923e+07  8.508848e+06  5.678463e+06  2.848079e+06     17695.0
2  2.473846e+02  2.542564e+02  2.611282e+02  2.680000e+02       268.0
3  7.060000e+05  7.060000e+05  7.060000e+05  7.060000e+05    706000.0
4  2.970800e+07  3.075950e+07  3.181100e+07  3.181100e+07  31811000.0

         2015
0  29181000.0
1     17695.0
2       268.0
3    706000.0
4  31811000.0

[5 rows x 42 columns]