Python How to convert string to dataframe?

Question

I print string from telnet like this code.

output = tn.read_until(b"[SW]").decode('ascii')
print(output)

The output variable type is str. It show output like this.

Total AP information:
fault : fault           [1]
idle  : idle            [6]
nor   : normal          [245]
ExtraInfo : Extra information
P     : insufficient power supply
---------------------------------------------------------------------------------------------------------------------------
ID    MAC            Name              Group   IP             Type     State  STA Uptime          ExtraInfo
---------------------------------------------------------------------------------------------------------------------------
0     11cc-ffff-0000 TESTAB1        @ABC1 -              AP1234N        idle   0   -               -
1     11cc-ffff-0000 TESTAB2        @ABC2 10.250.1.0     AP1234N        nor    0   11D:6H:30M:28S  -
2     11cc-ffff-0000 TESTAB3        @ABC3 10.250.2.0     AP1234N        nor    3   11D:6H:30M:11S  -

I want to convert these string to dataframe for sum data in STA column. How to convert string to dataframe ?

as of now there is not a clean way to do it

Deepak Chauhan
– Deepak Chauhan

2019-07-31 05:32:05 +00:00
Commented Jul 31, 2019 at 5:32 — Deepak Chauhan
– Deepak Chauhan, Commented Jul 31, 2019 at 5:32

Monu Kumar · Accepted Answer · 2019-07-31 06:05:34Z

4

Use io.StringIO (python3) and pass that to the pandas.read_csv function. E.g:

import sys
if sys.version_info[0] < 3: 
    from StringIO import StringIO
else:
    from io import StringIO

import pandas as pd

TESTDATA = StringIO("""col1;col2;col3
    1;4.4;99
    2;4.5;200
    3;4.7;65
    4;3.2;140
    """)

df = pd.read_csv(TESTDATA, sep=";")

answered Jul 31, 2019 at 6:05

Monu Kumar

1031 silver badge7 bronze badges

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

1 Comment

anky Over a year ago

pd.compat.StringIO is removed now, one should use io.StringIO going forward

anky · Accepted Answer · 2019-08-15 16:08:58Z

We can take help of StringIO with some custom adjustments:

s ="""Total AP information:
fault : fault           [1]

idle  : idle            [6]
nor   : normal          [245]
ExtraInfo : Extra information
P     : insufficient power supply
---------------------------------------------------------------------------------------------------------------------------
ID    MAC            Name              Group   IP             Type     State  STA Uptime          ExtraInfo
---------------------------------------------------------------------------------------------------------------------------
0     11cc-ffff-0000 TESTAB1        @ABC1 -              AP1234N        idle   0   -               -
1     11cc-ffff-0000 TESTAB2        @ABC2 10.250.1.0     AP1234N        nor    0   11D:6H:30M:28S  -
2     11cc-ffff-0000 TESTAB3        @ABC3 10.250.2.0     AP1234N        nor    3   11D:6H:30M:11S  -"""

df=pd.read_csv(pd.compat.StringIO(s),skiprows=range(7),delim_whitespace=True)
print(df.drop(0))

EDIT: Since pd.compat.StringIO is not supported anymore, you can use:

from io import StringIO
df=pd.read_csv(StringIO(s),skiprows=range(7),delim_whitespace=True)
print(df.drop(0))

  ID             MAC     Name  Group          IP     Type State  STA  \
1  0  11cc-ffff-0000  TESTAB1  @ABC1           -  AP1234N  idle  0.0   
2  1  11cc-ffff-0000  TESTAB2  @ABC2  10.250.1.0  AP1234N   nor  0.0   
3  2  11cc-ffff-0000  TESTAB3  @ABC3  10.250.2.0  AP1234N   nor  3.0   

           Uptime ExtraInfo  
1               -         -  
2  11D:6H:30M:28S         -  
3  11D:6H:30M:11S         -

Collectives™ on Stack Overflow

Python How to convert string to dataframe?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related