Styjun

How to repair a laptop's screen hinges?

Do native speakers use ZVE or CPU?

Find values of x so that the matrix is invertible

Won 50K! Now what should I do with it

In which ways do anagamis still experience ignorance?

How do Windows version numbers work?

Is it possible to chain the Dissonant Whispers spell if multiple characters have the War Caster feat?

Why exactly was Star Wars: Clone Wars (2003) excluded from Disney Canon?

Measuring mystery distances

Alternatives to using writing paper for writing practice

As a DM, how to avoid unconscious metagaming when dealing with a high AC character?

Should you avoid redundant information after dialogue?

Extract an attribute value from XML

How to check the quality of an audio sample?

Why does the autopilot disengage even when it does not receive pilot input?

School House Points (Python + SQLite)

If the derivative of a function is square of it then it is constant

Are there J.S. Bach pieces that do not start with the tonic chord?

TikZ Can I draw an arrow by specifying the initial point, direction, and length?

Can I intentionally omit previous work experience or pretend it doesn't exist when applying for jobs?

Would letting a multiclass character rebuild their character to be single-classed be game-breaking?

Bob's unnecessary trip to the shops

Why does Hellboy file down his horns?

How to determine port and starboard on a rotating wheel space station?

changing all dates to standard date time in dataframe

Convert Pandas column with mixed (datetime, and integer) values to datetime onlyHow to convert a given ordinal number (from Excel) to a dateUsing timestamp for indexing a pandas data frame gives value errorHow to get the current time in PythonCompare two dates with JavaScriptHow can I make a time delay in Python?Detecting an “invalid date” Date instance in JavaScriptHow do I get the current date in JavaScript?How do I list all files of a directory?How to format a JavaScript dateGet current time and date on AndroidWhy is subtracting these two times (in 1927) giving a strange result?Select rows from a DataFrame based on values in a column in pandas

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

2

I have a dataframe with date column where it looks like this. There are more than one date column such as end date, fiscal year date etc.

Plan Start Date
8/16/2017 0:00
5/31/2017 0:00
5/31/2017 0:00
5/31/2017 0:00
5/31/2017 0:00
4/21/2016 0:00
2/25/2016 0:00
12/15/2016 0:00
12/15/2016 0:00
12/15/2016 0:00
42373
42373
42367
42367
42367
42367
42460
42460
42460
42460
42460
42759
42333

I am trying to write a function where it basically changes those integrers to appropriate date format and format this column as datetime[64]. this column format is current object type.

I have written below function

def change_date_df(df):
 format_dates_df = [col for col in df.columns if 'Date' in col];
 for date in format_dates_df:
 df[date] = pd.to_datetime(df[date]).apply(lambda x: x.strftime('%d-%m-%y')if not pd.isnull(x) else '');
 return df;

Its giving back now a

ValueError: mixed datetimes and integers in passed array

Im guessing these numbers are not being converted to dates. but Im not sure how else i can adjust my code.

Any idea?

Adam

python pandas date

share|improve this question

asked Jan 10 '18 at 5:12

AdamAdam

30966 silver badges1919 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What should 42333 look like as a date?
  
  – cs95
  Jan 10 '18 at 5:15
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It should look like 11/25/2015
  
  – Adam
  Jan 10 '18 at 5:16
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can you explain how?
  
  – cs95
  Jan 10 '18 at 5:16
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I went to excel and formatted the column to short date. and it shows 11/25/2015
  
  – Adam
  Jan 10 '18 at 5:18
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Possible duplicate of How to convert a given ordinal number (from Excel) to a date
  
  – Vitor Figueredo
  Jan 10 '18 at 5:26
  

  
  
  
  

add a comment | 

2

I have a dataframe with date column where it looks like this. There are more than one date column such as end date, fiscal year date etc.

Plan Start Date
8/16/2017 0:00
5/31/2017 0:00
5/31/2017 0:00
5/31/2017 0:00
5/31/2017 0:00
4/21/2016 0:00
2/25/2016 0:00
12/15/2016 0:00
12/15/2016 0:00
12/15/2016 0:00
42373
42373
42367
42367
42367
42367
42460
42460
42460
42460
42460
42759
42333

