create DF with all possible values for week in pandas

2518 views python
2

I have a dataframe that looks like this

id    week count
1       1    2
1       2    2
2       3    1
3       5    4

I want a df that will have each id with all the possible values of week (1-5):

id    week  count
1       1     2
1       2     2
1       3     null
1       4     null
1       5     null

answered question

Welcome to StackOverflow. Please read and follow the posting guidelines in the help documentation, as suggested when you created this account. On topic, how to ask, and ... the perfect question apply here. StackOverflow is not a design, coding, research, or tutorial resource. However, if you follow whatever resources you find on line, make an honest coding attempt, and run into a problem, you'd have a good example to post.

1 Answer

1

I would just create a new dataframe with all id values repeated 5 times, and the range of 1-5 repeated n times, n being the number of id values. Then, do an outer merge with your original dataframe:

new_df = (pd.merge(pd.DataFrame({'id':np.repeat(df['id'].unique(), 5),
                                 'week':np.tile([1,2,3,4,5], df['id'].nunique())}),
                   df, how='outer'))

    id  week  count
0    1     1    2.0
1    1     2    2.0
2    1     3    NaN
3    1     4    NaN
4    1     5    NaN
5    2     1    NaN
6    2     2    NaN
7    2     3    1.0
8    2     4    NaN
9    2     5    NaN
10   3     1    NaN
11   3     2    NaN
12   3     3    NaN
13   3     4    NaN
14   3     5    4.0

posted this

Have an answer?

JD

Please login first before posting an answer.