How to convert string that uses "\r\n" as line breaks to pandas dataframe

Question

I have a string bytearray(b'onefish,twofish,greenfish,bluefish\r\n1,2,3,4') I get this string from an encrypted csv file using the pgpy library.

I need my dataframe to look like this.

      onefish twofish greenfish bluefish
0     1       2       3         4

So far I use multiple for loops that make lists that I then put into a dictionary.

How can approach this using: df = pd.Dataframe(bytearray(b'onefish,twofish,greenfish,bluefish\r\n1,2,3,4') to get the output I want above?

Poojan · Accepted Answer · 2019-09-11T18:27:33.557

5

from given data. Split data by '\r\n'.
assuming First row is always header pass that as columns and rest of the rows as data.

>>>import pandas as pd
>>>b = b'onefish,twofish,greenfish,bluefish\r\n1,2,3,4\r\n5,6,7,8' 
>>>data = list(map(lambda x: x.split(','),b.decode('utf-8').split("\r\n")))
>>>pd.DataFrame(data[1:], columns=data[0])

    onefish     twofish     greenfish   bluefish
0      1          2            3           4
1      5          6            7           8

edited Sep 11 '19 at 18:27

answered Sep 11 '19 at 18:25

Poojan

3,366
2
17
33

1

Solid solution but you might want to break up the data line into steps so its clearer to the OP what each is doing – sedavidw Sep 11 '19 at 18:27
1

@sedavidw Done. Removed unnecessary map. Its more clear now. Also added small description. – Poojan Sep 11 '19 at 18:28
hey how do you get the `b` in front of a string? – Jeffyx Sep 11 '19 at 19:29
You can just type. `b` represents binary string. – Poojan Sep 11 '19 at 19:31

score 4 · Answer 2 · answered Sep 11 '19 at 18:34

4

You can use io.BytesIO as far as I'm aware:

import pandas as pd
import io

df = pd.read_csv(io.BytesIO(bytearray(b'onefish,twofish,greenfish,bluefish\r\n1,2,3,4\r\n5,6,7,8\r\n9,10,11,12')))

print(df)

   onefish  twofish  greenfish  bluefish
0        1        2          3         4
1        5        6          7         8
2        9       10         11        12

answered Sep 11 '19 at 18:34

manwithfewneeds

1,137
1
7
10

This kinda works but i only get one column with all the info, its not splitting into 4 columns. – Jeffyx Sep 11 '19 at 19:23
1

The first part is columns so you need to add that to df seperatly. – Poojan Sep 11 '19 at 19:35

How to convert string that uses "\r\n" as line breaks to pandas dataframe

2 Answers2

Linked