Replace values in Pandas DataFrame with a DateTime Index based on a list of dates

Question

I have a dataframe of values and a list of dates. E.g.,

data = pd.DataFrame([1,3,5,7,2,3,9,1,3,8,4,5],index=pd.date_range(start='2017-01-01',periods=12),columns=['values'])

I want to replace the value of a date in the date list with a zero value. E.g.,

date_list = ['2017-01-04', '2017-01-07', '2017-01-10']

I have tried:

data[date_list] == 0

but this yields an error:

KeyError: "None of [['2017-01-04', '2017-01-07', '2017-01-10']] are in the [index]"

Does anyone have an idea of how to solve this? I have a very large dataframe and date list...

Zero · Accepted Answer · 2017-07-18 10:56:55Z

3

Another way,

In [11]: data[data.index.isin(date_list)] = 0

In [12]: data
Out[12]:
            values
2017-01-01       1
2017-01-02       3
2017-01-03       5
2017-01-04       0
2017-01-05       2
2017-01-06       3
2017-01-07       0
2017-01-08       1
2017-01-09       3
2017-01-10       0
2017-01-11       4
2017-01-12       5

answered Jul 18, 2017 at 10:56

Zero

77.4k22 gold badges153 silver badges153 bronze badges

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

Comments

score 2 · Accepted Answer · 2017-07-18 11:42:51Z

2

You need to convert that list to datetime and use the loc indexer:

data.loc[pd.to_datetime(date_list)] = 0

data
Out[19]: 
            values
2017-01-01       1
2017-01-02       3
2017-01-03       5
2017-01-04       0
2017-01-05       2
2017-01-06       3
2017-01-07       0
2017-01-08       1
2017-01-09       3
2017-01-10       0
2017-01-11       4
2017-01-12       5

This works because the DataFrame has only one column. This sets all the columns to zero. But as jezrael pointed out, if you only want to set the values column to zero, you need to specify that:

data.loc[pd.to_datetime(date_list), 'values'] = 0

edited Jul 18, 2017 at 11:42

answered Jul 18, 2017 at 10:56

user2285236

3 Comments

jezrael Over a year ago

In my opinion better solution, maybe if need specify columns data.loc[pd.to_datetime(date_list), 'values'] = 0

user2285236 Over a year ago

@jezrael ah yes I do that mistake all the time. Thanks.

jezrael Over a year ago

No, I think your solution is fine if need replace all columns. It depends of OP ;)

Collectives™ on Stack Overflow

Replace values in Pandas DataFrame with a DateTime Index based on a list of dates

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related