Expand a column of quarterly months with petl and Python

Question

I have a table with quarterly months:

Effective Date

3/31/1981
6/30/1981
9/30/1981
12/31/1981
3/31/1982
6/30/1982
9/30/1982
12/31/1982
3/31/1983
6/30/1983

I'm trying to figure out how to expand these dates and insert the remaining months so that I get a full date column. Been looking into petl and I'm unsure how to "add" a month to each date and insert it in different positions.

score 0 · Answer 1 · answered Jun 06 '18 at 20:28

Can you use pandas and pd.date_range?

import pandas as pd

dates = [
    '3/31/1981',
    '6/30/1981',
    '9/30/1981',
    '12/31/1981',
    '3/31/1982',
    '6/30/1982',
    '9/30/1982',
    '12/31/1982',
    '3/31/1983',
    '6/30/1983'
]

s = pd.to_datetime(dates)

r = pd.date_range(start=min(s), end = max(s), freq='M').strftime('%d/%m/%Y').tolist()

print(r)

Returns:

['31/03/1981', '30/04/1981', '31/05/1981', '30/06/1981', 
'31/07/1981', '31/08/1981', '30/09/1981', '31/10/1981', 
'30/11/1981', '31/12/1981', '31/01/1982', '28/02/1982', 
'31/03/1982', '30/04/1982', '31/05/1982', '30/06/1982', 
'31/07/1982', '31/08/1982', '30/09/1982', '31/10/1982', 
'30/11/1982', '31/12/1982', '31/01/1983', '28/02/1983', 
'31/03/1983', '30/04/1983', '31/05/1983', '30/06/1983']

Expand a column of quarterly months with petl and Python

1 Answers1