1

I have a dataframe that includes two columns like the following:

         date        value
0     2017-05-01       1
1     2017-05-08       4
2     2017-05-15       9

each row shows Monday of the week and I have a value only for that specific day. I want to estimate this value for the whole week days until the next Monday, and get the following output:

            date        value
0      2017-05-01       1
1      2017-05-02       1
2      2017-05-03       1
3      2017-05-04       1
4      2017-05-05       1
5      2017-05-06       1
6      2017-05-07       1
7      2017-05-08       4
8      2017-05-09       4
9      2017-05-10       4
10     2017-05-11       4
11     2017-05-12       4
12     2017-05-13       4
13     2017-05-14       4
14     2017-05-15       9
15     2017-05-16       9
16     2017-05-17       9
17     2017-05-18       9
18     2017-05-19       9
19     2017-05-20       9
20     2017-05-21       9

in this link it shows how to select the range in Dataframe but I don't know how to fill the value column as I explained.

mk_sch
  • 1,060
  • 4
  • 16
  • 31

1 Answers1

2

Here is a solution using pandas reindex and ffill:

# Make sure dates is treated as datetime 
df['date'] = pd.to_datetime(df['date'], format = "%Y-%m-%d")

from pandas.tseries.offsets import DateOffset

# Create target dates: all days in the weeks in the original dataframe
new_index = pd.date_range(start=df['date'].iloc[0],
                          end=df['date'].iloc[-1] + DateOffset(6),
                          freq='D')

# Temporarily set dates as index, conform to target dates and forward fill data
# Finally reset the index as in the original df  
out = df.set_index('date')\
        .reindex(new_index).ffill()\
        .reset_index(drop=False)\
        .rename(columns = {'index' : 'date'})

Which gives the expected result:

         date  value
0  2017-05-01    1.0
1  2017-05-02    1.0
2  2017-05-03    1.0
3  2017-05-04    1.0
4  2017-05-05    1.0
5  2017-05-06    1.0
6  2017-05-07    1.0
7  2017-05-08    4.0
8  2017-05-09    4.0
9  2017-05-10    4.0
10 2017-05-11    4.0
11 2017-05-12    4.0
12 2017-05-13    4.0
13 2017-05-14    4.0
14 2017-05-15    9.0
15 2017-05-16    9.0
16 2017-05-17    9.0
17 2017-05-18    9.0
18 2017-05-19    9.0
19 2017-05-20    9.0
20 2017-05-21    9.0
FLab
  • 7,136
  • 5
  • 36
  • 69
  • I am getting TypeError: Can't convert 'datetime.timedelta' object to str implicitly. I try to resolve it first and then run your code. – mk_sch Jul 14 '17 at 08:39
  • I have edited the answer to be sure "date" is trated as datetime – FLab Jul 14 '17 at 08:42
  • Thanks, the new_index work fine, but then I get an error : IndexError: Too many levels: Index has only 1 level, not 2 – mk_sch Jul 14 '17 at 08:52
  • 1
    My bad, I removed "level=1" from reset_index, which was left there as typo – FLab Jul 14 '17 at 09:00