I am trying to write a function where it basically changes those integrers to appropriate date format and format this column as datetime[64]. this column format is current object type.

I have written below function

def change_date_df(df):
 format_dates_df = [col for col in df.columns if 'Date' in col];
 for date in format_dates_df:
 df[date] = pd.to_datetime(df[date]).apply(lambda x: x.strftime('%d-%m-%y')if not pd.isnull(x) else '');
 return df;

Its giving back now a

ValueError: mixed datetimes and integers in passed array

Im guessing these numbers are not being converted to dates. but Im not sure how else i can adjust my code.

Any idea?

Adam

python pandas date

share|improve this question

asked Jan 10 '18 at 5:12

AdamAdam

30966 silver badges1919 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What should 42333 look like as a date?
  
  – cs95
  Jan 10 '18 at 5:15
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It should look like 11/25/2015
  
  – Adam
  Jan 10 '18 at 5:16
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can you explain how?
  
  – cs95
  Jan 10 '18 at 5:16
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I went to excel and formatted the column to short date. and it shows 11/25/2015
  
  – Adam
  Jan 10 '18 at 5:18
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Possible duplicate of How to convert a given ordinal number (from Excel) to a date
  
  – Vitor Figueredo
  Jan 10 '18 at 5:26
  

  
  
  
  

add a comment | 

I have a dataframe with date column where it looks like this. There are more than one date column such as end date, fiscal year date etc.

Plan Start Date
8/16/2017 0:00
5/31/2017 0:00
5/31/2017 0:00
5/31/2017 0:00
5/31/2017 0:00
4/21/2016 0:00
2/25/2016 0:00
12/15/2016 0:00
12/15/2016 0:00
12/15/2016 0:00
42373
42373
42367
42367
42367
42367
42460
42460
42460
42460
42460
42759
42333

I am trying to write a function where it basically changes those integrers to appropriate date format and format this column as datetime[64]. this column format is current object type.

I have written below function

def change_date_df(df):
 format_dates_df = [col for col in df.columns if 'Date' in col];
 for date in format_dates_df:
 df[date] = pd.to_datetime(df[date]).apply(lambda x: x.strftime('%d-%m-%y')if not pd.isnull(x) else '');
 return df;

Its giving back now a

ValueError: mixed datetimes and integers in passed array

Im guessing these numbers are not being converted to dates. but Im not sure how else i can adjust my code.

Any idea?

Adam

python pandas date

share|improve this question

asked Jan 10 '18 at 5:12

AdamAdam

30966 silver badges1919 bronze badges

Plan Start Date
8/16/2017 0:00
5/31/2017 0:00
5/31/2017 0:00
5/31/2017 0:00
5/31/2017 0:00
4/21/2016 0:00
2/25/2016 0:00
12/15/2016 0:00
12/15/2016 0:00
12/15/2016 0:00
42373
42373
42367
42367
42367
42367
42460
42460
42460
42460
42460
42759
42333

def change_date_df(df):
 format_dates_df = [col for col in df.columns if 'Date' in col];
 for date in format_dates_df:
 df[date] = pd.to_datetime(df[date]).apply(lambda x: x.strftime('%d-%m-%y')if not pd.isnull(x) else '');
 return df;

ValueError: mixed datetimes and integers in passed array

share|improve this question

asked Jan 10 '18 at 5:12

AdamAdam

30966 silver badges1919 bronze badges

share|improve this question

asked Jan 10 '18 at 5:12

AdamAdam

30966 silver badges1919 bronze badges

asked Jan 10 '18 at 5:12

AdamAdam

30966 silver badges1919 bronze badges

asked Jan 10 '18 at 5:12

AdamAdam

30966 silver badges1919 bronze badges

1 Answer
1

active

oldest

votes

3

Referencing How to convert a given ordinal number (from Excel) to a date, convert the ordinal values to datetime using from_excel_ordinal -

m = df['Plan Start Date'].str.isdigit()

Or, if you have a column of objects -

df['Plan Start Date'].astype(str).str.isdigit()

