How can I extend a data frame with date range in two columns using python?

Question

How do I expand the time range of information in a dataframe as a new data frame.

I have a data frame df with dates, strings and factors similar to this:

 "start"     "end"      "note"   "item"
2016-12-30  2017-01-03    Z        1
2017-09-10  2017-09-14    W        2

I want to expand it as follows.

 "start"      "note"    "item"
2016-12-30     Z         1
2016-12-31     Z         1
2017-01-01     Z         1
2017-01-02     Z         1
2017-01-03     Z         1
2017-09-10     W         2
2017-09-11     W         2
2017-09-12     W         2
2017-09-13     W         2
2017-09-14     W         2

How can I do it using python.

I wonder if there is a fast and efficient way. It shouldn't slow down my code because I will apply this to every row. @DelenaMalan — sumeyyeemir
– sumeyyeemir, Commented Mar 17, 2020 at 12:42

jezrael · Accepted Answer · 2020-03-17 12:43:32Z

2

Use:

#convert columns to datetimes if necessary
df[['start','end']] = df[['start','end']].apply(pd.to_datetime)
#repeat datetimes to Series
s = pd.concat([pd.Series(r.Index,pd.date_range(r.start, r.end)) 
                         for r in df.itertuples()])

#repoeat values, remove end column and reaasign start by index values
df = df.loc[s].drop(['end'], axis=1).assign(start=s.index).reset_index(drop=True)
print (df)
       start note  item
0 2016-12-30    Z     1
1 2016-12-31    Z     1
2 2017-01-01    Z     1
3 2017-01-02    Z     1
4 2017-01-03    Z     1
5 2017-09-10    W     2
6 2017-09-11    W     2
7 2017-09-12    W     2
8 2017-09-13    W     2
9 2017-09-14    W     2

answered Mar 17, 2020 at 12:43

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I extend a data frame with date range in two columns using python?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related