I have a dataframe that looks like this:
dict_1 = {"Code" : ['A', 'A', 'A', 'A', 'A', 'A'],
'Period' : ['2022-04-29','2022-04-29', '2022-04-30', '2022-05-01', '2022-05-01', '2022-05-01']}
df_1 = pd.DataFrame(dict_1)
df_1['Period'] = pd.to_datetime(df_1['Period']).dt.strftime("%Y-%m-%d")
df_1.head(10)
| Code | Period |
|---|---|
| A | 2022-04-29 |
| A | 2022-04-29 |
| A | 2022-04-30 |
| A | 2022-05-01 |
| A | 2022-05-01 |
| A | 2022-05-01 |
I have to create a new column, i.e., if the month ends then Count should start from 1.
Below is the code that I have tried at my end.
df_2 = df_1.groupby(['Period', 'Code'], as_index=False).size() df_2.head()
| Code | Period | size |
|---|---|---|
| A | 2022-04-29 | 2 |
| A | 2022-04-30 | 1 |
| A | 2022-05-01 | 3 |
def Cumulative(lists):
cu_list = []
length = len(lists)
cu_list = [sum(lists[0:x:1]) for x in range(0, length+1)]
return cu_list[1:]
df_2['Count'] = Cumulative(df_2['size'])
df_2.head()
| Code | Period | size | Count |
|---|---|---|---|
| A | 2022-04-29 | 2 | 2 |
| A | 2022-04-30 | 1 | 3 |
| A | 2022-05-01 | 3 | 6 |
For the row with a Period of 2022-05-01, the total count should be 3 instead of 6 because a new month has started.
Advertisement
Answer
Use groupby on the month (and year to be safe) information from Period and apply cumsum:
year_col = pd.to_datetime(df_2['Period']).dt.year month_col = pd.to_datetime(df_2['Period']).dt.month df_2['count'] = df_2.groupby([year_col, month_col])['size'].cumsum()
Result:
Period Code size count 0 2022-04-29 A 2 2 1 2022-04-30 A 1 3 2 2022-05-01 A 3 3