Next, apply the function on a subset of the rows using apply -

df.loc[m, 'Plan Start Date'] = 
df.loc[m, 'Plan Start Date']
 .astype(int)
 .apply(from_excel_ordinal)

Finally, convert the entire column to datetime using pd.to_datetime, giving a uniform result -

df['Plan Start Date'] = pd.to_datetime(df['Plan Start Date'], errors='coerce')

df

 Plan Start Date
0 2017-08-16
1 2017-05-31
2 2017-05-31
3 2017-05-31
4 2017-05-31
5 2016-04-21
6 2016-02-25
7 2016-12-15
8 2016-12-15
9 2016-12-15
10 2016-01-04
11 2016-01-04
12 2015-12-29
13 2015-12-29
14 2015-12-29
15 2015-12-29
16 2016-03-31
17 2016-03-31
18 2016-03-31
19 2016-03-31
20 2016-03-31
21 2017-01-24
22 2015-11-25

share|improve this answer

edited Jan 10 '18 at 7:19

answered Jan 10 '18 at 5:27

cs95cs95

159k2626 gold badges216216 silver badges285285 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  HIhi i tried this when i did m = df['Plan Start Date'].str.isdigit(), it shows as NaN.
  
  – Adam
  Jan 10 '18 at 7:18
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Adam Okay... I see the problem! Try this: df['Plan Start Date'].astype(str).str.isdigit().
  
  – cs95
  Jan 10 '18 at 7:18
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Excellent. this worked perfectly!
  
  – Adam
  Jan 11 '18 at 7:39
  

  
  
  
  

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
);



);

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f48180685%2fchanging-all-dates-to-standard-date-time-in-dataframe%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

3

Referencing How to convert a given ordinal number (from Excel) to a date, convert the ordinal values to datetime using from_excel_ordinal -

m = df['Plan Start Date'].str.isdigit()

Or, if you have a column of objects -

df['Plan Start Date'].astype(str).str.isdigit()

Next, apply the function on a subset of the rows using apply -

df.loc[m, 'Plan Start Date'] = 
df.loc[m, 'Plan Start Date']
 .astype(int)
 .apply(from_excel_ordinal)

Finally, convert the entire column to datetime using pd.to_datetime, giving a uniform result -

df['Plan Start Date'] = pd.to_datetime(df['Plan Start Date'], errors='coerce')

df

 Plan Start Date
0 2017-08-16
1 2017-05-31
2 2017-05-31
3 2017-05-31
4 2017-05-31
5 2016-04-21
6 2016-02-25
7 2016-12-15
8 2016-12-15
9 2016-12-15
10 2016-01-04
11 2016-01-04
12 2015-12-29
13 2015-12-29
14 2015-12-29
15 2015-12-29
16 2016-03-31
17 2016-03-31
18 2016-03-31
19 2016-03-31
20 2016-03-31
21 2017-01-24
22 2015-11-25

share|improve this answer

edited Jan 10 '18 at 7:19

answered Jan 10 '18 at 5:27

cs95cs95

159k2626 gold badges216216 silver badges285285 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  HIhi i tried this when i did m = df['Plan Start Date'].str.isdigit(), it shows as NaN.
  
  – Adam
  Jan 10 '18 at 7:18
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Adam Okay... I see the problem! Try this: df['Plan Start Date'].astype(str).str.isdigit().
  
  – cs95
  Jan 10 '18 at 7:18
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Excellent. this worked perfectly!
  
  – Adam
  Jan 11 '18 at 7:39
  

  
  
  
  

add a comment | 

3

Referencing How to convert a given ordinal number (from Excel) to a date, convert the ordinal values to datetime using from_excel_ordinal -

m = df['Plan Start Date'].str.isdigit()

Or, if you have a column of objects -

df['Plan Start Date'].astype(str).str.isdigit()

Next, apply the function on a subset of the rows using apply -

df.loc[m, 'Plan Start Date'] = 
df.loc[m, 'Plan Start Date']
 .astype(int)
 .apply(from_excel_ordinal)

Finally, convert the entire column to datetime using pd.to_datetime, giving a uniform result -

