Cfrgtkky

I am generating a df from command line result with code like below :-

df_output_lines = [s.split() for s in os.popen("my command linecode").read().splitlines()]

df_output_lines  = list(filter(None, df_output_lines))

and tehn converting it into a dataframe :-

df=pd.DataFrame(df_output_lines)

df

the data is in the below format :-

abc = pd.DataFrame([['time:"08:59:38.000"', 'instance:"(null)"','id:"3214039276626790405"'],['time:"08:59:38.000"', 'instance:"(Ops-MacBook-Pro.local)"','id:"3214039276626790405"'],['time:"08:59:38.000"', 'instance:"(Ops-MacBook-Pro.local)"','id:"3214039276626790405"']])

abc

I want to filter it in a way so that value before : will be the column name and the values within the quotes " " be the value and same goes for all columns. The output should be like :-

As of now i am doing it the hard way :-

abc.rename(columns={0:'time',1:'instance',2:'id'},inplace=True)

and then

abc['time'] = abc['time'].map(lambda x: str(x)[:-1])

abc['time'] = abc['time'].map(lambda x: str(x)[6:])



abc['instance'] = abc['instance'].map(lambda x: str(x)[:-1])

abc['instance'] = abc['instance'].map(lambda x: str(x)[10:])



abc['id'] = abc.id.str.extract('(d+)', expand=True).astype(int)

Any suggestion for lambda expression or any one liner to do this.

My out put for the raw log is like below :-

    time:"11:22:20.000" instance:"(null)" id:"723927731576482920" channel:"sip:confctl.com" type:"control" elapsedtime:"0.000631" level:"info" operation:"Init" message:"Initialize (version 4.9.0002.30618) ... "



    time:"11:22:21.000" instance:"Ops-MacBook-Pro.local" id:"723927731576482920" channel:"sip:confctl.com" type:"control" elapsedtime:"0.067122" level:"info" operation:"Connect" message:"Connecting to https://hrpd.www.vivox.com/api2/"



    time:"11:22:23.000" instance:"Ops-MacBook-Pro.local" id:"723927731576482920" channel:"sip:confctl-.com" type:"control" elapsedtime:"2.685700" level:"info" operation:"Connect" message:"Connected to https://hrpd.www.vivox.com/api2/"



    time:"11:22:23.000" instance:"Ops-MacBook-Pro.local" id:"723927731576482920" channel:"sip:confctl-.com" type:"control" elapsedtime:"2.814268" level:"info" operation:"Login" message:"Logged in .tester_food."



    time:"11:22:23.000" instance:"Ops-MacBook-Pro.local" id:"723927731576482920" channel:"sip:confctl-.com" type:"control" elapsedtime:"2.912255" level:"error" operation:"Call" message:".tester_food. failed to join sip:confctl-2@hrpd.vivox.com error:Access token has invalid signature(403)"



 time:"12:30:41.000" instance:"Ops-MacBook-Pro.local" id:"10316899144153251411" channel:"sip:confctl-2@hrpd.vivox.com" type:"media" sampleperiod:"0.000000" incomingpktsreceived:"0" incomingpktsexpected:"0" incomingpktsloss:"0" incomingpktssoutoftime:"0" incomingpktsdiscarded:"0" outgoingpktssent:"0" predictedmos:"3" latencypktssent:"0" latencycount:"0" latencysum:"0.000000" latencymin:"0.000000" latencymax:"0.000000" callid:"2477580077" r_factor:"0.000000"

Feed list of dictionaries to pd.DataFrame

The pd.DataFrame constructor accepts a list of dictionaries directly. You can use str.rstrip and str.split within a list comprehension:

res = pd.DataFrame([dict(i.rstrip('"').split(':"') for i in row) for row in abc.values])



print(res)



                    id                 instance          time

0  3214039276626790405                   (null)  08:59:38.000

1  3214039276626790405  (Ops-MacBook-Pro.local)  08:59:38.000

2  3214039276626790405  (Ops-MacBook-Pro.local)  08:59:38.000

It's unclear what logic you use to determine only 'null' strings are surrounded by parentheses.

Given your example input of:

time:"11:22:20.000" instance:"(null)" id:"723927731576482920" channel:"sip:confctl.com" type:"control" elapsedtime:"0.000631" level:"info" operation:"Init" message:"Initialize (version 4.9.0002.30618) ... "



