Retrieve values from a list resembling json with Pandas

I have values in my DataFrame that look like JSON but not quite.

My column looks like this:

> ['"{immediateJob : ""DIA"",Trimester :""Q"",Year :""}"', nan,
       '"{immediateJob : ""Responsable conformité"",Trimester :""Q3"",Year :""2022""}{immediateJob : ""Responsable projet"",Trimester :""Q3"",Year :""2022""}"',
       ...,
       '"{immediateJob : ""Head of M360 APAC (if this role is created)"",Trimester :""Q"",Year :""}{immediateJob : ""Head of EM Research APAC"",Trimester :""Q"",Year :""}"',
       '"{immediateJob : ""Change Manager su aree di business diverse da quelle attuali"",Trimester :""Q2"",Year :""2024""}"',
       '"{immediateJob : ""Manager ITGP"",Trimester :""Q3"",Year :""2025""}"']

Often it is a missing value, and often it is two values on a single line:

Immediatejob is ok but other are missing here

'"{immediateJob : ""DIA"",Trimester :""Q"",Year :""}"'

In line 2, I have two differents values
'"{immediateJob : ""Responsable conformité"",Trimester :""Q3"",Year :""2022""}{immediateJob : ""Responsable projet"",Trimester :""Q3"",Year :""2022""}"'

I tried to retrieve the values of immediate job, but it does not work because there are often several lines in one.

`DF['col'].str.split('immediateJob : ""', expand=True)[1].str.split('"",', expand=True)[0]`

What I would like is first to separate in several rows when there are several values conditional to the identifier that is in another column and then retrieve the values of immediate job, quarter and year

If you only want to retrieve immediate job, you could manipulate a list like this:

list_test = ['"{immediateJob : ""DIA"",Trimester :""Q"",Year :""}"', 
         None,
         '"{immediateJob : ""Responsable conformité"",Trimester :""Q3"",Year :""2022""}{immediateJob : ""Responsable projet"",Trimester :""Q3"",Year :""2022""}"',
         '"{immediateJob : ""Head of M360 APAC (if this role is created)"",Trimester :""Q"",Year :""}{immediateJob : ""Head of EM Research APAC"",Trimester :""Q"",Year :""}"',
         '"{immediateJob : ""Change Manager su aree di business diverse da quelle attuali"",Trimester :""Q2"",Year :""2024""}"',
         '"{immediateJob : ""Manager ITGP"",Trimester :""Q3"",Year :""2025""}"']

list_test = [s.replace('}{', '''}'","'{''') for s in list_test if s is not None]

[s.split('immediateJob : ""')[1].split('"",')[0] for s in list_test]

Output:

['DIA',
 'Responsable conformité',
 'Head of M360 APAC (if this role is created)',
 'Change Manager su aree di business diverse da quelle attuali',
 'Manager ITGP']

I've removed the Nan value from the beginning, was it necessary to keep it?

try this regex pattern and Python code to find each section you want:

"{\s*immediateJob\s*:\s*(\"\"[\w (){}]+\"\"|\"\")\s*,\s*Trimester\s*:\s*(\"\"[\w (){}]+\"\"|\"\")\s*,\s*Year\s*:\s*(\"\"\d+\"\"|\"\")}"

import re

string = ['"{immediateJob : ""DIA"",Trimester :""Q"",Year :""}"', 
        None,
        '"{immediateJob : ""Responsable conformité"",Trimester :""Q3"",Year :""2022""}{immediateJob : ""Responsable projet"",Trimester :""Q3"",Year :""2022""}"',
        '"{immediateJob : ""Head of M360 APAC (if this role is created)"",Trimester :""Q"",Year :""}{immediateJob : ""Head of EM Research APAC"",Trimester :""Q"",Year :""}"',
        '"{immediateJob : ""Change Manager su aree di business diverse da quelle attuali"",Trimester :""Q2"",Year :""2024""}"',
        '"{immediateJob : ""Manager ITGP"",Trimester :""Q3"",Year :""2025""}"']

re_pattern = re.compile(r"{\s*immediateJob\s*:\s*(\"\"[\w (){}]+\"\"|\"\")\s*,\s*Trimester\s*:\s*(\"\"[\w (){}]+\"\"|\"\")\s*,\s*Year\s*:\s*(\"\"\d+\"\"|\"\")}")

for i in string:
    if i is not None:
        print(re_pattern.findall(i))

result on your input:

and then, if you want to select "immediate job" values, you should select first component of tuples in each list

import re

string = ['"{immediateJob : ""DIA"",Trimester :""Q"",Year :""}"', 
        None,
        '"{immediateJob : ""Responsable conformité"",Trimester :""Q3"",Year :""2022""}{immediateJob : ""Responsable projet"",Trimester :""Q3"",Year :""2022""}"',
        '"{immediateJob : ""Head of M360 APAC (if this role is created)"",Trimester :""Q"",Year :""}{immediateJob : ""Head of EM Research APAC"",Trimester :""Q"",Year :""}"',
        '"{immediateJob : ""Change Manager su aree di business diverse da quelle attuali"",Trimester :""Q2"",Year :""2024""}"',
        '"{immediateJob : ""Manager ITGP"",Trimester :""Q3"",Year :""2025""}"']

re_pattern = re.compile(r"{\s*immediateJob\s*:\s*(\"\"[\w (){}]+\"\"|\"\")\s*,\s*Trimester\s*:\s*(\"\"[\w (){}]+\"\"|\"\")\s*,\s*Year\s*:\s*(\"\"\d+\"\"|\"\")}")

ans = []
for i in string:
    if i is not None:
        for s in re_pattern.findall(i):
            ans.append(s[0].replace("\"", ""))

print(ans)

output

['DIA', 'Responsable conformité', 'Responsable projet', 'Head of M360 APAC (if this role is created)', 'Head of EM Research APAC', 'Change Manager su aree di business diverse da quelle attuali', 'Manager ITGP']