df['Plan Start Date'] = pd.to_datetime(df['Plan Start Date'], errors='coerce')

df

 Plan Start Date
0 2017-08-16
1 2017-05-31
2 2017-05-31
3 2017-05-31
4 2017-05-31
5 2016-04-21
6 2016-02-25
7 2016-12-15
8 2016-12-15
9 2016-12-15
10 2016-01-04
11 2016-01-04
12 2015-12-29
13 2015-12-29
14 2015-12-29
15 2015-12-29
16 2016-03-31
17 2016-03-31
18 2016-03-31
19 2016-03-31
20 2016-03-31
21 2017-01-24
22 2015-11-25

share|improve this answer

edited Jan 10 '18 at 7:19

answered Jan 10 '18 at 5:27

cs95cs95

159k2626 gold badges216216 silver badges285285 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  HIhi i tried this when i did m = df['Plan Start Date'].str.isdigit(), it shows as NaN.
  
  – Adam
  Jan 10 '18 at 7:18
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Adam Okay... I see the problem! Try this: df['Plan Start Date'].astype(str).str.isdigit().
  
  – cs95
  Jan 10 '18 at 7:18
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Excellent. this worked perfectly!
  
  – Adam
  Jan 11 '18 at 7:39
  

  
  
  
  

add a comment | 

Referencing How to convert a given ordinal number (from Excel) to a date, convert the ordinal values to datetime using from_excel_ordinal -

m = df['Plan Start Date'].str.isdigit()

Or, if you have a column of objects -

df['Plan Start Date'].astype(str).str.isdigit()

Next, apply the function on a subset of the rows using apply -

df.loc[m, 'Plan Start Date'] = 
df.loc[m, 'Plan Start Date']
 .astype(int)
 .apply(from_excel_ordinal)

Finally, convert the entire column to datetime using pd.to_datetime, giving a uniform result -

df['Plan Start Date'] = pd.to_datetime(df['Plan Start Date'], errors='coerce')

df

 Plan Start Date
0 2017-08-16
1 2017-05-31
2 2017-05-31
3 2017-05-31
4 2017-05-31
5 2016-04-21
6 2016-02-25
7 2016-12-15
8 2016-12-15
9 2016-12-15
10 2016-01-04
11 2016-01-04
12 2015-12-29
13 2015-12-29
14 2015-12-29
15 2015-12-29
16 2016-03-31
17 2016-03-31
18 2016-03-31
19 2016-03-31
20 2016-03-31
21 2017-01-24
22 2015-11-25

share|improve this answer

edited Jan 10 '18 at 7:19

answered Jan 10 '18 at 5:27

cs95cs95

159k2626 gold badges216216 silver badges285285 bronze badges

m = df['Plan Start Date'].str.isdigit()

df['Plan Start Date'].astype(str).str.isdigit()

df.loc[m, 'Plan Start Date'] = 
df.loc[m, 'Plan Start Date']
 .astype(int)
 .apply(from_excel_ordinal)

df['Plan Start Date'] = pd.to_datetime(df['Plan Start Date'], errors='coerce')

df

 Plan Start Date
0 2017-08-16
1 2017-05-31
2 2017-05-31
3 2017-05-31
4 2017-05-31
5 2016-04-21
6 2016-02-25
7 2016-12-15
8 2016-12-15
9 2016-12-15
10 2016-01-04
11 2016-01-04
12 2015-12-29
13 2015-12-29
14 2015-12-29
15 2015-12-29
16 2016-03-31
17 2016-03-31
18 2016-03-31
19 2016-03-31
20 2016-03-31
21 2017-01-24
22 2015-11-25

share|improve this answer

edited Jan 10 '18 at 7:19

answered Jan 10 '18 at 5:27

cs95cs95

159k2626 gold badges216216 silver badges285285 bronze badges

answered Jan 10 '18 at 5:27

cs95cs95

159k2626 gold badges216216 silver badges285285 bronze badges

answered Jan 10 '18 at 5:27

cs95cs95

159k2626 gold badges216216 silver badges285285 bronze badges