Import ".dat" file into pandas dataframe

Question

I have a ".dat" file that looks like this:

You can download it here: https://filebin.net/hnhsv8sc3zvaoi8k

I would like to read this file into a pandas data frame. I have found this question and tried the solution:

with open('Output2.dat','r') as f:
    df = pd.DataFrame(l.rstrip().split() for l in f)

The result looks like that:

the row "Balance" was displaced, How to change that?
How can I make sure that the first row is added as pandas column titles?

Thank you very much.

EDIT: This is how the final Table should look like:

L3viathan · Accepted Answer · 2019-11-05 11:47:01Z

1

The "empty elements" are 0xa0 bytes; non-breaking spaces in Latin-1, but spaces nevertheless, therefore they are seen as whitespace by str.split.

Here's a hacky workaround that works:

with open("Output2.dat", "rb") as f:  # binary mode
    df = pd.DataFrame(
        [e.decode("latin-1") if e != b'\xa0' else None for e in l.strip().split()]
        for l in f
    )

Here I'm opening the file in binary mode, because bytes.split ignores that special space. When I detect it, I further replace it with None to not have some weird character in the empty cells.

I'm assuming a latin-1 encoding, because 0xa0 on its own is invalid UTF-8, but you may need to replace that with windows-1252 or some other encoding, depending on where this data is coming from.

answered Nov 5, 2019 at 11:47

L3viathan

27.5k2 gold badges63 silver badges84 bronze badges

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

2 Comments

henry Over a year ago

That's very nice ! Thanks a lot ! Is there a way to add the first row as column names in the pandas dataframe ?

L3viathan Over a year ago

Sure, you could do something like columns = [element.decode("latin-1") for element in next(f).strip().split()] before the line where you define the dataframe, and provide them as columns like df = pd.DataFrame(([e.decode....] for l in f), columns=columns)

Collectives™ on Stack Overflow

Import ".dat" file into pandas dataframe

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related