Replace the day in a date in to fit the pd.to_datetime format
I have a data frame with multiple columns and multiple rows. In one of these columns there are dates that take the form of mm/dd/yyyy.
I am trying to convert this using df['col'] = pd.to_datetime(df['col']) but am getting the following error because there are multiple records that have 00 in the place of a missing month or day:
ValueError: day is out of range for month
I don't want to do df['col'] = pd.to_datetime(df['col'], errors = 'coerce') because I want to keep whatever data is there.
I would like all the dates that are missing days or months or both (e.g 11/00/2018, 00/13/2018, or 00/00/2018) to have the value 01 where the value is missing (e.g 11/01/2018, 01/13/2018, 01/01/2018).
python pandas string-to-datetime
edited Nov 13 '18 at 22:43
Joel
1,5706719
asked Nov 13 '18 at 22:01
PriyaPriya
1056
add a comment |
I have a data frame with multiple columns and multiple rows. In one of these columns there are dates that take the form of mm/dd/yyyy.
I am trying to convert this using df['col'] = pd.to_datetime(df['col']) but am getting the following error because there are multiple records that have 00 in the place of a missing month or day:
ValueError: day is out of range for month
I don't want to do df['col'] = pd.to_datetime(df['col'], errors = 'coerce') because I want to keep whatever data is there.
I would like all the dates that are missing days or months or both (e.g 11/00/2018, 00/13/2018, or 00/00/2018) to have the value 01 where the value is missing (e.g 11/01/2018, 01/13/2018, 01/01/2018).
python pandas string-to-datetime
edited Nov 13 '18 at 22:43
Joel
1,5706719
asked Nov 13 '18 at 22:01
PriyaPriya
1056
add a comment |
I have a data frame with multiple columns and multiple rows. In one of these columns there are dates that take the form of mm/dd/yyyy.
I am trying to convert this using df['col'] = pd.to_datetime(df['col']) but am getting the following error because there are multiple records that have 00 in the place of a missing month or day:
ValueError: day is out of range for month
I don't want to do df['col'] = pd.to_datetime(df['col'], errors = 'coerce') because I want to keep whatever data is there.
I would like all the dates that are missing days or months or both (e.g 11/00/2018, 00/13/2018, or 00/00/2018) to have the value 01 where the value is missing (e.g 11/01/2018, 01/13/2018, 01/01/2018).
python pandas string-to-datetime
edited Nov 13 '18 at 22:43
Joel
1,5706719
asked Nov 13 '18 at 22:01
PriyaPriya
1056
I have a data frame with multiple columns and multiple rows. In one of these columns there are dates that take the form of mm/dd/yyyy.
I am trying to convert this using df['col'] = pd.to_datetime(df['col']) but am getting the following error because there are multiple records that have 00 in the place of a missing month or day:
ValueError: day is out of range for month
I don't want to do df['col'] = pd.to_datetime(df['col'], errors = 'coerce') because I want to keep whatever data is there.
I would like all the dates that are missing days or months or both (e.g 11/00/2018, 00/13/2018, or 00/00/2018) to have the value 01 where the value is missing (e.g 11/01/2018, 01/13/2018, 01/01/2018).
python pandas string-to-datetime
python pandas string-to-datetime
edited Nov 13 '18 at 22:43
Joel
1,5706719
asked Nov 13 '18 at 22:01
PriyaPriya
1056
edited Nov 13 '18 at 22:43
Joel
1,5706719
asked Nov 13 '18 at 22:01
PriyaPriya
1056
edited Nov 13 '18 at 22:43
Joel
1,5706719
edited Nov 13 '18 at 22:43
Joel
1,5706719
edited Nov 13 '18 at 22:43
Joel
1,5706719
1,5706719
asked Nov 13 '18 at 22:01
PriyaPriya
1056
asked Nov 13 '18 at 22:01
PriyaPriya
1056
asked Nov 13 '18 at 22:01
PriyaPriya
1056
1056
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You could use the following regex to replace 00:
import pandas as pd
data = ['11/00/2018', '00/13/2018', '00/00/2018']
df = pd.DataFrame(data=data, columns=['col'])
replace = df['col'].replace('00/', '01/', regex=True)
result = pd.to_datetime(replace)
print(result)
Output
0 2018-11-01
1 2018-01-13
2 2018-01-01
Name: col, dtype: datetime64[ns]
answered Nov 13 '18 at 22:07
Daniel MesejoDaniel Mesejo
16.8k21430
1
Less susceptible to the issues of the previous answer, but I'm not a fan at all of replacing unknown values with a default value here if this ever has to go forwards for analysis. Not the downvoter, but I really think some consideration needs to be made about how this could skew analysis
– roganjosh
Nov 13 '18 at 22:08
1
Well I believe this does answer what the OP asked or I am mistaken?
– Daniel Mesejo
Nov 13 '18 at 22:09
Hence why I'm not the downvoter :) But this result is likely more harmful than just dropping the data in this case if they actually want to analyse things by date
– roganjosh
Nov 13 '18 at 22:10
add a comment |
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%2f53290164%2freplace-the-day-in-a-date-in-to-fit-the-pd-to-datetime-format%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You could use the following regex to replace 00:
import pandas as pd
data = ['11/00/2018', '00/13/2018', '00/00/2018']
df = pd.DataFrame(data=data, columns=['col'])
replace = df['col'].replace('00/', '01/', regex=True)
result = pd.to_datetime(replace)
print(result)
Output
0 2018-11-01
1 2018-01-13
2 2018-01-01
Name: col, dtype: datetime64[ns]
answered Nov 13 '18 at 22:07
Daniel MesejoDaniel Mesejo
16.8k21430
1
Less susceptible to the issues of the previous answer, but I'm not a fan at all of replacing unknown values with a default value here if this ever has to go forwards for analysis. Not the downvoter, but I really think some consideration needs to be made about how this could skew analysis
– roganjosh
Nov 13 '18 at 22:08
1
Well I believe this does answer what the OP asked or I am mistaken?
– Daniel Mesejo
Nov 13 '18 at 22:09
Hence why I'm not the downvoter :) But this result is likely more harmful than just dropping the data in this case if they actually want to analyse things by date
– roganjosh
Nov 13 '18 at 22:10
add a comment |
You could use the following regex to replace 00:
import pandas as pd
data = ['11/00/2018', '00/13/2018', '00/00/2018']
df = pd.DataFrame(data=data, columns=['col'])
replace = df['col'].replace('00/', '01/', regex=True)
result = pd.to_datetime(replace)
print(result)
Output
0 2018-11-01
1 2018-01-13
2 2018-01-01
Name: col, dtype: datetime64[ns]
answered Nov 13 '18 at 22:07
Daniel MesejoDaniel Mesejo
16.8k21430
1
Less susceptible to the issues of the previous answer, but I'm not a fan at all of replacing unknown values with a default value here if this ever has to go forwards for analysis. Not the downvoter, but I really think some consideration needs to be made about how this could skew analysis
– roganjosh
Nov 13 '18 at 22:08
1
Well I believe this does answer what the OP asked or I am mistaken?
– Daniel Mesejo
Nov 13 '18 at 22:09
Hence why I'm not the downvoter :) But this result is likely more harmful than just dropping the data in this case if they actually want to analyse things by date
– roganjosh
Nov 13 '18 at 22:10
add a comment |
You could use the following regex to replace 00:
import pandas as pd
data = ['11/00/2018', '00/13/2018', '00/00/2018']
df = pd.DataFrame(data=data, columns=['col'])
replace = df['col'].replace('00/', '01/', regex=True)
result = pd.to_datetime(replace)
print(result)
Output
0 2018-11-01
1 2018-01-13
2 2018-01-01
Name: col, dtype: datetime64[ns]
answered Nov 13 '18 at 22:07
Daniel MesejoDaniel Mesejo
16.8k21430
You could use the following regex to replace 00:
import pandas as pd
data = ['11/00/2018', '00/13/2018', '00/00/2018']
df = pd.DataFrame(data=data, columns=['col'])
replace = df['col'].replace('00/', '01/', regex=True)
result = pd.to_datetime(replace)
print(result)
Output
0 2018-11-01
1 2018-01-13
2 2018-01-01
Name: col, dtype: datetime64[ns]
answered Nov 13 '18 at 22:07
Daniel MesejoDaniel Mesejo
16.8k21430
answered Nov 13 '18 at 22:07
Daniel MesejoDaniel Mesejo
16.8k21430
answered Nov 13 '18 at 22:07
Daniel MesejoDaniel Mesejo
16.8k21430
answered Nov 13 '18 at 22:07
Daniel MesejoDaniel Mesejo
16.8k21430
16.8k21430
1
Less susceptible to the issues of the previous answer, but I'm not a fan at all of replacing unknown values with a default value here if this ever has to go forwards for analysis. Not the downvoter, but I really think some consideration needs to be made about how this could skew analysis
– roganjosh
Nov 13 '18 at 22:08
1
Well I believe this does answer what the OP asked or I am mistaken?
– Daniel Mesejo
Nov 13 '18 at 22:09
Hence why I'm not the downvoter :) But this result is likely more harmful than just dropping the data in this case if they actually want to analyse things by date
– roganjosh
Nov 13 '18 at 22:10
add a comment |
1
Less susceptible to the issues of the previous answer, but I'm not a fan at all of replacing unknown values with a default value here if this ever has to go forwards for analysis. Not the downvoter, but I really think some consideration needs to be made about how this could skew analysis
– roganjosh
Nov 13 '18 at 22:08
1
Well I believe this does answer what the OP asked or I am mistaken?
– Daniel Mesejo
Nov 13 '18 at 22:09
Hence why I'm not the downvoter :) But this result is likely more harmful than just dropping the data in this case if they actually want to analyse things by date
– roganjosh
Nov 13 '18 at 22:10
1
1
Less susceptible to the issues of the previous answer, but I'm not a fan at all of replacing unknown values with a default value here if this ever has to go forwards for analysis. Not the downvoter, but I really think some consideration needs to be made about how this could skew analysis
– roganjosh
Nov 13 '18 at 22:08
Less susceptible to the issues of the previous answer, but I'm not a fan at all of replacing unknown values with a default value here if this ever has to go forwards for analysis. Not the downvoter, but I really think some consideration needs to be made about how this could skew analysis
– roganjosh
Nov 13 '18 at 22:08
1
1
Well I believe this does answer what the OP asked or I am mistaken?
– Daniel Mesejo
Nov 13 '18 at 22:09
Well I believe this does answer what the OP asked or I am mistaken?
– Daniel Mesejo
Nov 13 '18 at 22:09
Hence why I'm not the downvoter :) But this result is likely more harmful than just dropping the data in this case if they actually want to analyse things by date
– roganjosh
Nov 13 '18 at 22:10
Hence why I'm not the downvoter :) But this result is likely more harmful than just dropping the data in this case if they actually want to analyse things by date
– roganjosh
Nov 13 '18 at 22:10
add a comment |
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%2f53290164%2freplace-the-day-in-a-date-in-to-fit-the-pd-to-datetime-format%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
