Convert key value pair strings in a list to columns in a Data Frame Python

user_13579834 0
Viewed 467

I have a list which has the following:

["ID : 1234 Name: John Exam Code:5678", "ID : 4566 Name: Jack Learning Code: 5678", "ID : 5678 Name: Helen Exam Code: 5435"]

Each item in the list have same headers like "ID" and "Name" but some have "Exam Code" and Others have "Learning Code"

How do I convert it into a DataFrame which shows me the following output:

ID Name Exam Code Learning Code
1234 John 5678
4566 Jack 5678
5678 Helen 5435

UPDATED QUERY:

Apologies, Some of the data may also have first name and last name e.g ["ID : 1234 Name: John Black Exam Code:5678", "ID : 4566 Name: Jack Learning Code: 5678", "ID : 5678 Name: Helen Exam Code: 5435"]

SO how can we deal with this ?

ID Name Exam Code Learning Code
1234 John Black 5678
4566 Jack 5678
5678 Helen 5435
dataframe list python
1 Answers

You can use re module to parse the data. For example:

import re
import pandas as pd

lst = [
    "ID : 1234 Name: John Exam Code:5678",
    "ID : 4566 Name: Jack Learning Code: 5678",
    "ID : 5678 Name: Helen Exam Code: 5435",
]

data = []
for line in lst:
    data.append(
        dict(
            re.findall(
                r"(ID|Name|Exam Code|Learning Code)\s*:\s*([^\s]+)", line
            )
        )
    )

df = pd.DataFrame(data).fillna("")
print(df)

Prints:

     ID   Name Exam Code Learning Code
0  1234   John      5678              
1  4566   Jack                    5678
2  5678  Helen      5435

EDIT: To add surnames:

lst = [
    "ID : 1234 Name: John Black Exam Code:5678",
    "ID : 4566 Name: Jack Learning Code: 5678",
    "ID : 5678 Name: Helen Exam Code: 5435",
]

data = []
for line in lst:
    id_ = re.search(r"(ID)\s*:\s*(\d+)", line)
    name = re.search(r"(Name)\s*:\s*(.*)\s+(?:Exam|Learning)", line)
    code = re.search(r"(Exam Code|Learning Code)\s*:\s*(.*)", line)
    data.append(dict([id_.groups(), name.groups(), code.groups()]))

df = pd.DataFrame(data).fillna("")
print(df)

Prints:

     ID        Name Exam Code Learning Code
0  1234  John Black      5678              
1  4566        Jack                    5678
2  5678       Helen      5435
user_100359850
Related