Create Pandas DataFrame from space separated String

Question

I have a string:

              C1     C2                       DATE     C4     C5         C6      C7
0            0.0    W04  2021-01-08 00:00:00+00:00      E    EUE         C1     157
1            0.0    W04  2021-01-08 00:00:00+00:00      E    AEU         C1     157
2            0.0    W04  2021-01-01 00:00:00+00:00      E   SADA         H1     747
3            0.0    W04  2021-01-04 00:00:00+00:00      E   SSEA         H1     747
4            0.0    W04  2021-01-05 00:00:00+00:00      E   GPEA         H1     747

It sure looks like a Pandas DataFrame because it comes from one. I need to convert it into a Pandas DataFrame.

I tried the following:

pd.read_csv(StringIO(string_file),sep=r"\s+")

but it messes with the columns and separates the DATE column into 2 columns.

@SayandipDutta thank you, it works on the body, but the header is still messed up. It looks right aligned and one-space-separated — Pedro Cintra
– Pedro Cintra, Commented Feb 2, 2021 at 18:30
I copied this data and tried reading using StringIO and sep=r'\s\s+', I can't reproduce your problem. Works fine for me. — Sayandip Dutta
– Sayandip Dutta, Commented Feb 2, 2021 at 18:32
pandas has read_fwf where you can specify the breakpoints for columns. this looks like a solution for your use case. — Eric Truett
– Eric Truett, Commented Feb 6, 2021 at 23:37

Arthur D. · Accepted Answer · 2021-02-10 20:46:30Z

6

First, recreate the string:

s = """
              C1     C2                       DATE     C4     C5         C6      C7
0            0.0    W04  2021-01-08 00:00:00+00:00      E    EUE         C1     157
1            0.0    W04  2021-01-08 00:00:00+00:00      E    AEU         C1     157
2            0.0    W04  2021-01-01 00:00:00+00:00      E   SADA         H1     747
3            0.0    W04  2021-01-04 00:00:00+00:00      E   SSEA         H1     747
4            0.0    W04  2021-01-05 00:00:00+00:00      E   GPEA         H1     747
"""

Now, you can use Pandas.read_csv to import a buffer:

from io import StringIO
df = pd.read_csv(StringIO(s), sep=r"\s\s+")

From what I can tell, this results in exactly the DataFrame that you are looking for:

You may want to convert the DATE column to datetime values as well:

df['DATE'] = df.DATE.astype('datetime64')

answered Feb 10, 2021 at 20:46

Arthur D.

4102 silver badges8 bronze badges

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

1 Comment

mcsj120 Over a year ago

Something to consider when following this method is that you may run into issues if the column name is longer than the data is being inputted. In that case, you may have to manually add some spaces in the header row in order for the columns to properly align with data.

Collectives™ on Stack Overflow

Create Pandas DataFrame from space separated String

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related