Warm tip: This article is reproduced from stackoverflow.com, please click
dataframe pandas python data-science missing-data

Fill in zeros from data of past week (168 indexes prior) in a pandas dataframe

发布于 2020-03-27 15:41:49

I have data of electricity usage. During the power outrages the data is '0'. I want to replace those 0's with the data of same time during the past week. Which is 168 indexes ahead or behind in the dataset.

In the below code, I am saving the index of all the zeros. Running a loop which will place the value that lies 168 indexes ahead in the dataset at the current index.

Index_Zero = data[data["Total"]==0].index.to_list() #Output = list of indexes where all the zeros lie

print(Index_Zero[0]) #Output = 2

for i in Index_Zero:
    data.loc[(Index_Zero[i]), 'Total']=data.loc[(Index_Zero[i+168]), 'Total']

Also, if I print

data.loc[(Index_Zero[0]), 'Total']=data.loc[(Index_Zero[2]), 'Total']
print(data.loc[(Index_Zero[0]), 'Total'])
Output: 0.0

DataSet:

           Date         Time     Total
0     23-Jan-2019  12:00:00 AM  18343.00
1     23-Jan-2019  01:00:00 AM  18188.00
2     23-Jan-2019  02:00:00 AM      0.00
3     23-Jan-2019  03:00:00 AM  23394.00
4     23-Jan-2019  04:00:00 AM  20037.00
Questioner
AyyBeeShafi
Viewed
21
AyyBeeShafi 2020-02-05 18:54

The problem was in the range of for loop. It was iterating beyond the list.

Index_Zero = data[data["Total"]==0].index.to_list()
for items in range(0, len(Index_Zero)-1):
    data.loc[(Index_Zero[items]), 'Total'] = data.loc[(items+168), 'Total']