Pandas to extract Directory data into tabular form

Format Specifications

Your format is not standard but seems to be sufficiently organized and consistent to be parsed easily. Let's assume the format is as follow:

• Record are separated by two newlines;
• Records are collections properties expressed as key value pairs separated by a single newline;
• Property key value pair are separated by : .

Additionally, it seems we may expect:

• Value to contains : as well;
• Extra new lines separating the records or at the end of the file.

Initial version

I'll suggest to parse it in two steps.

First we write a generator that will consume your file:

def generate(file):
    with open(file) as handler:
        for record in handler.read().split("\n\n"):
            result = {}
            for line in record.split("\n"):
                key, value, *_ = line.split(": ")  # Adapted on OP comment
                if key in result:
                    result[key] += ";" + value
                else:
                    result[key] = value
            yield result

Then, once we have loaded your data into a DataFrame:

frame = pd.DataFrame(generate("llc-data.txt"))

We can take advantages of Pandas series bound string functions such as regular expressions:

frame["responsible"] = frame["LLCResponsible"].str.extract("cn=([\w]*)")
frame["location"] = frame["LLCHomeDirectory"].str.extract("ou=([\w_\-]*)")
frame["directory"] = frame["LLCHomeDirectory"].str.findall("NisMap=\w+:([\w_\-/]*)")

Which produce the expected results:

frame[["costCenter", "responsible", "location", "directory", "fullName", "mail"]]

#   costCenter responsible           location                                          directory               fullName                      mail  
# 0   KR000001   llcq05075  LLC_EMEA_NL-LDC01  [/proj/LLC4008_teja_rafoo_cache/q, /proj/LLC30...  Entertainment account     jhaboo.udalt@LLC.com  
# 1   KR000002   llcq05075  LLC_EMEA_NL-LDC02  [/proj/LLC4008_chourya_rafoo_cache/q, /proj/LL...  Entertainment account     jhaboo.udalt@LLC.com  

Where I have renamed computed columns in order to keep original data untouched for further extraction.

The advantage of this two steps process is that you don't need to adapt your file parser each time you want to extract more information from your file. Now it is just about to implement your additional logic in term of DataFrame manipulations which is decoupled from the file loading step.

If you need extra information later on, then just add a new column:

frame["username"] = frame["LLCAutoSudoRole"].str.extract("cn=([\w]*)")

Additionaly, if you need as much rows as directories, then just explode it:

frame.explode("directory")[["costCenter", "responsible", "directory"]]

#   costCenter responsible                            directory
# 0   KR000001   llcq05075     /proj/LLC4008_teja_rafoo_cache/q
# 0   KR000001   llcq05075           /proj/LLC3008_teja_rafoo/q
# 1   KR000002   llcq05075  /proj/LLC4008_chourya_rafoo_cache/q
# 1   KR000002   llcq05075        /proj/LLC3008_chourya_rafoo/q

Second version after OP update

This snippet cope with the change of trial dataset you have posted. I also have added extra newlines to highlight for sake of reproducibility.

Your final problem is probably due to extra newline at the end of the file. Hence the need of having a way to accurately reproduce inputs.

This new version is resistant to extra newlines and extra field separator in values.

import pandas as pd

