0

I have a Dataframe in which each row represents a consecutive day and the column represents total electricity consumption. There are some NaN values where data is missing:

     ELECTRICITY
0    10
1    15
2    17
3    12
4    15
5    16
6    22
7    8
8    NaN
9    16
10   13

Because electricity consumption in this sample is mostly affected by day of the week, I want to replace all NaNs with the value from 7 rows earlier or later.

I have investigated the following with no success:

  1. fillna: only allows me to replace with a specific value or immediately adjacent values
  2. interpolate: only allows me to replace with an average of immediately adjacent values
  3. replace: seems to allow conditional replacements with set values

Thanks for any help.

Jon K
  • 3
  • 2
  • Welcome to SO. Could you provide a more complete data example? Is there a date column to your data that can be leveraged or is the data always assumed to be in order? Also you say you want to replace the value "from 7 rows earlier or later". In what cases would you want the value from 7 days earlier and in what cases from 7 days later? Providing what you expect the output to be for the `NaN` in the data you provide would be helpful. – vielkind Sep 19 '18 at 13:49
  • Possible duplicate: https://stackoverflow.com/questions/22081878/get-previous-rows-value-and-calculate-new-column-pandas-python – Hatt Sep 19 '18 at 13:50
  • @vealkind Thanks for the clarifying questions. I tried to simplify the example as much as possible so that my dataset's idiosyncrasies don't make the question irrelevant for others - maybe I went too far! Below are the requested clarifications. As I am new to SO, please advise me if I should rather update the question: (1) Is there a date column: the index is a DatetimeIndex with hourly times (2) When 7 rows earlier / later: replace with 7 rows earlier if 7 rows earlier is not NaN, else 7 days later – Jon K Sep 23 '18 at 08:24

1 Answers1

1

Use fillna and shift

df.fillna(df.shift(7))

    ELECTRICITY
0          10.0
1          15.0
2          17.0
3          12.0
4          15.0
5          16.0
6          22.0
7           8.0
8          15.0
9          16.0
10         13.0
piRSquared
  • 285,575
  • 57
  • 475
  • 624