2

I have a large DataFrame which is indexed by datetime, in particular, by days. I am looking for an efficient function which, for each column, checks the most common non-null value in each week, and outputs a dataframe which is indexed by weeks consisting of these within-week most common values.

Here is an example. The following DataFrame consists of two weeks of daily data:

                        0      1    
2015-11-12 00:00:00     8     nan   
2015-11-13 00:00:00     7     nan   
2015-11-14 00:00:00     nan   5   
2015-11-15 00:00:00     7     nan   
2015-11-16 00:00:00     8     nan   
2015-11-17 00:00:00     7     nan   
2015-11-18 00:00:00     5     nan   
2015-11-19 00:00:00     9     nan   
2015-11-20 00:00:00     8     nan   
2015-11-21 00:00:00     6     nan   
2015-11-22 00:00:00     6     nan   
2015-11-23 00:00:00     6     nan   
2015-11-24 00:00:00     6     nan   
2015-11-25 00:00:00     2     nan

and should be transformed into:

                        0    1    
2015-11-12 00:00:00     7    5
2015-11-19 00:00:00     6    nan

My DataFrame is very large so efficiency is important. Thanks.

EDIT: If possible, can someone suggest a method that would be applicable if the entries are tuples (instead of floats as in my example)?

vestland
  • 55,229
  • 37
  • 187
  • 305
splinter
  • 3,727
  • 8
  • 37
  • 82

1 Answers1

3

You can use resample to group your data by the weekly interval. Then, count the number of occurences via pd.value_counts and select the most common with idxmax:

df.resample("7D").apply(lambda x: x.apply(pd.value_counts).idxmax())

                     0      1
2015-11-12 00:00:00  7.0    5.0
2015-11-19 00:00:00  6.0    NaN

Edit

Here is another numpy version which is faster than the above solution:

def numpy_mode(series):
    values = series.values
    dropped = values[~np.isnan(values)]

    # check for empty array and return NaN
    if not dropped.size:
        return np.NaN

    uniques, counts = np.unique(series.dropna(), return_counts=True)
    return uniques[np.argmax(counts)]

df2.resample("7D").apply(lambda x: x.apply(get_mode))

                     0      1
2015-11-12 00:00:00  7.0    5.0
2015-11-19 00:00:00  6.0    NaN

And here the timings based on the dummy data (for further improvements, have a look here):

%%timeit
df2.resample("7D").apply(lambda x: x.apply(pd.value_counts).idxmax())
>>> 100 loops, best of 3: 18.6 ms per loop

%%timeit 
df2.resample("7D").apply(lambda x: x.apply(get_mode))
>>> 100 loops, best of 3: 3.72 ms per loop

I also tried scipy.stats.mode however it was also slower than the numpy solution:

size = 1000
index = pd.DatetimeIndex(start="2012-12-12", periods=size, freq="D")
dummy = pd.DataFrame(np.random.randint(0, 20, size=(size, 50)), index=index)
print(dummy.head)

            0   1   2   3   4   5   6   7   8   9   ...     40  41  42  43  44  45  46  47  48  49
2012-12-12  18  2   7   1   7   9   16  2   19  19  ...     10  2   18  16  15  10  7   19  9   6
2012-12-13  7   4   11  19  17  10  18  0   10  7   ...     19  11  5   5   11  4   0   16  12  19
2012-12-14  14  0   14  5   1   11  2   19  5   9   ...     2   9   4   2   9   5   19  2   16  2
2012-12-15  12  2   7   2   12  12  11  11  19  5   ...     16  0   4   9   13  5   10  2   14  4
2012-12-16  8   15  2   18  3   16  15  0   14  14  ...     18  2   6   13  19  10  3   16  11  4 

%%timeit
dummy.resample("7D").apply(lambda x: x.apply(get_mode))
>>> 1 loop, best of 3: 926 ms per loop

%%timeit
dummy.resample("7D").apply(lambda x: x.apply(pd.value_counts).idxmax())
>>> 1 loop, best of 3: 5.84 s per loop

%%timeit
dummy.resample("7D").apply(lambda x: stats.mode(x).mode)
>>> 1 loop, best of 3: 1.32 s per loop
Community
  • 1
  • 1
pansen
  • 6,433
  • 4
  • 19
  • 32