time:"11:22:21.000" instance:"Ops-MacBook-Pro.local" id:"723927731576482920" channel:"sip:confctl.com" type:"control" elapsedtime:"0.067122" level:"info" operation:"Connect" message:"Connecting to https://hrpd.www.vivox.com/api2/"



time:"11:22:23.000" instance:"Ops-MacBook-Pro.local" id:"723927731576482920" channel:"sip:confctl-.com" type:"control" elapsedtime:"2.685700" level:"info" operation:"Connect" message:"Connected to https://hrpd.www.vivox.com/api2/"



time:"11:22:23.000" instance:"Ops-MacBook-Pro.local" id:"723927731576482920" channel:"sip:confctl-.com" type:"control" elapsedtime:"2.814268" level:"info" operation:"Login" message:"Logged in .tester_food."



time:"11:22:23.000" instance:"Ops-MacBook-Pro.local" id:"723927731576482920" channel:"sip:confctl-.com" type:"control" elapsedtime:"2.912255" level:"error" operation:"Call" message:".tester_food. failed to join sip:confctl-2@hrpd.vivox.com error:Access token has invalid signature(403)"

Which is coming from your os.popen command, then we filter out blank lines and attempt to shlex.split the line so that whitespace in quoted items is preserved (but the quotes themselves are removed), eg:

import os

import shlex

import pandas as pd



rows = [shlex.split(line) for line in os.popen("my command linecode").read().splitlines() if line.strip()]

This'll give you, for example rows[0] of:

['time:11:22:20.000',

 'instance:(null)',

 'id:723927731576482920',

 'channel:sip:confctl.com',

 'type:control',

 'elapsedtime:0.000631',

 'level:info',

 'operation:Init',

 'message:Initialize (version 4.9.0002.30618) ... ']

You then partition those on : to separate the identifier from the value and feed that into a pd.DataFrame, eg:

df = pd.DataFrame(dict(col.partition(':')[::2] for col in row) for row in rows)

Giving you a df of:

            channel elapsedtime                  id               instance  level                                            message operation          time     type

0   sip:confctl.com    0.000631  723927731576482920                 (null)   info           Initialize (version 4.9.0002.30618) ...       Init  11:22:20.000  control

1   sip:confctl.com    0.067122  723927731576482920  Ops-MacBook-Pro.local   info     Connecting to https://hrpd.www.vivox.com/api2/   Connect  11:22:21.000  control

2  sip:confctl-.com    2.685700  723927731576482920  Ops-MacBook-Pro.local   info      Connected to https://hrpd.www.vivox.com/api2/   Connect  11:22:23.000  control

3  sip:confctl-.com    2.814268  723927731576482920  Ops-MacBook-Pro.local   info                            Logged in .tester_food.     Login  11:22:23.000  control

4  sip:confctl-.com    2.912255  723927731576482920  Ops-MacBook-Pro.local  error  .tester_food. failed to join sip:confctl-2@hrp...      Call  11:22:23.000  control

Though the answer is already produced, However would like to add a regex base approach to achieve the same:

>>> abc

                  time                            instance                        id

0  time:"08:59:38.000"                   instance:"(null)"  id:"3214039276626790405"

1  time:"08:59:38.000"  instance:"(Ops-MacBook-Pro.local)"  id:"3214039276626790405"

2  time:"08:59:38.000"  instance:"(Ops-MacBook-Pro.local)"  id:"3214039276626790405"

Just applying regex=True within DataFrame.

>>> abc.replace('instance:|id:|time:|"|[()]', '',regex=True)

           time               instance                   id

0  08:59:38.000                   null  3214039276626790405

1  08:59:38.000  Ops-MacBook-Pro.local  3214039276626790405

2  08:59:38.000  Ops-MacBook-Pro.local  3214039276626790405



OR   



# abc.replace('(instance:|id:|time:)|"|[()]', '',regex=True)

regex explanation:

• 1st Alternative 'instance: 'instance: matches the characters 'instance: literally (case sensitive)




• 2nd Alternative id: id: matches the characters id: literally (case sensitive)




• 3rd Alternative time: time: matches the character time: literally (case sensitive)




• 4th Alternative " matches the character " literally (case sensitive)




• 5th Alternative [()]' Match a single character present in the list below [()]
  () matches a single character in the list () (case sensitive)