resampling a pandas dataframe and filling new rows with zero-CodePudding

I have a time series as a dataframe. The first column is the week number, the second are values for that week. The first week (22) and the last week (48), are the lower and upper bounds of the time series. Some weeks are missing, for example, there is no week 27 and 28. I would like to resample this series such that there are no missing weeks. Where a week was inserted, I would like the corresponding value to be zero. This is my data:

week    value
0   22  1
1   23  2
2   24  2
3   25  3
4   26  2
5   29  3
6   30  3
7   31  3
8   32  7
9   33  4
10  34  5
11  35  4
12  36  2
13  37  3
14  38  10
15  39  5
16  40  7
17  41  10
18  42  11
19  43  15
20  44  9
21  45  13
22  46  5
23  47  6
24  48  2

I am wondering if this can be achieved in Pandas without creating a loop from scratch. I have looked into pd.resample, but can't achieve the results I am looking for.

CodePudding user response：

I would set week as index, reindex with fill_value option:

start, end = df['week'].agg(['min','max'])

df.set_index('week').reindex(np.arange(start, end 1), fill_value=0).reset_index()

Output (head):

    week  value
0     22      1
1     23      2
2     24      2
3     25      3
4     26      2
5     27      0
6     28      0
7     29      3
8     30      3