Python: Add 1 every month in dataframe for all columns-CodePudding

I have a dataframe:

            A    B   C
date
2021-01-01  1    nan 1
2021-01-23  nan  1   1
2021-02-03  1    nan 1

How can I add "1" to all columns at the beginning of each month? (Note I also want to do this quarterly as well) The dataframe should end up looking like this:

            A   B   C
date
2021-01-01  2   nan 2
2021-01-23  nan 1   1
2021-02-01  nan 1   1
2021-02-03  1   nan 1

The beginning of the month should have "nan" in the same place as the last instance of the previous month.

CodePudding user response：

IIUC the logic, you could do:

# ensure datetime
df.index = pd.to_datetime(df.index)

# fill missing starts of month
idx = pd.date_range(df.index.min(), df.index.max(), freq='MS')
df = df.reindex(df.index.union(idx))

# update starts of month
prev = df.shift(1).loc[idx] # get last data of previous month
df.loc[idx] = df.loc[idx].add(1).combine_first(prev) # increment/fill

output:

              A    B    C
2021-01-01  2.0  NaN  2.0
2021-01-23  NaN  1.0  1.0
2021-02-01  NaN  1.0  1.0
2021-02-03  1.0  NaN  1.0

CodePudding user response：

df[(df.index.is_month_end) & (df.index >= df.first_valid_index())] =1

Figured it out. And, for quarterly, it would be .is_quarter_end