Just create a column with all days of one month - with pandas

Question

I really try for more than 4 hour to do a simple task: Create a column in a df with pandas that represent from day one to last day of the month. For example:

index date
0     2018-08-01
1     2018-08-02
2     2018-08-03
...   ...

I'm giving up and do this in excel, save in csv to import again as a df in pandas... thanks for any help!

Answer 1

Ok, sure... I could not hold myself to write an answer to your question. When you say all the days in one month I directly think of: how do we get the last day? And the answer to that is using an offset (which is build-in in Pandas already).

Apart from that you are instersted for what in Pandas is called a date_range. There are numerous options to create a date range with for instance intervals and frequency (defaults to day). But to make it clear let us pass three variables:

1. start : our start date as a timeobject
2. end : our end date as a timeobject. Construct it with pandas offset Monthsend
3. frequency : day (this is optional but makes it clear that we want days)

Note: The reason the offset helps is to easily pass the end date as the days of the month can vary.

Consider this example:

import pandas as pd

month = '2018-08'

df = pd.DataFrame({
    'date': pd.date_range(
        start = pd.Timestamp(month),                        
        end = pd.Timestamp(month) + pd.offsets.MonthEnd(0),  # <-- 2018-08-31 with MonthEnd
        freq = 'D'
    )
})

date_range is powerful, and if you want every hour you could do something like this:

df = pd.DataFrame({
    'date': pd.date_range(
        start = pd.Timestamp(month),
        end = pd.Timestamp(month) + pd.offsets.MonthEnd(0) + pd.Timedelta(days=1),
        freq = 'H',     # <--- try '3h', '6h', '12h' if you want
        closed = 'left'
    )
})

Answer 2

Create a daily datetime index using the start and end times from a period with monthly frequency.

month = '2018-08'
period = pd.Period(month, freq='M')
>>> pd.Series(
        pd.DatetimeIndex(
            start=period.start_time, 
            end=period.end_time, 
            freq='D')
    ).to_frame('date')
     date
0    2018-08-01
1    2018-08-02
2    2018-08-03
...
30   2018-08-31