pandas 将csv dateint列读取到datetime [英] Pandas read csv dateint columns to datetime
问题描述
日期,时间,开放,高,低,接近,数量,分割,收益,股息,sym
20130625,715,49.2634,49.2634,49.2634,49.2634,156.293,1,0,0,JPM
20130625,730,49.273, 49.273,49.273,49.273,208.39,1,0,0,JPM
20130625,740,49.1866,49.1866,49.1866,49.1866,224.019,1,0,0,JPM
20130625,745,49.321, 49.321,49.321,49.321,208.39,1,0,0,JPM
20130625,750,49.3306,49.369,49.3306,49.369,4583.54,1,0,0,JPM
20130625,755,49.369, 49.369,49.369,49.369,416.78,1,0,0,JPM
20130625,800,49.369,49.369,49.3594,49.3594,1715.05,1,0,0,JPM
20130625,805,49.369, 49.369,49.3306,49.3306,1333.7,1,0,0,JPM
20130625,810,49.3306,49.3786,49.3306,49.3786,1567.09,1,0,0,JPM
我有以下代码将其读入Pandas中的DataFrame
import numpy as np
import scipy as sp
import pandas as pd
import datetime as dt
fname ='bindat.csv'
df = pd.read_csv(fname,header = 0,sep =',')
问题是日期和时间列以int64的形式读取。我想将这两个合并到一个单一的时间戳,例如:2013-06-25 07:15:00。
我正在努力寻找时间正确使用:
df ['date'] = pd.to_datetime(df ['date']。astype(str)
df ['time'] = pd.to_datetime(df ['time']。astype(str))
第一个命令可以转换,但时间似乎很奇怪。
df.info()
< class'pandas.core.frame.DataFrame'>
Int64Index:9999条目,0到9998
数据列(共11列):
日期9999非空datetime64 [ns]
时间9999非空对象
打开9999非空float64
高9999非空float64
低9999非空float64
关闭9999非空float64
卷9999非空float64
拆分9999非空float64
收入9999非空int64
红利9999非空float64
sym 9999非空对象
dtypes:datetime64 [ns](1) ,float64(7),int64(1),object(2)None
然后我会想要合并成一个DatetimeIndex。
非常感谢任何建议。
干杯!
有很多方法可以做到这一点。在 read_csv
中执行此操作的一种方法是使用 parse_dates
和 date_parser
参数,告诉 parse_dates
组合日期和时间列并定义一个内联函数来解析日期:
>>> df = pd.read_csv(bindat.csv,parse_dates = [[date,time]],
date_parser = lambda x:pd.to_datetime(x,format =%Y%m%d %H%M),
index_col =date_time)
>>> df
打开高低收盘价分割盈利股息sym
date_time
2013-06-25 07:15:00 49.2634 49.2634 49.2634 49.2634 156.293 1 0 0 JPM
2013-06- 25 07:30:00 49.2730 49.2730 49.2730 49.2730 208.390 1 0 0 JPM
2013-06-25 07:40:00 49.1866 49.1866 49.1866 49.1866 224.019 1 0 0 JPM
2013-06-25 07:45 :00 49.3210 49.3210 49.3210 49.3210 208.390 1 0 0 JPM
2013-06-25 07:50:00 49.3306 49.3690 49.3306 49.3690 4583.540 1 0 0 JPM
2013-06-25 07:55:00 49.3690 49.3690 49.3690 49.3690 416.780 1 0 0 JPM
2013-06-25 08:00:00 49.3690 49.3690 49.3594 49.3594 1715.050 1 0 0 JPM
2013-06-25 08:05:00 49.3690 49.3690 49.3306 49.3306 1333.700 1 0 0 JPM
2013-06-25 08:10:00 49.3306 49.3786 49.3306 49.3786 1567.090 1 0 0 JPM
2013-06-25 16:10:00 49.3306 49.3786 49.3306 49.3786 1567.090 1 0 0 JPM
我在最后添加了一个额外的行,以确保小时行为。 p>
I'm new to both StackOverflow and pandas. I am trying to read in a large CSV file with stock market bin data in the following format:
date,time,open,high,low,close,volume,splits,earnings,dividends,sym
20130625,715,49.2634,49.2634,49.2634,49.2634,156.293,1,0,0,JPM
20130625,730,49.273,49.273,49.273,49.273,208.39,1,0,0,JPM
20130625,740,49.1866,49.1866,49.1866,49.1866,224.019,1,0,0,JPM
20130625,745,49.321,49.321,49.321,49.321,208.39,1,0,0,JPM
20130625,750,49.3306,49.369,49.3306,49.369,4583.54,1,0,0,JPM
20130625,755,49.369,49.369,49.369,49.369,416.78,1,0,0,JPM
20130625,800,49.369,49.369,49.3594,49.3594,1715.05,1,0,0,JPM
20130625,805,49.369,49.369,49.3306,49.3306,1333.7,1,0,0,JPM
20130625,810,49.3306,49.3786,49.3306,49.3786,1567.09,1,0,0,JPM
I have the following code to read it into a DataFrame in Pandas
import numpy as np
import scipy as sp
import pandas as pd
import datetime as dt
fname = 'bindat.csv'
df = pd.read_csv(fname, header=0, sep=',')
The problem is that the date and time columns are read in as int64. I would like to merge these two to a single timestamp such as: 2013-06-25 07:15:00.
I am struggling to even get the time read in properly using:
df['date'] = pd.to_datetime(df['date'].astype(str))
df['time'] = pd.to_datetime(df['time'].astype(str))
The first command works to convert, but the time seems weird.
df.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 9999 entries, 0 to 9998
Data columns (total 11 columns):
date 9999 non-null datetime64[ns]
time 9999 non-null object
open 9999 non-null float64
high 9999 non-null float64
low 9999 non-null float64
close 9999 non-null float64
volume 9999 non-null float64
splits 9999 non-null float64
earnings 9999 non-null int64
dividends 9999 non-null float64
sym 9999 non-null object
dtypes: datetime64[ns](1), float64(7), int64(1), object(2)None
And then I'll want to merge into a single DatetimeIndex.
Any suggestions are greatly appreciated.
Cheers!
There are quite a few ways to do this. One way to do it during read_csv
would be to use the parse_dates
and date_parser
arguments, telling parse_dates
to combine the date and time columns and defining an inline function to parse the dates:
>>> df = pd.read_csv("bindat.csv", parse_dates=[["date", "time"]],
date_parser=lambda x: pd.to_datetime(x, format="%Y%m%d %H%M"),
index_col="date_time")
>>> df
open high low close volume splits earnings dividends sym
date_time
2013-06-25 07:15:00 49.2634 49.2634 49.2634 49.2634 156.293 1 0 0 JPM
2013-06-25 07:30:00 49.2730 49.2730 49.2730 49.2730 208.390 1 0 0 JPM
2013-06-25 07:40:00 49.1866 49.1866 49.1866 49.1866 224.019 1 0 0 JPM
2013-06-25 07:45:00 49.3210 49.3210 49.3210 49.3210 208.390 1 0 0 JPM
2013-06-25 07:50:00 49.3306 49.3690 49.3306 49.3690 4583.540 1 0 0 JPM
2013-06-25 07:55:00 49.3690 49.3690 49.3690 49.3690 416.780 1 0 0 JPM
2013-06-25 08:00:00 49.3690 49.3690 49.3594 49.3594 1715.050 1 0 0 JPM
2013-06-25 08:05:00 49.3690 49.3690 49.3306 49.3306 1333.700 1 0 0 JPM
2013-06-25 08:10:00 49.3306 49.3786 49.3306 49.3786 1567.090 1 0 0 JPM
2013-06-25 16:10:00 49.3306 49.3786 49.3306 49.3786 1567.090 1 0 0 JPM
where I added an extra row at the end to make sure that hours were behaving.
这篇关于 pandas 将csv dateint列读取到datetime的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!