data = """costCenter: LL63238012
mail: shiva.gowni@LLp.com
LLpResponsible: cn=LLf58420,ou=Personal,ou=People,ou=LLDI,o=LLP
LLpHomeDirectory: nisMapName=auto.home,ou=RDC_AMEC_LL-CDC01,ou=Locations,ou=LLDI,o=LLP#0#Quality=economy,NisMap=llc1002:/proj/llc1002_ziz1/q,Quota=10621
LLpHomeDirectory: nisMapName=auto.home,ou=RDC_AMEC_LL-CDC01,ou=Locations,ou=LLDI,o=LLP#0#Quality=scratKG,NisMap=llc1002:/proj/llc1002_ziz1_scratKG/q,Quota=12000,Id=scratKG
fullName: Tulip project ziz1

costCenter: MX61FRK604
mail: ali.pina@LLp.com
LLpResponsible: cn=LLa11826,ou=Personal,ou=People,ou=LLDI,o=LLP
LLpHomeDirectory: nisMapName=auto.home,ou=SAT_AMEC_MX-GDL01,ou=Locations,ou=LLDI,o=LLP#0#Quality=reference,NisMap=llc0156:/proj/llc0156_zmx28home_3/q,Quota=100,Id=3
LLpHomeDirectory: nisMapName=auto.home,ou=SAT_AMEC_MX-GDL01,ou=Locations,ou=LLDI,o=LLP#0#Quality=reference,NisMap=llc0156:/proj/llc0156_zmx28home/q,Quota=300
LLpHomeDirectory: nisMapName=auto.home,ou=SAT_AMEC_MX-GDL01,ou=Locations,ou=LLDI,o=LLP#0#Quality=reference,NisMap=llc0156:/proj/llc0156_zmx28home_2/q,Quota=100,Id=2
fullName: xFSL to LLDI migration



costCenter: RU61FPD561
mail: udi.landen@LLp.com
LLpResponsible: cn=LLa09278,ou=Personal,ou=People,ou=LLDI,o=LLP
LLpHomeDirectory: nisMapName=auto.home,ou=RDC_AMEC_LL-CDC01,ou=Locations,ou=LLDI,o=LLP#0#Quota=1,Quality=EconomyHP,NisMap=llc2002:/proj/llc2002_zru12/q
LLpHomeDirectory: nisMapName=auto.home,ou=RDC_AMEC_LL-CDC01,ou=Locations,ou=LLDI,o=LLP#0#Quota=2800,Quality=EconomyHP,NisMap=llc1002:/proj/llc1002_zru12_analog/q,Id=analog
LLpHomeDirectory: nisMapName=auto.home,ou=RDC_AMEC_LL-CDC01,ou=Locations,ou=LLDI,o=LLP#0#Quota=1100,Quality=EconomyHP,NisMap=llc1002:/proj/llc1002_zru12_home/q,Id=home
LLpHomeDirectory: nisMapName=auto.home,ou=RDC_AMEC_LL-CDC01,ou=Locations,ou=LLDI,o=LLP#0#Quality=EconomyHP,NisMap=llc1002:/proj/llc1002_zru12_libddk/q,Quota=2162,Id=libddk
LLpHomeDirectory: nisMapName=auto.home,ou=RDC_AMEC_LL-CDC01,ou=Locations,ou=LLDI,o=LLP#0#Quality=EconomyHP,NisMap=llc1002:/proj/llc1002_zru12_proj/q,Quota=1102,Id=proj
fullName: zru12

costCenter: KG63010285
mail: adam.smith@LLp.com
LLpHomeDirectory: nisMapName=auto.home,ou=RDC_EMEA_NL-CDC01,ou=Locations,ou=LLDI,o=LLP#0#Quality=BLLinessCriticalHP,Quota=60,NisMap=llc4008:/proj/llc4008_zuriKG/q
fullName: Container to store ZuriKG vault



costCenter: KG63010285
mail: adam.smith@LLp.com
LLpHomeDirectory: nisMapName=auto.home,ou=RDC_EMEA_NL-CDC01,ou=Locations,ou=LLDI,o=LLP#0#Quality=EconomyHP,Quota=1,NisMap=llc3008:/proj/llc3008_zuriKG_rme/q
LLpHomeDirectory: nisMapName=auto.home,ou=SAT_EMEA_NL-RME01,ou=Locations,ou=LLDI,o=LLP#0#Quality=EconomyHP,Quota=30,NisMap=llc4014:/proj/llc4014_zuriKG_rme/q
LLpHomeDirectory: nisMapName=auto.home,ou=SAT_EMEA_NL-RME01,ou=Locations,ou=LLDI,o=LLP#0#Quality=ScratKGHP,Quota=400,NisMap=llc4014:/proj/llc4014_zuriKG_rme_scratKG/q,Id=scratKG
fullName: Project to restore  project data on the RME work on HP-UX


"""


