Python convert csv string into Pandas DataFrame

Question

Bit of a Python newbie and would greatly appreciate any help with what I thought would be a very simply operation:

I'm hitting an API which is returning me a <class 'str'> object which looks like this:

one,two,three,four,five,six
1,2,4,1000,0,1
2,4,6,1000,0,0
3,6,9,1000,0,1
4,8,2,1000,0,1
5,10,3,1000,0,1
5,12,7,1000,0,1

At least that is how it is being printed out in VSC terminal on a print(val) command. Looking at the data in notepad++ with show all chars I see that each line is terminated with a CRLF.

I need to get that data into a pandas dataframe for further analysis. I am actually loading a number of other files into Pandas but they are using .read_csv(filepath) to build the DF's. This is the only chunk of data which I have to hit an API to get.

If there is a simple way to do this, I'm not doing a good job of finding it; I've tried different versions of pandas.DataFrame(val) and other methods... but getting nowhere fast. I would have thought there would be a Pandas loader where I could pass in something like (6,6) to tell it to parse out six columns and six records.

Help!

CypherX · Accepted Answer · 2020-07-29 21:11:22Z

6

Solution

Use io.StringIO + pandas.read_csv.

import pandas as pd
from io import StringIO

s = """
one,two,three,four,five,six
1,2,4,1000,0,1
2,4,6,1000,0,0
3,6,9,1000,0,1
4,8,2,1000,0,1
5,10,3,1000,0,1
5,12,7,1000,0,1
"""
s = s.strip()

df = pd.read_csv(StringIO(s), sep=',')
print(df)

Output:

   one  two  three  four  five  six
0    1    2      4  1000     0    1
1    2    4      6  1000     0    0
2    3    6      9  1000     0    1
3    4    8      2  1000     0    1
4    5   10      3  1000     0    1
5    5   12      7  1000     0    1

answered Jul 29, 2020 at 21:11

CypherX

7,4034 gold badges29 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Indrid Over a year ago

FANTASTIC! Thank you very much.

Collectives™ on Stack Overflow

Python convert csv string into Pandas DataFrame

1 Answer 1

Solution

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Solution

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related