#🔒 Pandas reading column as object from csv file while it's datetime

37 messages · Page 1 of 1 (latest)

lost fossil
#

Hello, I need to access the year for each dates in a particular column in my csv file. I'm using the to_datetime() function as suggested in another thread. However, at first, even if my column is in date format in my csv, it seems that it is being read as an object, does anyone know why?

hmm the csv, I open it using excel and so it marks the data type as date but a csv doesn't really have a data type?

tawny scaffoldBOT
#

@lost fossil

Python help channel opened

Remember to:

  • Ask your Python question, not if you can ask or if there's an expert who can help.
  • Show a code sample as text (rather than a screenshot) and the error message, if you've got one.
  • Explain what you expect to happen and what actually happens.

:warning: Do not pip install anything that isn't related to your question, especially if asked to over DMs.

Closes after a period of inactivity, or when you send !close.
valid echo
#

CSV is just strings. Pandas know to read many date formats. Maybe post the first several lines of your CSV?

lost fossil
#

sure, wait

valid echo
#

Also, you've looked at the read_csv docs?

lost fossil
# 
1,1,m,FALSE,1,59000,7/31/2020,A,0,0,0,FALSE,7/1/2018,0,1,0,0,0,FALSE,5100,5100,7/1/2020,,A,TRUE,0,0,0,0,0,0,0,0,1,325000,,,,,,,,,,,,,,,,,,,,,,,59000

I needed to change the values after the 59 000, which is a date

#

euh yeah I did have a look, I mean, I manage to make the code work but didn't understand at first why my column is being detected as an object

valid echo
#

Right, USA-centric date format. Pandas should recognise that pretty readily.

lost fossil
#

wait, here is my code

valid echo
#

Is is a particular column? The above seems to be 1 row of a very wide CSV.

lost fossil
# 
df = pd.read_csv("TestData/.csv")
pd.set_option('display.max_rows', df.shape[0]+1)
pd.set_option('display.max_columns', df.shape[1]+1)

print(df['PaymentDate'].dtype)
df['PaymentDate'] = pd.to_datetime(df['PaymentDate'])
print(df['PaymentDate'].dtype)


years = np.where(df['PaymentDate'].dt.year == 2020, 2025, 2026)

df["PaymentDate"] = pd.to_datetime({
    'year': years,
    'month': df["PaymentDate"].dt.month,
    'day': df["PaymentDate"].dt.day,
})
lost fossil
#

see, I first check the PaymentDate data type

#

it printed object

#

then I read from a blog I needed to convert it into datetime

#

I couldn't use the .dt because it can only be used on datetime

valid echo
#

Is that really one line of the CSV above. Looks like 3 or more record.

I'd pass parse_dates=[6,12,21] in the read_csv call. Don't try to convert the columns afterwards, let pandas do it in the read_csv.

#

See the docs on the parse_dates parameter in the URL above.

lost fossil
#

ok will have a look

valid echo
#

AIUI pandas scans several rows to infer the date format - I presume it rejects possibe formats which do not match all the rows.

#

A column type of objects probably means that pands doesn't consider every value in the column to be a basic tyupe like float or date.

lost fossil
#

yep strange

#

will have a look

#

Thanks !

valid echo
#

Or these days this may no longer be needed.

lost fossil
#

ok will try

#

will come back

lost fossil
#

but it works though when I use the parse date when loading

valid echo
#

Cool. Saves a lot of trouble.

lost fossil
#

basically, without that, it seems that pandas was reading each row as a str

#

yup, thanks for the help !

valid echo
#

Yeah.

tawny scaffoldBOT
#
Python help channel closed for inactivity

This help channel has been closed. Feel free to create a new post in #1035199133436354600. To maximize your chances of getting a response, check out this guide on asking good questions.