How to import time data in the format of “M-d-y h-m”?

user_16748629 0
Viewed 66

The sample data from csv is shown as follows:

datetime,symbol,price,volume
10/1/2020 9:00,XYZ,10.68,375
10/1/2020 9:00,XYZ,10.9,66
10/1/2020 9:00,XYZ,11.42,103
10/1/2020 9:00,XYZ,12.62,280
10/1/2020 9:00,XYZ,10.73,23
10/1/2020 9:00,XYZ,11.44,299
10/1/2020 9:00,XYZ,12.66,152
10/1/2020 9:00,XYZ,11.04,401
10/1/2020 9:00,XYZ,10.61,392
10/1/2020 9:00,XYZ,11.21,473

I executed the following line to read the data:

schemaTB = extractTextSchema(csvFile)
update schemaTB set type="DATETIME" where name="datetime"
schemaTB[`format]=["M-d-y h:m:s",,,];
t = loadText(csvFile,,schemaTB)

But it reported an error:

t = loadText(csvFile, , schemaTB) => Invalid temporal format M-d-y h:m:s
dolphindb python
1 Answers

You can use pandas for this task.
First you should read the date time column with the correct type while importing your csv. Then you convert to the desired format:

import pandas as pd
df = pd.read_csv(csvFile, sep=",", parse_dates=['datetime'])
df["datetime"].dt.strftime("%m-%d-%Y %H:%M:%S")
print(df)

Output:

             datetime symbol  price  volume
0 2020-10-01 09:00:00    XYZ  10.68     375
1 2020-10-01 09:00:00    XYZ  10.90      66
2 2020-10-01 09:00:00    XYZ  11.42     103
3 2020-10-01 09:00:00    XYZ  12.62     280
4 2020-10-01 09:00:00    XYZ  10.73      23
5 2020-10-01 09:00:00    XYZ  11.44     299
6 2020-10-01 09:00:00    XYZ  12.66     152
7 2020-10-01 09:00:00    XYZ  11.04     401
8 2020-10-01 09:00:00    XYZ  10.61     392
9 2020-10-01 09:00:00    XYZ  11.21     473

Edit: you can even directly import in the right format with a lambda function:

df = pd.read_csv(csvFile, sep=",", parse_dates=['datetime'], date_parser=lambda d: pd.Timestamp(d).strftime("%m-%d-%Y %H:%M:%S"))
user_135255120
Related