Styjun

Don't look at what I did there

How do I keep my animals from eating my people food?

Why does the U.S. military maintain their own weather satellites?

How do I get my neighbour to stop disturbing with loud music?

Understanding data transmission rates over copper wire

Why do presidential pardons exist in a country having a clear separation of powers?

Calculate Landau's function

'Horseshoes' for Deer?

Storing milk for long periods of time

Welche normative Autorität hat der Duden? / What's the normative authority of the Duden?

I was reported to HR as being a satan worshiper

How to investigate an unknown 1.5GB file named "sudo" in my Linux home directory?

Why are JWST optics not enclosed like HST?

Is the word 'mistake' a concrete or abstract noun?

Can I lend a small amount of my own money to a bank at the federal funds rate?

What caused the end of cybernetic implants?

Eshet Chayil in the Tunisian service

What are the in-game differences between WoW Classic and the original 2006 Version

Why does Sauron not permit his followers to use his name?

Find the logic in first 2 statements to give the answer for the third statement

What was Captain Marvel supposed to do once she reached her destination?

Can inductive kick be discharged without freewheeling diode, in this example?

LWC: Is it safe to rely on window.location.href to get the page url?

How to understand payment due date for credit card?

Formatting Date To MM/DD/YYY From xlsb File

Number to Date Conversion using Pandas in Python?How do I check whether a file exists without exceptions?How to return only the Date from a SQL Server DateTime datatypeCompare two dates with JavaScriptWhere can I find documentation on formatting a date in JavaScript?Detecting an “invalid date” Date instance in JavaScriptYYYY-MM-DD format date in shell scriptHow do I get the current date in JavaScript?How do I list all files of a directory?How to format a JavaScript date“Large data” work flows using pandas

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

0

I have a .xlsb file that I want to use pandas and analyse. I have found how to use pyxlsb to open the file and create another dataset. However, the problem now is that the time formats have changed into a different number format (e.g 41256).

The code I'm using at the moment is:

dataset = []

with open_xlsb(file) as wb: #opening an xlsb file workbook
 with wb.get_sheet(1) as sheet1:
 for row in sheet1.rows():
 dataset.append([item.v for item in row])

dataset= pd.DataFrame(dataset[1:], columns=dataset[0])

I have already tried the convert_date as follows:

convert_date(dataset)

I have also tried the to_datetime function, but unsure if I used it correctly. For reference, the dataset I am using has dates in multiple columns and rows so I'm looking for a way to covert all of them into the right format, whilst ignoring any errors.

EDIT: So I don't have a single column with "Date", rather I have multiple columns, such as StartDate, EndDate, Last Updated and a few others. The result I want to see is if I go to a column, e.g dataset.columns['StartDate'], I want to get a date value, such as 15/03/2019, as opposed to 42156.

Any help would be much appreciated!

python pandas date

share|improve this question

edited Mar 27 at 23:45

JollyKinG

asked Mar 27 at 23:12

JollyKinGJollyKinG

2244 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Are you looking for a pandas specific solution? I'm sure you could accomplish this through the datetime module if not
  
  – Reedinationer
  Mar 27 at 23:17
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What is the expected output here? Your title and the question contents don't quite match up.
  
  – roganjosh
  Mar 27 at 23:25
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Possible duplicate of Number to Date Conversion using Pandas in Python?
  
  – roganjosh
  Mar 27 at 23:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That's a speculative dupe because I'm not sure the exact date formats translate between the libraries (turning a numerical input to a date format)
  
  – roganjosh
  Mar 27 at 23:42
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Your title says "MM/DD/YYY". You don't really want a 3-digit year, do you? (If your requirements permit it, consider using ISO-8601 format "YYYY-MM-DD". xkcd.com/1179)
  
  – Keith Thompson
  Mar 27 at 23:51
  
  

  
  
  
  

 | 
show 1 more comment

0

I have a .xlsb file that I want to use pandas and analyse. I have found how to use pyxlsb to open the file and create another dataset. However, the problem now is that the time formats have changed into a different number format (e.g 41256).

