Complete date series grouped by another column in pandas and fill missing rows

user_8152837 0
Viewed 46

I have data set with a particular ID. For particular ID I want to complete the date series. So, for ex: if I have the max date in dataset as : '2020-06-01'(YYYY-MM-DD) and min date as : '2020-03-01' How do I fill missing rows while filling in value of attribute columns

    ID  sale_month   attribute1    attribute2
0   1   2020-06-01    blue              1
1   1   2020-05-01    blue              2
2   1   2020-04-01    blue              3
3   1   2020-03-01    blue              4
4   2   2020-05-01    yellow            5
5   2   2020-04-01    yellow            4
6   2   2020-03-01    yellow            3
7   3   2020-05-01    green             7
8   3   2020-04-01    green             8

I'd like to achieve this :

    ID  sale_month   attribute1    attribute2
0   1   2020-06-01    blue              1
1   1   2020-05-01    blue              2
2   1   2020-04-01    blue              3
3   1   2020-03-01    blue              4
4   2   2020-06-01    yellow            6
5   2   2020-05-01    yellow            5
6   2   2020-04-01    yellow            4
7   2   2020-03-01    yellow            3
8   3   2020-06-01    green             6
9   3   2020-05-01    green             7
10  3   2020-04-01    green             8
11  3   2020-03-01    green             9

I can do it when there's no ID-based grouping by indexing on the date column and then reindexing and using interpolation to fill in values. How do I do that here with the grouping column? And also, can I use different methods of interpolation for different columns. For example, duplicating attribute1, but linear interpolation in attribute2.

To duplicate the data frame :

df = pd.DataFrame({'ID': [1, 1, 1, 1, 2, 2, 2, 3, 3], 
                   'sale_month' : ['2020-06-01', '2020-05-01', '2020-04-01', '2020-03-01', '2020-05-01', '2020-04-01', '2020-03-01', '2020-05-01', '2020-04-01'],
                   'attribute1': [ 'blue', 'blue', 'blue', 'blue', 'yellow', 'yellow', 'yellow', 'green', 'green'],
                   'attribute2' : [1, 2, 3, 4, 5, 4, 3, 7, 8 ]})
df.sale_month = pd.to_datetime(df.sale_month)
pandas date python
1 Answers

You can use MultiIndex.from_product with pd.date_range:

dates = pd.date_range(df["sale_month"].min(), df["sale_month"].max(), freq="MS")

s = pd.MultiIndex.from_product([df["ID"].unique(), reversed(dates)],names=df.columns[:2])

df = df.set_index(["ID","sale_month"]).reindex(s).reset_index()

df["attribute1"] = df.groupby('ID')["attribute1"].transform("first")

print (df)

    ID sale_month attribute1  attribute2
0    1 2020-06-01       blue         1.0
1    1 2020-05-01       blue         2.0
2    1 2020-04-01       blue         3.0
3    1 2020-03-01       blue         4.0
4    2 2020-06-01     yellow         NaN
5    2 2020-05-01     yellow         5.0
6    2 2020-04-01     yellow         4.0
7    2 2020-03-01     yellow         3.0
8    3 2020-06-01      green         NaN
9    3 2020-05-01      green         7.0
10   3 2020-04-01      green         8.0
11   3 2020-03-01      green         NaN
user_92844230
Related