How can I speed up filling in dates in Pandas?

user_1175849 0
Viewed 178

I have a dataframe with four columns: name, account, date, and points

I need to group by name and account, and then fill in the missing dates with the previous days points.

I know how to do this, but I don't know how to do it quickly. My actual data frame is millions and millions of lines.

Here's a simplified version of the problem. I want to get the same output, but make it much, much faster when filling in lots of data.

(The actual data comes from Excel files.)

import pandas as pd

data = """
name  account   date         points
Steve e12       2014-02-07   17
Steve e12       2014-02-09   18

Steve g52       2014-02-03   52
Steve g52       2014-02-06   25
Steve g52       2014-02-08   31
Steve g52       2014-02-09   40

Fred  g21       2014-02-02   17
Fred  g21       2014-02-08   19

Fred  g52       2014-02-07   21
Fred  g52       2014-02-09   18
"""

dates = pd.date_range("2014-02-01", "2014-02-10")

def fill_in_dates(part_df):
    part_df.index = pd.DatetimeIndex(part_df.date)
    part_df = part_df.reindex(dates)
    part_df = part_df.fillna(method='ffill')
    return part_df

lines = [line.strip().split() for line in data.splitlines()[2:] if line.strip()]
columns = data.splitlines()[1].split()
df = pd.DataFrame(lines, columns=columns)

df = df.groupby(['name', 'account'], as_index=False).apply(fill_in_dates)

df = df.dropna()
df = df.reset_index()
df.date = df.level_1
df = df.drop(['level_0', 'level_1'], axis=1)

print(df)

And here's the output:

     name account       date points
0    Fred     g21 2014-02-02     17
1    Fred     g21 2014-02-03     17
2    Fred     g21 2014-02-04     17
3    Fred     g21 2014-02-05     17
4    Fred     g21 2014-02-06     17
5    Fred     g21 2014-02-07     17
6    Fred     g21 2014-02-08     19
7    Fred     g21 2014-02-09     19
8    Fred     g21 2014-02-10     19
9    Fred     g52 2014-02-07     21
10   Fred     g52 2014-02-08     21
11   Fred     g52 2014-02-09     18
12   Fred     g52 2014-02-10     18
13  Steve     e12 2014-02-07     17
14  Steve     e12 2014-02-08     17
15  Steve     e12 2014-02-09     18
16  Steve     e12 2014-02-10     18
17  Steve     g52 2014-02-03     52
18  Steve     g52 2014-02-04     52
19  Steve     g52 2014-02-05     52
20  Steve     g52 2014-02-06     25
21  Steve     g52 2014-02-07     25
22  Steve     g52 2014-02-08     31
23  Steve     g52 2014-02-09     40
24  Steve     g52 2014-02-10     40
pandas-groupby pandas dataframe python
3 Answers

I think your only option is to call groupby and reindex on the date range:

def reindex(g):
    return g.reindex(pd.date_range(g.index.min(), g.index.max()))

df['date'] = pd.to_datetime(df['date'], errors='coerce')
(df.set_index('date')
   .groupby(['name', 'account'])
   .points.apply(reindex)
   .ffill()
   .rename_axis(['name', 'account', 'date'])
   .reset_index())

     name account       date points
0    Fred     g21 2014-02-02     17
1    Fred     g21 2014-02-03     17
2    Fred     g21 2014-02-04     17
3    Fred     g21 2014-02-05     17
4    Fred     g21 2014-02-06     17
5    Fred     g21 2014-02-07     17
6    Fred     g21 2014-02-08     19
7    Fred     g52 2014-02-07     21
8    Fred     g52 2014-02-08     21
9    Fred     g52 2014-02-09     18
10  Steve     e12 2014-02-07     17
11  Steve     e12 2014-02-08     17
12  Steve     e12 2014-02-09     18
13  Steve     g52 2014-02-03     52
14  Steve     g52 2014-02-04     52
15  Steve     g52 2014-02-05     52
16  Steve     g52 2014-02-06     25
17  Steve     g52 2014-02-07     25
18  Steve     g52 2014-02-08     31
19  Steve     g52 2014-02-09     40
user_49090870

I think you can save some time by not doing part_df.index = pd.DatetimeIndex(part_df.date) for each group but at the whole dataframe level. Then do the groupby only on the 'points' column, and do several operation at the same time instead of reassigning df. The whole operation is then:

df = pd.DataFrame(lines, columns=columns)
df = (df.set_index(pd.to_datetime(df.date))
        .groupby(['name', 'account'])['points'].apply(lambda x: x.reindex(dates).ffill())
        .dropna().reset_index().rename(columns={'level_2':'date'}))

and you get the same result. Not sure how much is the improve for your big dataset, but on the example you give it's about 2.4 time faster. It may depend on the number of group you have and the length of dates

user_92747320

Use: 

df.set_index('date')\
  .groupby(['name','account'], as_index=False, group_keys=False)\
  .apply(lambda x: x.reindex(pd.date_range(x.index.min(), 
                                           x.index.max(), freq='D'))
                    .ffill())\
  .reset_index()

Output:

        index   name account points
0  2014-02-02   Fred     g21     17
1  2014-02-03   Fred     g21     17
2  2014-02-04   Fred     g21     17
3  2014-02-05   Fred     g21     17
4  2014-02-06   Fred     g21     17
5  2014-02-07   Fred     g21     17
6  2014-02-08   Fred     g21     19
7  2014-02-07   Fred     g52     21
8  2014-02-08   Fred     g52     21
9  2014-02-09   Fred     g52     18
10 2014-02-07  Steve     e12     17
11 2014-02-08  Steve     e12     17
12 2014-02-09  Steve     e12     18
13 2014-02-03  Steve     g52     52
14 2014-02-04  Steve     g52     52
15 2014-02-05  Steve     g52     52
16 2014-02-06  Steve     g52     25
17 2014-02-07  Steve     g52     25
18 2014-02-08  Steve     g52     31
19 2014-02-09  Steve     g52     40
user_63615310
Related