0

Is it possible in pandas to interpolate for missing values in multiindex dataframe. This example below does not work as expected:

arr1=np.array(np.arange(1.,10.,1.))
arr2=np.array(np.arange(2.,20.,2.))
df1=pd.DataFrame(zip(arr1,arr2,arr1+arr2,arr1*arr2),columns=['x','y','xplusy','xtimesy'])

df1.set_index(['x','y'],inplace=True)

df2=df1.reindex(index=zip(*df1.index.levels)+[(2,2),(3,2),(5,5)])
df2.sortlevel([0,1],inplace=True)
df2.interpolate(method='linear',inplace=True)

displays not what I expected in xplusy and xtimesy columns for added indices.

-----------  ----  ---
(1.0, 2.0)    3      2
(2.0, 2.0)    4.5    5
(2.0, 4.0)    6      8
(3.0, 2.0)    7.5   13
(3.0, 6.0)    9     18
(4.0, 8.0)   12     32
(5.0, 5.0)   13.5   41
(5.0, 10.0)  15     50
(6.0, 12.0)  18     72
(7.0, 14.0)  21     98
(8.0, 16.0)  24    128
(9.0, 18.0)  27    162
-----------  ----  ---
denfromufa
  • 5,610
  • 13
  • 81
  • 138

3 Answers3

1

So before filling the missing values, this is what you have in the first few rows:

df2

      xplusy  xtimesy
x y                  
1 2        3        2
2 2      NaN      NaN
  4        6        8

It looks like you want to interpolate based on the MultiIndex. I don't believe there is any way to do that with pandas interpolate, but you can do it based on a simple index (method='linear' ignores the index btw and is also the default so no need to specify it either):

df2.reset_index(level=1).interpolate(method='index')

    y  xplusy  xtimesy
x                     
1   2       3        2
2   2       6        8
2   4       6        8

df2.reset_index(level=0).interpolate(method='index')

    x  xplusy  xtimesy
y                     
2   1     3.0        2
2   2     3.0        2
4   2     6.0        8

Obviously in this case you could create xplusy and xtimesy in multiple steps (first x, then y, then xplusy and xtimesy) but I'm not sure if that's what you are really trying to do.

Anyway, this is the kind of 1d interpolation you can do pretty easily with pandas interpolate. If that's not enough, you could look into numpy's interp2d for starters.

JohnE
  • 29,156
  • 8
  • 79
  • 109
  • I'm looking for ND interpolation such as griddata in scipy – denfromufa Apr 06 '15 at 13:48
  • 1
    @denfromufa -- You should add that to the question then. I'd also add numpy as a tag (instead of dataframe). You may want to remove pandas interpolate from the question too as it doesn't look like that is going to be of any use here. Just suggestions, of course. – JohnE Apr 06 '15 at 13:53
  • I posted this question in https://groups.google.com/forum/#!topic/pydata/ido98vCx86Q – denfromufa Apr 06 '15 at 14:35
0
def multireindex(_df, new_multi_index, method='linear',copy=True):
    #from scipy.interpolate import griddata
    #import numpy as np
    #import pandas as pd
    _points=np.array(_df.index.values.tolist())
    dfn=dict()
    for aclm in _df.columns:
        dfn[aclm] = griddata(_points, _df[aclm], 
                        np.array(new_multi_index), method=method)
    dfn=pd.DataFrame(dfn,index=pd.MultiIndex.from_tuples(
            new_multi_index,names=_df.index.names))
    return pd.concat([dfn,_df])

import pandas as pd
import numpy as np
#import numpy.random as npr
#df1=pd.DataFrame(npr.rand(10,5))
arr1=np.random.rand(100)
arr2=np.random.rand(100)
arr1,arr2=[np.round(a*b) for a,b in 
                zip([arr1,arr2],[100,100,1000])]
df1=pd.DataFrame(zip(arr1,arr2,arr1+arr2,arr1*arr2),columns=['x','y','plus','times'])
df1.set_index(['x','y'],inplace=True)
from scipy.interpolate import griddata
new_points=[(20.0,20.0),(25.0,25.0)]
df2=multireindex(df1,new_points)
df2.head()
denfromufa
  • 5,610
  • 13
  • 81
  • 138
0

There are different ways depending on how many rows do you have.

I used to deal with a dataset with 70 million rows on my MAC Pro(16G RAM). I had to group rows by product_id, client_id and week number to caculate customer's demand. Like your example, this dataset does not have every product of every week. So I try these ways:

  1. Find missing week number of every product, fill in and reindex. It takes too much time and memory to return result, even when i split the dataset into several pieces.

  2. Find missing week number of every product, make a new dataframe, and concat with origin dataframe. More efficient, but still using too much time(several hours) and memory.

  3. After all, I find this post on Stackoverflow. I try unstack the week number, fillna with "-9999"(an non-existed number) in the empty weeks and stack it again. After that I replace "-9999" with np.nan, then I get what I want. It just takes several minutes to make it done. I think it's the right way to do it.

As a conclusion, if you have limited resource, "reindex" could just be used on a small dataset (I used the first way to process a piece with 5 million rows, it returns in minutes), besides "unstack/stack" chould works on bigger dataframe.

Community
  • 1
  • 1
soulcoder
  • 13
  • 4