Pandas .loc saying a value is not in index when I'm pretty sure it isHow to access pandas DataFrame datetime index using stringsUse a list of values to select rows from a pandas dataframeHow to drop rows of Pandas DataFrame whose value in certain columns is NaNSet value for particular cell in pandas DataFrame using indexSelecting a row of pandas series/dataframe by integer indexSelect rows from a DataFrame based on values in a column in pandasDeleting DataFrame row in Pandas based on column valuePretty-print an entire Pandas Series / DataFrameHow to convert index of a pandas dataframe into a column?How to avoid Python/Pandas creating an index in a saved csv?How to check if any value is NaN in a Pandas DataFrame
Is a ccH, ccX and ccH equivalent to a cH, ccX and cH sequence?
2019 2-letters 33-length list
How to idiomatically express the idea "if you can cheat without being caught, do it"
Can I hire several veteran soldiers to accompany me?
Why is the saxophone not common in classical repertoire?
Tricky riddle from sister
How can solar sailed ships be protected from space debris?
Enterprise Layers and Naming Conventions
What is the meaning of ゴト in the context of 鮎
Which are more efficient in putting out wildfires: planes or helicopters?
What's the difference between the Find Steed and Find Greater Steed spells?
How come having a Deathly Hallow is not a big deal?
Is it OK to say "The situation is pregnant with a crisis"?
Are the plates of a battery really charged?
What happened to the Apollo 1 rocket?
Find the closest three-digit hex colour
Does Dhp 256-257 condone judging others?
How can I smooth the top side of this ring?
Is my background sufficient to start Quantum Computing
Crop production in mountains?
What was the point of separating stdout and stderr?
What is the meaning of "it" in "as luck would have it"?
Why will we fail creating a self sustaining off world colony?
Are there advantages in writing by hand over typing out a story?
Pandas .loc saying a value is not in index when I'm pretty sure it is
How to access pandas DataFrame datetime index using stringsUse a list of values to select rows from a pandas dataframeHow to drop rows of Pandas DataFrame whose value in certain columns is NaNSet value for particular cell in pandas DataFrame using indexSelecting a row of pandas series/dataframe by integer indexSelect rows from a DataFrame based on values in a column in pandasDeleting DataFrame row in Pandas based on column valuePretty-print an entire Pandas Series / DataFrameHow to convert index of a pandas dataframe into a column?How to avoid Python/Pandas creating an index in a saved csv?How to check if any value is NaN in a Pandas DataFrame
I'm doing a little bit of math on some indices that I have saved in a CSV file, and I'm getting some behavior from .loc that I can only describe as... strange. When I read this CSV file into a dataframe using Pandas, I see the following:
[1]: import pandas as pd
[2]: df = pd.read_csv(csv_path, parse_dates=True, index_col="Date")
[3]: df = df.apply(pd.to_numeric, errors='coerce') # shouldn't matter
[4]: df.head(5)
Date idx1 idx2 idx3 idx4 idx5
2019-03-22 106.1069 106.6425 106.520 106.45 105.870 ...
2019-03-21 106.6994 107.1746 106.975 106.87 106.145 ...
2019-03-20 106.4900 107.0894 106.875 106.84 106.095 ...
2019-03-19 106.4661 106.9107 106.820 106.71 106.100 ...
2019-03-18 106.5319 107.0137 106.760 106.75 106.100 ...
[5 rows x 53 columns]
When I print the index and index.values I also see the following:
[5]: print df.index
DatetimeIndex(['2019-03-22', '2019-03-21', '2019-03-20', '2019-03-19',
'2019-03-18', '2019-03-15', '2019-03-14', '2019-03-13',
'2019-03-12', '2019-03-11',
...
'2013-02-07', '2013-02-06', '2013-02-05', '2013-02-04',
'2013-02-01', '2013-01-31', '2013-01-30', '2013-01-29',
'2013-01-28', '2013-01-25'],
dtype='datetime64[ns]', name=u'Date', length=1539, freq=None)
[6]: print df.index.values
['2019-03-22T00:00:00.000000000' '2019-03-21T00:00:00.000000000'
'2019-03-20T00:00:00.000000000' ... '2013-01-29T00:00:00.000000000'
'2013-01-28T00:00:00.000000000' '2013-01-25T00:00:00.000000000']
Now here's where it gets weird. If I run the following:
[7]: df.loc["2019-03-21"]
Date idx1 idx2 idx3 idx4 idx5
2019-03-21 106.6994 107.1746 106.975 106.87 106.145
[1 rows x 53 columns]
I get what I expect which is the row corresponding to that date. However, when I run the same exact thing with:
[8]: print df.loc["2019-03-22"]
KeyError: 'the label [2019-03-22] is not in the [index]'
I get a KeyError saying this label is not in the index. I have gone to the actual CSV file to confirm that date is there and I've tried various other .loc dates and have had success with all of them except for 2019-03-22.
Can anyone give me a hint as to what might be going on here? I cannot for the life of me figure out what's going on.
In response to the question from Edeki Okoh below:
print df.index.get_loc("2019-03-22")
[0]
print df.index.get_loc("2019-03-21")
[1]
df.iloc[0]
Out[17]:
idx1 106.107
idx2 106.642
idx3 106.52
idx4 106.45
idx5 105.87
Name: 2019-03-22 00:00:00, dtype: object
python pandas
asked Mar 25 at 16:49
weskpgaweskpga
9047 gold badges17 silver badges37 bronze badges
|
show 4 more comments
I'm doing a little bit of math on some indices that I have saved in a CSV file, and I'm getting some behavior from .loc that I can only describe as... strange. When I read this CSV file into a dataframe using Pandas, I see the following:
[1]: import pandas as pd
[2]: df = pd.read_csv(csv_path, parse_dates=True, index_col="Date")
[3]: df = df.apply(pd.to_numeric, errors='coerce') # shouldn't matter
[4]: df.head(5)
Date idx1 idx2 idx3 idx4 idx5
2019-03-22 106.1069 106.6425 106.520 106.45 105.870 ...
2019-03-21 106.6994 107.1746 106.975 106.87 106.145 ...
2019-03-20 106.4900 107.0894 106.875 106.84 106.095 ...
2019-03-19 106.4661 106.9107 106.820 106.71 106.100 ...
2019-03-18 106.5319 107.0137 106.760 106.75 106.100 ...
[5 rows x 53 columns]
When I print the index and index.values I also see the following:
[5]: print df.index
DatetimeIndex(['2019-03-22', '2019-03-21', '2019-03-20', '2019-03-19',
'2019-03-18', '2019-03-15', '2019-03-14', '2019-03-13',
'2019-03-12', '2019-03-11',
...
'2013-02-07', '2013-02-06', '2013-02-05', '2013-02-04',
'2013-02-01', '2013-01-31', '2013-01-30', '2013-01-29',
'2013-01-28', '2013-01-25'],
dtype='datetime64[ns]', name=u'Date', length=1539, freq=None)
[6]: print df.index.values
['2019-03-22T00:00:00.000000000' '2019-03-21T00:00:00.000000000'
'2019-03-20T00:00:00.000000000' ... '2013-01-29T00:00:00.000000000'
'2013-01-28T00:00:00.000000000' '2013-01-25T00:00:00.000000000']
Now here's where it gets weird. If I run the following:
[7]: df.loc["2019-03-21"]
Date idx1 idx2 idx3 idx4 idx5
2019-03-21 106.6994 107.1746 106.975 106.87 106.145
[1 rows x 53 columns]
I get what I expect which is the row corresponding to that date. However, when I run the same exact thing with:
[8]: print df.loc["2019-03-22"]
KeyError: 'the label [2019-03-22] is not in the [index]'
I get a KeyError saying this label is not in the index. I have gone to the actual CSV file to confirm that date is there and I've tried various other .loc dates and have had success with all of them except for 2019-03-22.
Can anyone give me a hint as to what might be going on here? I cannot for the life of me figure out what's going on.
In response to the question from Edeki Okoh below:
print df.index.get_loc("2019-03-22")
[0]
print df.index.get_loc("2019-03-21")
[1]
df.iloc[0]
Out[17]:
idx1 106.107
idx2 106.642
idx3 106.52
idx4 106.45
idx5 105.87
Name: 2019-03-22 00:00:00, dtype: object
python pandas
asked Mar 25 at 16:49
weskpgaweskpga
9047 gold badges17 silver badges37 bronze badges
Can you use df.index.get_loc('2019-03-22') and use df.index.get_loc('2019-03-21') and tell me what values get returned? Also can you try dr.iloc[0] and tell me which row gets returned? I want to make sure that the first row is actually in the dataframe or if it gets read differently.
– Edeki Okoh
Mar 25 at 16:54
Updated my answer. They still confirm that2019-03-22is in the dataframe's index, which is why this is so confusing.
– weskpga
Mar 25 at 17:29
1
have you try df.loc[datetime(2019,03,22)]
– Frenchy
Mar 25 at 17:49
@Frenchy yes, and that works (that's the workaround I have created for now). But that said, it still shouldn't be the case that using strings would work for every date except for this one, which is what I'm trying to get to the bottom of.
– weskpga
Mar 25 at 18:02
Try passing infer_datetime_format into read_csv. That for some reason when reading the csv it did not recognize the first column as datetime. Thats why once you convert it to datetime you can slice it
– Edeki Okoh
Mar 25 at 18:32
|
show 4 more comments
I'm doing a little bit of math on some indices that I have saved in a CSV file, and I'm getting some behavior from .loc that I can only describe as... strange. When I read this CSV file into a dataframe using Pandas, I see the following:
[1]: import pandas as pd
[2]: df = pd.read_csv(csv_path, parse_dates=True, index_col="Date")
[3]: df = df.apply(pd.to_numeric, errors='coerce') # shouldn't matter
[4]: df.head(5)
Date idx1 idx2 idx3 idx4 idx5
2019-03-22 106.1069 106.6425 106.520 106.45 105.870 ...
2019-03-21 106.6994 107.1746 106.975 106.87 106.145 ...
2019-03-20 106.4900 107.0894 106.875 106.84 106.095 ...
2019-03-19 106.4661 106.9107 106.820 106.71 106.100 ...
2019-03-18 106.5319 107.0137 106.760 106.75 106.100 ...
[5 rows x 53 columns]
When I print the index and index.values I also see the following:
[5]: print df.index
DatetimeIndex(['2019-03-22', '2019-03-21', '2019-03-20', '2019-03-19',
'2019-03-18', '2019-03-15', '2019-03-14', '2019-03-13',
'2019-03-12', '2019-03-11',
...
'2013-02-07', '2013-02-06', '2013-02-05', '2013-02-04',
'2013-02-01', '2013-01-31', '2013-01-30', '2013-01-29',
'2013-01-28', '2013-01-25'],
dtype='datetime64[ns]', name=u'Date', length=1539, freq=None)
[6]: print df.index.values
['2019-03-22T00:00:00.000000000' '2019-03-21T00:00:00.000000000'
'2019-03-20T00:00:00.000000000' ... '2013-01-29T00:00:00.000000000'
'2013-01-28T00:00:00.000000000' '2013-01-25T00:00:00.000000000']
Now here's where it gets weird. If I run the following:
[7]: df.loc["2019-03-21"]
Date idx1 idx2 idx3 idx4 idx5
2019-03-21 106.6994 107.1746 106.975 106.87 106.145
[1 rows x 53 columns]
I get what I expect which is the row corresponding to that date. However, when I run the same exact thing with:
[8]: print df.loc["2019-03-22"]
KeyError: 'the label [2019-03-22] is not in the [index]'
I get a KeyError saying this label is not in the index. I have gone to the actual CSV file to confirm that date is there and I've tried various other .loc dates and have had success with all of them except for 2019-03-22.
Can anyone give me a hint as to what might be going on here? I cannot for the life of me figure out what's going on.
In response to the question from Edeki Okoh below:
print df.index.get_loc("2019-03-22")
[0]
print df.index.get_loc("2019-03-21")
[1]
df.iloc[0]
Out[17]:
idx1 106.107
idx2 106.642
idx3 106.52
idx4 106.45
idx5 105.87
Name: 2019-03-22 00:00:00, dtype: object
python pandas
asked Mar 25 at 16:49
weskpgaweskpga
9047 gold badges17 silver badges37 bronze badges
I'm doing a little bit of math on some indices that I have saved in a CSV file, and I'm getting some behavior from .loc that I can only describe as... strange. When I read this CSV file into a dataframe using Pandas, I see the following:
[1]: import pandas as pd
[2]: df = pd.read_csv(csv_path, parse_dates=True, index_col="Date")
[3]: df = df.apply(pd.to_numeric, errors='coerce') # shouldn't matter
[4]: df.head(5)
Date idx1 idx2 idx3 idx4 idx5
2019-03-22 106.1069 106.6425 106.520 106.45 105.870 ...
2019-03-21 106.6994 107.1746 106.975 106.87 106.145 ...
2019-03-20 106.4900 107.0894 106.875 106.84 106.095 ...
2019-03-19 106.4661 106.9107 106.820 106.71 106.100 ...
2019-03-18 106.5319 107.0137 106.760 106.75 106.100 ...
[5 rows x 53 columns]
When I print the index and index.values I also see the following:
[5]: print df.index
DatetimeIndex(['2019-03-22', '2019-03-21', '2019-03-20', '2019-03-19',
'2019-03-18', '2019-03-15', '2019-03-14', '2019-03-13',
'2019-03-12', '2019-03-11',
...
'2013-02-07', '2013-02-06', '2013-02-05', '2013-02-04',
'2013-02-01', '2013-01-31', '2013-01-30', '2013-01-29',
'2013-01-28', '2013-01-25'],
dtype='datetime64[ns]', name=u'Date', length=1539, freq=None)
[6]: print df.index.values
['2019-03-22T00:00:00.000000000' '2019-03-21T00:00:00.000000000'
'2019-03-20T00:00:00.000000000' ... '2013-01-29T00:00:00.000000000'
'2013-01-28T00:00:00.000000000' '2013-01-25T00:00:00.000000000']
Now here's where it gets weird. If I run the following:
[7]: df.loc["2019-03-21"]
Date idx1 idx2 idx3 idx4 idx5
2019-03-21 106.6994 107.1746 106.975 106.87 106.145
[1 rows x 53 columns]
I get what I expect which is the row corresponding to that date. However, when I run the same exact thing with:
[8]: print df.loc["2019-03-22"]
KeyError: 'the label [2019-03-22] is not in the [index]'
I get a KeyError saying this label is not in the index. I have gone to the actual CSV file to confirm that date is there and I've tried various other .loc dates and have had success with all of them except for 2019-03-22.
Can anyone give me a hint as to what might be going on here? I cannot for the life of me figure out what's going on.
In response to the question from Edeki Okoh below:
print df.index.get_loc("2019-03-22")
[0]
print df.index.get_loc("2019-03-21")
[1]
df.iloc[0]
Out[17]:
idx1 106.107
idx2 106.642
idx3 106.52
idx4 106.45
idx5 105.87
Name: 2019-03-22 00:00:00, dtype: object
python pandas
python pandas
asked Mar 25 at 16:49
weskpgaweskpga
9047 gold badges17 silver badges37 bronze badges
asked Mar 25 at 16:49
weskpgaweskpga
9047 gold badges17 silver badges37 bronze badges
edited Mar 25 at 17:28
weskpga
asked Mar 25 at 16:49
weskpgaweskpga
9047 gold badges17 silver badges37 bronze badges
asked Mar 25 at 16:49
weskpgaweskpga
9047 gold badges17 silver badges37 bronze badges
asked Mar 25 at 16:49
weskpgaweskpga
9047 gold badges17 silver badges37 bronze badges
9047 gold badges17 silver badges37 bronze badges
Can you use df.index.get_loc('2019-03-22') and use df.index.get_loc('2019-03-21') and tell me what values get returned? Also can you try dr.iloc[0] and tell me which row gets returned? I want to make sure that the first row is actually in the dataframe or if it gets read differently.
– Edeki Okoh
Mar 25 at 16:54
Updated my answer. They still confirm that2019-03-22is in the dataframe's index, which is why this is so confusing.
– weskpga
Mar 25 at 17:29
1
have you try df.loc[datetime(2019,03,22)]
– Frenchy
Mar 25 at 17:49
@Frenchy yes, and that works (that's the workaround I have created for now). But that said, it still shouldn't be the case that using strings would work for every date except for this one, which is what I'm trying to get to the bottom of.
– weskpga
Mar 25 at 18:02
Try passing infer_datetime_format into read_csv. That for some reason when reading the csv it did not recognize the first column as datetime. Thats why once you convert it to datetime you can slice it
– Edeki Okoh
Mar 25 at 18:32
|
show 4 more comments
Can you use df.index.get_loc('2019-03-22') and use df.index.get_loc('2019-03-21') and tell me what values get returned? Also can you try dr.iloc[0] and tell me which row gets returned? I want to make sure that the first row is actually in the dataframe or if it gets read differently.
– Edeki Okoh
Mar 25 at 16:54
Updated my answer. They still confirm that2019-03-22is in the dataframe's index, which is why this is so confusing.
– weskpga
Mar 25 at 17:29
1
have you try df.loc[datetime(2019,03,22)]
– Frenchy
Mar 25 at 17:49
@Frenchy yes, and that works (that's the workaround I have created for now). But that said, it still shouldn't be the case that using strings would work for every date except for this one, which is what I'm trying to get to the bottom of.
– weskpga
Mar 25 at 18:02
Try passing infer_datetime_format into read_csv. That for some reason when reading the csv it did not recognize the first column as datetime. Thats why once you convert it to datetime you can slice it
– Edeki Okoh
Mar 25 at 18:32
Can you use df.index.get_loc('2019-03-22') and use df.index.get_loc('2019-03-21') and tell me what values get returned? Also can you try dr.iloc[0] and tell me which row gets returned? I want to make sure that the first row is actually in the dataframe or if it gets read differently.
– Edeki Okoh
Mar 25 at 16:54
Can you use df.index.get_loc('2019-03-22') and use df.index.get_loc('2019-03-21') and tell me what values get returned? Also can you try dr.iloc[0] and tell me which row gets returned? I want to make sure that the first row is actually in the dataframe or if it gets read differently.
– Edeki Okoh
Mar 25 at 16:54
Updated my answer. They still confirm that
2019-03-22 is in the dataframe's index, which is why this is so confusing.– weskpga
Mar 25 at 17:29
Updated my answer. They still confirm that
2019-03-22 is in the dataframe's index, which is why this is so confusing.– weskpga
Mar 25 at 17:29
1
1
have you try df.loc[datetime(2019,03,22)]
– Frenchy
Mar 25 at 17:49
have you try df.loc[datetime(2019,03,22)]
– Frenchy
Mar 25 at 17:49
@Frenchy yes, and that works (that's the workaround I have created for now). But that said, it still shouldn't be the case that using strings would work for every date except for this one, which is what I'm trying to get to the bottom of.
– weskpga
Mar 25 at 18:02
@Frenchy yes, and that works (that's the workaround I have created for now). But that said, it still shouldn't be the case that using strings would work for every date except for this one, which is what I'm trying to get to the bottom of.
– weskpga
Mar 25 at 18:02
Try passing infer_datetime_format into read_csv. That for some reason when reading the csv it did not recognize the first column as datetime. Thats why once you convert it to datetime you can slice it
– Edeki Okoh
Mar 25 at 18:32
Try passing infer_datetime_format into read_csv. That for some reason when reading the csv it did not recognize the first column as datetime. Thats why once you convert it to datetime you can slice it
– Edeki Okoh
Mar 25 at 18:32
|
show 4 more comments
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55342737%2fpandas-loc-saying-a-value-is-not-in-index-when-im-pretty-sure-it-is%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Is this question similar to what you get asked at work? Learn more about asking and sharing private information with your coworkers using Stack Overflow for Teams.
Is this question similar to what you get asked at work? Learn more about asking and sharing private information with your coworkers using Stack Overflow for Teams.
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55342737%2fpandas-loc-saying-a-value-is-not-in-index-when-im-pretty-sure-it-is%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Can you use df.index.get_loc('2019-03-22') and use df.index.get_loc('2019-03-21') and tell me what values get returned? Also can you try dr.iloc[0] and tell me which row gets returned? I want to make sure that the first row is actually in the dataframe or if it gets read differently.
– Edeki Okoh
Mar 25 at 16:54
Updated my answer. They still confirm that
2019-03-22is in the dataframe's index, which is why this is so confusing.– weskpga
Mar 25 at 17:29
1
have you try df.loc[datetime(2019,03,22)]
– Frenchy
Mar 25 at 17:49
@Frenchy yes, and that works (that's the workaround I have created for now). But that said, it still shouldn't be the case that using strings would work for every date except for this one, which is what I'm trying to get to the bottom of.
– weskpga
Mar 25 at 18:02
Try passing infer_datetime_format into read_csv. That for some reason when reading the csv it did not recognize the first column as datetime. Thats why once you convert it to datetime you can slice it
– Edeki Okoh
Mar 25 at 18:32