def generate(data):
    for record in data.split("\n\n"):              # Split records based on two newlines (unix)
        result = {}
        for line in record.split("\n"):            # Split properties based on single newlines (unix)
            if line:                               # Skip empty lines happening for extra or trailing newlines
                key, *value = line.split(": ")     # Tolerant to lines with more than a single ´: ´ (*values)
                value = ": ".join(value)           # Recover original value if more than a single (`: `)
                if key in result:
                    result[key] += ";" + value
                else:
                    result[key] = value
        if result:                                 # Don't yield empty results
            yield result


frame = pd.DataFrame(generate(data))

frame["responsible"] = frame["LLpResponsible"].str.extract("cn=([\w]*)")
frame["location"] = frame["LLpHomeDirectory"].str.extract("ou=([\w_\-]*)")
frame["directory"] = frame["LLpHomeDirectory"].str.findall("NisMap=\w+:([\w_\-/]*)")

Which produce the expected result:

frame[["costCenter", "responsible", "directory"]]

#    costCenter responsible                                          directory
# 0  LL63238012    LLf58420  [/proj/llc1002_ziz1/q, /proj/llc1002_ziz1_scra...
# 1  MX61FRK604    LLa11826  [/proj/llc0156_zmx28home_3/q, /proj/llc0156_zm...
# 2  RU61FPD561    LLa09278  [/proj/llc2002_zru12/q, /proj/llc1002_zru12_an...
# 3  KG63010285         NaN                           [/proj/llc4008_zuriKG/q]
# 4  KG63010285         NaN  [/proj/llc3008_zuriKG_rme/q, /proj/llc4014_zur...

Try this:

import pandas as pd


def convert_str(input: str) -> list:
    """
    :param input: string
    :return: list of dict
    """
    output = []
    for i in input.split('\n\n'):  # split each double newline (start of new entry)
        buffer = {}
        for line in i.split('\n'):  # split each newline (start of new field)
            if line:
                a = line.split(': ')  # split field name : value
                if a[0] == 'LLCHomeDirectory':  # check if field is LLCHomeDirectory
                    # just keep the path. you can use regex if you want to add another library
                    a[1] = a[1].split(':')[1].split(',')[0]
                    # add column otherwise append column
                    if 'LLCHomeDirectory' in buffer:
                        buffer['LLCHomeDirectory'].append(a[1])
                    else: 
                        buffer['LLCHomeDirectory'] = [a[1]]
                elif a[0] == 'LLCResponsible':  # check if field is LLCResponsible
                    # just keep 'cn'
                    a[1] = a[1].split(',')[0][3:]
                    buffer['LLCResponsible'] = a[1]
                else:
                    buffer[a[0]] = a[1].split(',') if ',' in a[1] else a[1]  # split commas
        output.append(buffer)
    return output


