how transform text files with non structured format into data frame or a more exploitable format with python?

Question

i have text file with data looking like this (a block of vertical columns with the id's and their corresponding variables). The set of variables is the same but it depends, some id's has more than one attribute for same variable, some doesnt have a certain variable so it is not exactly the set of variable for each id. Below an example of the first id's.(a vertical column )

A more clear image of the text file: enter image description here I used python to transform it into a dataframe:

import collections 
import pandas as pd
from collections import defaultdict as dd
with open ('table.txt', 'r')as f:
    text_list=[r.strip() for r in f.readlines() if r.strip()]
text_list=[x.split(': ') for x in text_list]
data= dd(list)
for text in text_list:
    data[text[0]].append(text[1])
df=pd.DataFrame.from_dict(data, orient='index')
df= df.transpose()

it gave me the result below presented in the image which is false, the first id has same variable with two attributes, but clearly his mail transposed to the second id instead.The second id thus lost his actual value that got transposed into the following id and so on. I want to group for the same id the attribute(s) for each corresponding variable.

enter image description here

user_10035985 · Answer

Suppose you have this text file:

dn: uid=aaa
uid: zi                  
mail: ab                  
aglnPersonStatus: O        
aglnMailStatus: n 
aglnMailAlias: D
aglnMailAlias: e
aglnMailEffectiveAddr: z 

dn: uid=aaa
uid: k 
mail: c 
aglnPersonStatus: O 
aglnMailStatus: n 
aglnMailAlias: o 
aglnMailEffectiveAddr: k

Then:

import pandas as pd

data = []
with open("your_file.txt", "r") as f_in:
    row = {}
    for line in f_in:
        line = line.strip()
        if not line:
            continue
        k, v = line.split(":", maxsplit=1)
        if k == "dn" and row:
            data.append(row)
            row = {k: v}
        elif k not in row:
            row[k] = v
        else:
            row[k] = row[k] + ", " + v

    if row:
        data.append(row)

df = pd.DataFrame(data).drop(columns="dn")
print(df)

Creates this dataframe:

   uid mail aglnPersonStatus aglnMailStatus aglnMailAlias aglnMailEffectiveAddr
0   zi   ab                O              n         D,  e                     z
1    k    c                O              n             o                     k

EDIT: Dropped the dn column and keep all occurrences of values.

how transform text files with non structured format into data frame or a more exploitable format with python?

1 Answers