CODEWITHSUNDEEP
pythonpandas
oceanbeach96
oceanbeach96

Reputation: 634

Creating the same value in column, up to the next date

I am wanting to extend the value in the Rating column for each date in a year, up to the next date when a Rating changes. For example, on 1999-10-25, the Rating is 1, it is not until 2000-03-01 that the rating changes to 2. How would I create a seperate DataFrame that simply has 1 in the Rating column from 1999-10-25 up to 2000-02-29, then 2 in the Rating column from 2000-03-01 until 2000-04-17 etc. Any help would be awesome! Thanks :)

      Date  Company Name  Rating
2018-02-26  7-Eleven Inc     1 
2018-01-31  7-Eleven Inc     2 
2011-09-16  7-Eleven Inc     1
2002-04-22  7-Eleven Inc     4 
2002-02-15  7-Eleven Inc     2
2000-04-17  7-Eleven Inc     3
2000-03-01  7-Eleven Inc     2
1999-10-25  7-Eleven Inc     1

Upvotes: 2

Views: 27

Answers (1)

jezrael
jezrael

Reputation: 862671

Use DataFrame.resample with Resampler.ffill, but because sorting by default is added for descending sort DataFrame.sort_index and last convert index to column:

df['Date'] = pd.to_datetime(df['Date'])

df1 = (df.set_index('Date')
         .resample('d')
         .ffill()
         .sort_index(ascending=False)
         .reset_index())
print (df1)
           Date   Company Name  Rating
0    2018-02-26  7-Eleven  Inc       1
1    2018-02-25  7-Eleven  Inc       2
2    2018-02-24  7-Eleven  Inc       2
3    2018-02-23  7-Eleven  Inc       2
4    2018-02-22  7-Eleven  Inc       2
        ...       ...  ...     ...
6695 1999-10-29  7-Eleven  Inc       1
6696 1999-10-28  7-Eleven  Inc       1
6697 1999-10-27  7-Eleven  Inc       1
6698 1999-10-26  7-Eleven  Inc       1
6699 1999-10-25  7-Eleven  Inc       1

[6700 rows x 4 columns]

Upvotes: 2

Related Questions