CODEWITHSUNDEEP
pythonpython-3.xpandas
bloodynri
bloodynri

Reputation: 603

Converting string with comma delimited data and newline character to pandas dataframe

I'm pulling 1 minute historical bars for a stock and the data comes in like this:

'2018-06-11 09:31:00,968.250,965.000,968.000,965.250,17220,1160\n2018-06-11
09:32:00,965.250,964.250,965.250,964.750,17872,611\n2018-06-11
09:33:00,965.000,963.250,965.000,963.500,18851,547\n'

It's one string where each row is separated by the new line character and each field is separated by a comma. It looks fine when I use the print() function but I want to convert this into a pandas dataframe. I appreciate any help.

Upvotes: 4

Views: 3962

Answers (1)

jpp
jpp

Reputation: 164703

This works fine when feeding the string to pandas.read_csv:

import pandas as pd
from io import StringIO

mystr = StringIO("""2018-06-11 09:31:00,968.250,965.000,968.000,965.250,17220,1160\n2018-06-11 09:32:00,965.250,964.250,965.250,964.750,17872,611\n2018-06-11 09:33:00,965.000,963.250,965.000,963.500,18851,547\n""")

df = pd.read_csv(mystr, index_col=0, header=None)
df.index = pd.to_datetime(df.index)

print(df)

                          1       2       3       4      5     6
0                                                               
2018-06-11 09:31:00  968.25  965.00  968.00  965.25  17220  1160
2018-06-11 09:32:00  965.25  964.25  965.25  964.75  17872   611
2018-06-11 09:33:00  965.00  963.25  965.00  963.50  18851   547

print(df.dtypes)

1    float64
2    float64
3    float64
4    float64
5      int64
6      int64
dtype: object

Upvotes: 7

Related Questions