0

how can i have on a new line the number of days for each month between start_date and end_date in pandas? image

this what i have

sea_start_date  sea_end_date
2016-04-04      2016-07-04
2016-07-04      2016-09-04

this is what i want

sea_start_date  sea_end_date
2016-04         2016-05
2016-05         2016-06
2016-06         2016-07
2016-07         2016-08
2016-08         2016-09

Thank you

Roy2012
  • 11,755
  • 2
  • 22
  • 35
Babacar Faye
  • 11
  • 3
  • Welcome to SO. Please include sample data as text in your question, as well as the expected output. Have a look at https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples – Roy2012 Jun 25 '20 at 17:56
  • So basically, you'd like to have a row for every month, starting from the first month and going all the way to the last month in your dataframe? – Roy2012 Jun 25 '20 at 18:41
  • @Roy2012 yes. that is what i want – Babacar Faye Jun 25 '20 at 18:45

1 Answers1

0

I believe this is what you're looking for:

df = df.melt()
df.value = pd.to_datetime(df.value)

min_month = df["value"].min()
max_month = df["value"].max()
min_month = datetime.datetime(min_month.year, min_month.month, 1)

res = pd.DataFrame({"sea_start_date": pd.date_range(min_month, max_month, freq="MS"), 
              "sea_end_date": pd.date_range(min_month, max_month, freq="MS").shift()})

res = res.iloc[:-1]
print(res)

Output:

  sea_start_date sea_end_date
0     2016-04-01   2016-05-01
1     2016-05-01   2016-06-01
2     2016-06-01   2016-07-01
3     2016-07-01   2016-08-01
4     2016-08-01   2016-09-01
Roy2012
  • 11,755
  • 2
  • 22
  • 35