data = """
dn: cn=teja_rafoo,ou=Projects,ou=People,ou=LLDI,o=LLC
costCenter: KR000001
LLCResponsible: cn=llcq05075,ou=Personal,ou=People,ou=LLDI,o=LLC
fullName: Entertainment account
LLCAutoSudoRole: cn=teja_rafoo,ou=Auto,ou=Sudoers,ou=Global,o=Services
LLCSecurityLevel: 0
LLCSecDom: cn=LLC,ou=SecurityDomains,ou=LLDI,o=LLC
LLCEndDate: 20000000
mail: jhaboo.udalt@LLC.com
LLCObjectType: ProjectAccount
LLCHomeDirectory: nisMapName=auto.home,ou=LLC_EMEA_NL-LDC01,ou=Locations,ou=LLDI,o=LLC#0#Quality=ScratchHP,NisMap=LLC4008:/proj/LLC4008_teja_rafoo_cache/q,Quota=20,Id=cache
LLCHomeDirectory: nisMapName=auto.home,ou=LLC_EMEA_NL-LDC01,ou=Locations,ou=LLDI,o=LLC#0#Quality=BusinessCriticalHP,Quota=50,NisMap=LLC3008:/proj/LLC3008_teja_rafoo/q
LLCSecurityDomain: cn=LLC,ou=SecurityDomains,ou=Global,o=Services
loginShell: /bin/bash
sn: teja_rafoo
cn: teja_rafoo
LLCStartDate: 20200510
objectClass: Top
objectClass: inetOrgPerson
objectClass: LLCUserAuxClass
objectClass: organizationalPerson
objectClass: Person
LLCPrimaryGroup: cn=teja_rafoo,ou=Generic,ou=Group,ou=LLDI,o=LLC
homeDirectory: /home/teja_rafoo
LLCRequestorGroup: cn=Default,ou=Requestors,ou=Master,o=Services
gecos: Entertainment account
gidNumber: 2365
LLCAutoSudoEntitledUser: cn=llcmgr,ou=Functional,ou=People,ou=LLDI,o=LLC
uidNumber: 9678
LLCLocalStatus: Active
uid: teja_rafoo

dn: cn=chourya_rafoo,ou=Projects,ou=People,ou=LLDI,o=LLC
costCenter: KR000002
LLCResponsible: cn=llcq05075,ou=Personal,ou=People,ou=LLDI,o=LLC
fullName: Entertainment account
LLCAutoSudoRole: cn=chourya_rafoo,ou=Auto,ou=Sudoers,ou=Global,o=Services
LLCSecurityLevel: 0
LLCSecDom: cn=LLC,ou=SecurityDomains,ou=LLDI,o=LLC
LLCEndDate: 20000000
mail: jhaboo.udalt@LLC.com
LLCObjectType: ProjectAccount
LLCHomeDirectory: nisMapName=auto.home,ou=LLC_EMEA_NL-LDC02,ou=Locations,ou=LLDI,o=LLC#0#Quality=ScratchHP,NisMap=LLC4008:/proj/LLC4008_chourya_rafoo_cache/q,Quota=20,Id=cache
LLCHomeDirectory: nisMapName=auto.home,ou=LLC_EMEA_NL-LDC02,ou=Locations,ou=LLDI,o=LLC#0#Quality=BusinessCriticalHP,Quota=50,NisMap=LLC3008:/proj/LLC3008_chourya_rafoo/q
LLCSecurityDomain: cn=LLC,ou=SecurityDomains,ou=Global,o=Services
loginShell: /bin/bash
sn: chourya_rafoo
cn: chourya_rafoo
LLCStartDate: 20200510
objectClass: Top
objectClass: inetOrgPerson
objectClass: LLCUserAuxClass
objectClass: organizationalPerson
objectClass: Person
LLCPrimaryGroup: cn=chourya_rafoo,ou=Generic,ou=Group,ou=LLDI,o=LLC
homeDirectory: /home/chourya_rafoo
LLCRequestorGroup: cn=Default,ou=Requestors,ou=Master,o=Services
gecos: Entertainment account
gidNumber: 2365
LLCAutoSudoEntitledUser: cn=llcmgr,ou=Functional,ou=People,ou=LLDI,o=LLC
uidNumber: 9678
LLCLocalStatus: Active
uid: chourya_rafoo
"""

final = pd.DataFrame.from_dict(convert_str(data))

# remove columns
remove_cols = ['costCenter', 'LLCResponsible', 'fullName', 'mail', 'LLCHomeDirectory', 'Location']
final = final[final.columns.intersection(remove_cols)]

print(final.to_string())

I took the liberty of converting comma deliminated strings to lists.

Output:

  costCenter LLCResponsible               fullName                  mail                                                      LLCHomeDirectory
0   KR000001      llcq05075  Entertainment account  jhaboo.udalt@LLC.com        [/proj/LLC4008_teja_rafoo_cache/q, /proj/LLC3008_teja_rafoo/q]
1   KR000002      llcq05075  Entertainment account  jhaboo.udalt@LLC.com  [/proj/LLC4008_chourya_rafoo_cache/q, /proj/LLC3008_chourya_rafoo/q]