1

I have the below data. I need to fill in the data for the remaining months

enter image description here

I need only the first day [day one] of the month to be filled in. Wherever there is no data, I need the value to be filled with '0'.

For example below is the existing data

       uname        month_first     msg_count
0     ArtCort0324   2017-06-01      9

I need output in below way.

enter image description here

Improve this question
2
  • Posting image of the data or expected output makes it impossible to reproduce the example. Please revert to original expected output Commented Jan 30, 2019 at 18:37
  • I couldn't post the output with all indentations. Hence had to opt this way Commented Jan 30, 2019 at 23:34

1 Answer 1

Reset to default
2

Create a multiindex from combination of unman and date range and reindex the data

df.month_first = pd.to_datetime(df.month_first)

dates = pd.date_range(datetime.datetime(df.month_first.dt.year.min(), 1, 1),datetime.datetime(df.month_first.dt.year.max(), 12, 1), freq = 'MS')

idx = pd.MultiIndex.from_product([df.uname.unique(), dates], names = ['uname','month_first'])

df.set_index(['uname', 'month_first']).reindex(idx).fillna(0).astype(int).reset_index()

uname   month_first msg_count
0   ArtCort0324 2017-01-01  0
1   ArtCort0324 2017-02-01  0
2   ArtCort0324 2017-03-01  0
3   ArtCort0324 2017-04-01  0
4   ArtCort0324 2017-05-01  0
5   ArtCort0324 2017-06-01  9
6   ArtCort0324 2017-07-01  0
7   ArtCort0324 2017-08-01  0
8   ArtCort0324 2017-09-01  0
9   ArtCort0324 2017-10-01  0
10  ArtCort0324 2017-11-01  0
11  ArtCort0324 2017-12-01  0
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.