The code I'm using at the moment is:

dataset = []

with open_xlsb(file) as wb: #opening an xlsb file workbook
 with wb.get_sheet(1) as sheet1:
 for row in sheet1.rows():
 dataset.append([item.v for item in row])

dataset= pd.DataFrame(dataset[1:], columns=dataset[0])

I have already tried the convert_date as follows:

convert_date(dataset)

I have also tried the to_datetime function, but unsure if I used it correctly. For reference, the dataset I am using has dates in multiple columns and rows so I'm looking for a way to covert all of them into the right format, whilst ignoring any errors.

EDIT: So I don't have a single column with "Date", rather I have multiple columns, such as StartDate, EndDate, Last Updated and a few others. The result I want to see is if I go to a column, e.g dataset.columns['StartDate'], I want to get a date value, such as 15/03/2019, as opposed to 42156.

Any help would be much appreciated!

python pandas date

share|improve this question

edited Mar 27 at 23:45

JollyKinG

asked Mar 27 at 23:12

JollyKinGJollyKinG

2244 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Are you looking for a pandas specific solution? I'm sure you could accomplish this through the datetime module if not
  
  – Reedinationer
  Mar 27 at 23:17
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What is the expected output here? Your title and the question contents don't quite match up.
  
  – roganjosh
  Mar 27 at 23:25
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Possible duplicate of Number to Date Conversion using Pandas in Python?
  
  – roganjosh
  Mar 27 at 23:42
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That's a speculative dupe because I'm not sure the exact date formats translate between the libraries (turning a numerical input to a date format)
  
  – roganjosh
  Mar 27 at 23:42
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Your title says "MM/DD/YYY". You don't really want a 3-digit year, do you? (If your requirements permit it, consider using ISO-8601 format "YYYY-MM-DD". xkcd.com/1179)
  
  – Keith Thompson
  Mar 27 at 23:51
  
  

  
  
  
  

 | 
show 1 more comment

I have a .xlsb file that I want to use pandas and analyse. I have found how to use pyxlsb to open the file and create another dataset. However, the problem now is that the time formats have changed into a different number format (e.g 41256).

The code I'm using at the moment is:

dataset = []

with open_xlsb(file) as wb: #opening an xlsb file workbook
 with wb.get_sheet(1) as sheet1:
 for row in sheet1.rows():
 dataset.append([item.v for item in row])

dataset= pd.DataFrame(dataset[1:], columns=dataset[0])

I have already tried the convert_date as follows:

convert_date(dataset)

I have also tried the to_datetime function, but unsure if I used it correctly. For reference, the dataset I am using has dates in multiple columns and rows so I'm looking for a way to covert all of them into the right format, whilst ignoring any errors.

EDIT: So I don't have a single column with "Date", rather I have multiple columns, such as StartDate, EndDate, Last Updated and a few others. The result I want to see is if I go to a column, e.g dataset.columns['StartDate'], I want to get a date value, such as 15/03/2019, as opposed to 42156.

Any help would be much appreciated!

python pandas date

share|improve this question

edited Mar 27 at 23:45

JollyKinG

asked Mar 27 at 23:12

JollyKinGJollyKinG

2244 bronze badges

dataset = []

with open_xlsb(file) as wb: #opening an xlsb file workbook
 with wb.get_sheet(1) as sheet1:
 for row in sheet1.rows():
 dataset.append([item.v for item in row])

dataset= pd.DataFrame(dataset[1:], columns=dataset[0])

convert_date(dataset)

share|improve this question

edited Mar 27 at 23:45

JollyKinG

asked Mar 27 at 23:12

JollyKinGJollyKinG

2244 bronze badges

share|improve this question

edited Mar 27 at 23:45

JollyKinG

asked Mar 27 at 23:12

JollyKinGJollyKinG

2244 bronze badges

asked Mar 27 at 23:12

JollyKinGJollyKinG

2244 bronze badges

asked Mar 27 at 23:12

JollyKinGJollyKinG

2244 bronze badges

1 Answer
1

active

oldest

votes

0

