Create a row where sequence of month is Missing

Question

I need a help to build Dataframe in python where if a sequence of month is missing it will create a new row with Monthly Revenue as 0. the check for sequence of Month will be till Total Age of particular VIN. Below are the Dataframes.

CurrentDf:

VIN	Total Age	Months	Monthly Revenue
v1	10	00	1513
v1	10	05	1108
v1	10	07	4330
v1	10	09	7121
v2	08	01	1998
v2	08	04	4997
v2	08	05	8528
v2	08	06	1783
v2	08	07	9628
v2	08	08	2082

OutputDf:

VIN	Total Age	Months	Monthly Revenue
v1	10	00	1513
v1	10	01	00
v1	10	02	00
v1	10	03	00
v1	10	04	00
v1	10	05	1108
v1	10	06	00
v1	10	07	433
v1	10	08	00
v1	10	09	7121
v1	10	10	00
V2	08	00	00
v2	08	01	1998
v2	08	02	00
v2	08	03	00
v2	08	04	4997
v2	08	05	8528
v2	08	06	1783
v2	08	07	9628
v2	08	08	2082

user_10035985 · Answer

IIUC:

def reindexer(x):
    x = (
        x.reset_index()
        .set_index("Months")
        .reindex(range(0, x["Total Age"].max() + 1))
    )
    x["Total Age"] = x["Total Age"].ffill().bfill().astype(int)
    x["Monthly Revenue"] = x["Monthly Revenue"].fillna(0)
    return x


x = (
    df.groupby("VIN")
    .apply(reindexer)
    .drop(columns=["index", "VIN"])
    .reset_index()
)
print(x.to_markdown())

Prints:

	VIN	Months	Total Age	Monthly Revenue
0	v1	0	10	1513
1	v1	1	10	0
2	v1	2	10	0
3	v1	3	10	0
4	v1	4	10	0
5	v1	5	10	1108
6	v1	6	10	0
7	v1	7	10	4330
8	v1	8	10	0
9	v1	9	10	7121
10	v1	10	10	0
11	v2	0	8	0
12	v2	1	8	1998
13	v2	2	8	0
14	v2	3	8	0
15	v2	4	8	4997
16	v2	5	8	8528
17	v2	6	8	1783
18	v2	7	8	9628
19	v2	8	8	2082

^{Updated that Months doesn't exceed Total Age}

Create a row where sequence of month is Missing

1 Answers