CODEWITHSUNDEEP
pythonpandas
user572575
user572575

Reputation: 1049

Python How to convert string to dataframe?

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 ?

Upvotes: 0

Views: 245

Answers (2)

anky
anky

Reputation: 75080

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         -

Upvotes: 1

Monu Kumar
Monu Kumar

Reputation: 103

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=";")

Upvotes: 4

Related Questions