Supposing your date column is 'Date', the command would be something like this:

dataset['Date'] = pd.to_datetime(dataset['Date'], format='%m/%d/%Y')

share|improve this answer

answered Mar 27 at 23:18

accdiasaccdias

1,08388 silver badges1414 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That only does half the job. Now you have a datetime object, not a formatted string
  
  – roganjosh
  Mar 27 at 23:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Well actually, the question is unclear. The title reads as though they want a string at the end from strftime basically
  
  – roganjosh
  Mar 27 at 23:23
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I didn't see the OP saying he wants a string. He says he wants dates formatted as MM/DD/YYYY.
  
  – accdias
  Mar 27 at 23:23
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It's implied in the title but I've asked for clarification.
  
  – roganjosh
  Mar 27 at 23:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  To your edit: and the output of your answer would not be such an output. The format argument to to_datetime is to tell pandas how to parse it out of a string and to a datetime object. It won't print or write back out in the specified format if you leave it with just this step.
  
  – roganjosh
  Mar 27 at 23:27
  

  
  
  
  

 | 
show 3 more comments

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%2f55387880%2fformatting-date-to-mm-dd-yyy-from-xlsb-file%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Supposing your date column is 'Date', the command would be something like this:

dataset['Date'] = pd.to_datetime(dataset['Date'], format='%m/%d/%Y')

share|improve this answer

answered Mar 27 at 23:18

accdiasaccdias

1,08388 silver badges1414 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That only does half the job. Now you have a datetime object, not a formatted string
  
  – roganjosh
  Mar 27 at 23:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Well actually, the question is unclear. The title reads as though they want a string at the end from strftime basically
  
  – roganjosh
  Mar 27 at 23:23
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I didn't see the OP saying he wants a string. He says he wants dates formatted as MM/DD/YYYY.
  
  – accdias
  Mar 27 at 23:23
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It's implied in the title but I've asked for clarification.
  
  – roganjosh
  Mar 27 at 23:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  To your edit: and the output of your answer would not be such an output. The format argument to to_datetime is to tell pandas how to parse it out of a string and to a datetime object. It won't print or write back out in the specified format if you leave it with just this step.
  
  – roganjosh
  Mar 27 at 23:27
  

  
  
  
  

 | 
show 3 more comments

0

Supposing your date column is 'Date', the command would be something like this:

dataset['Date'] = pd.to_datetime(dataset['Date'], format='%m/%d/%Y')

share|improve this answer

answered Mar 27 at 23:18

accdiasaccdias

1,08388 silver badges1414 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  That only does half the job. Now you have a datetime object, not a formatted string
  
  – roganjosh
  Mar 27 at 23:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Well actually, the question is unclear. The title reads as though they want a string at the end from strftime basically
  
  – roganjosh
  Mar 27 at 23:23
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I didn't see the OP saying he wants a string. He says he wants dates formatted as MM/DD/YYYY.
  
  – accdias
  Mar 27 at 23:23
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It's implied in the title but I've asked for clarification.
  
  – roganjosh
  Mar 27 at 23:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  To your edit: and the output of your answer would not be such an output. The format argument to to_datetime is to tell pandas how to parse it out of a string and to a datetime object. It won't print or write back out in the specified format if you leave it with just this step.
  
  – roganjosh
  Mar 27 at 23:27
  

  
  
  
  

 | 
show 3 more comments

Supposing your date column is 'Date', the command would be something like this:

dataset['Date'] = pd.to_datetime(dataset['Date'], format='%m/%d/%Y')

share|improve this answer

answered Mar 27 at 23:18

accdiasaccdias

1,08388 silver badges1414 bronze badges

dataset['Date'] = pd.to_datetime(dataset['Date'], format='%m/%d/%Y')

share|improve this answer

answered Mar 27 at 23:18

accdiasaccdias

1,08388 silver badges1414 bronze badges

answered Mar 27 at 23:18

accdiasaccdias

1,08388 silver badges1414 bronze badges

answered Mar 27 at 23:18

accdiasaccdias

1,08388 silver badges1414 bronze badges