python 3.x - How do I resolve this filtering challenge in data to DataFrame?

Question

My data in code snippet below refers.

I'm stuck in trying to filter out values inside the list of dictionaries in step2.

**Desired goal:**

0      0.00.00
1    1.765
2.035
2      0.00
0.00
3      1.65
2.21

Where 0.00 values are filtered-out because 'P' the specific key-value is not desired ie. 8.50 in this case. I only want to return values of 6.00 and default any other to 0.00 .

Step1:

data = [[{'A': 2.25, 'G': 17, 'P': 8.50, 'T': 9},
  {'A': 1.63, 'G': 17, 'P': 8.50, 'T': 10}],
 [{'A': 1.765, 'G': 17, 'P': 6.00, 'T': 9},
  {'A': 2.035, 'G': 17, 'P': 6.00, 'T': 10}],
 [{'A': 2.33, 'G': 17, 'P': 8.50, 'T': 9},
  {'A': 1.59, 'G': 17, 'P': 8.50, 'T': 10}],
 [{'A': 1.65, 'G': 17, 'P': 6.00, 'T': 9},
  {'A': 2.21, 'G': 17, 'P': 6.00, 'T': 10}]]

Step2:

df = pd.Series(pd.DataFrame(data).applymap(lambda x:x['C']).astype('str').values.tolist()).str.join("
")
df
0      2.251.63
1    1.765
2.035
2      2.33
1.59
3      1.65
2.21

As you can see, values in rows (0) & (3) should read 0.00 0.00 as desired.

Help is very much appreciated.

Answer 1

This matches your output. Column 2 is the output:

Input:

data = [[{'A': 2.25, 'G': 17, 'P': 8.50, 'T': 9},
  {'A': 1.63, 'G': 17, 'P': 8.50, 'T': 10}],
 [{'A': 1.765, 'G': 17, 'P': 6.00, 'T': 9},
  {'A': 2.035, 'G': 17, 'P': 6.00, 'T': 10}],
 [{'A': 2.33, 'G': 17, 'P': 8.50, 'T': 9},
  {'A': 1.59, 'G': 17, 'P': 8.50, 'T': 10}],
 [{'A': 1.65, 'G': 17, 'P': 6.00, 'T': 9},
  {'A': 2.21, 'G': 17, 'P': 6.00, 'T': 10}]]
df = pd.DataFrame(data)

Code:

df[2] = df[0].apply(lambda x: ['0.00\0.00'  for (a,b) in x.items() if a == 'P' if b == 8.5]).str[0]
df[2] = (df[2].fillna(df[0].apply(lambda x: [f'{b}' for (a,b) in x.items() if a == 'A']).str[0] + '
' +
         df[1].apply(lambda x: [f'{b}' for (a,b) in x.items() if a == 'A']).str[0]))
df

Output:

Out[1]: 
                                         0  
0   {'A': 2.25, 'G': 17, 'P': 8.5, 'T': 9}   
1  {'A': 1.765, 'G': 17, 'P': 6.0, 'T': 9}   
2   {'A': 2.33, 'G': 17, 'P': 8.5, 'T': 9}   
3   {'A': 1.65, 'G': 17, 'P': 6.0, 'T': 9}   

                                          1              2  
0   {'A': 1.63, 'G': 17, 'P': 8.5, 'T': 10}      0.00.00  
1  {'A': 2.035, 'G': 17, 'P': 6.0, 'T': 10}   1.765
2.035  
2   {'A': 1.59, 'G': 17, 'P': 8.5, 'T': 10}      0.00.00  
3   {'A': 2.21, 'G': 17, 'P': 6.0, 'T': 10}     1.65
2.21  

---

Full Explanation and output with more intermediate columns:

data = [[{'A': 2.25, 'G': 17, 'P': 8.50, 'T': 9},
  {'A': 1.63, 'G': 17, 'P': 8.50, 'T': 10}],
 [{'A': 1.765, 'G': 17, 'P': 6.00, 'T': 9},
  {'A': 2.035, 'G': 17, 'P': 6.00, 'T': 10}],
 [{'A': 2.33, 'G': 17, 'P': 8.50, 'T': 9},
  {'A': 1.59, 'G': 17, 'P': 8.50, 'T': 10}],
 [{'A': 1.65, 'G': 17, 'P': 6.00, 'T': 9},
  {'A': 2.21, 'G': 17, 'P': 6.00, 'T': 10}]]
df = pd.DataFrame(data)

#condition of a key-value pair being P and 8.5...
# ...calling x.items() allows you to simultaneously loop through key value pairs 
# ...and return the desired output for the condition while NaN for those that don't meet it
#... we use list comprehension to achieve this and you can use `.str[0]` to transform from list with one value to value
df[2] = df[0].apply(lambda x: ['0.00\0.00'  for (a,b) in x.items() if a == 'P' if b == 8.5]).str[0]

#... just like above logic is easier but conditionally return values if A for column 1
df[3] = df[0].apply(lambda x: [f'{b}' for (a,b) in x.items() if a == 'A']).str[0]

#... just like above logic is easier but conditionally return values if A for column 2
df[4] = df[1].apply(lambda x: [f'{b}' for (a,b) in x.items() if a == 'A']).str[0]

# fill NaN values for column 3 with combined string of columns 4 and 5
df[5] = df[2].fillna(df[3] + '
' + df[4])
df
Out[839]: 
                                         0  
0   {'A': 2.25, 'G': 17, 'P': 8.5, 'T': 9}   
1  {'A': 1.765, 'G': 17, 'P': 6.0, 'T': 9}   
2   {'A': 2.33, 'G': 17, 'P': 8.5, 'T': 9}   
3   {'A': 1.65, 'G': 17, 'P': 6.0, 'T': 9}   

                                          1          2      3      4  
0   {'A': 1.63, 'G': 17, 'P': 8.5, 'T': 10}  0.00.00   2.25   1.63   
1  {'A': 2.035, 'G': 17, 'P': 6.0, 'T': 10}        NaN  1.765  2.035   
2   {'A': 1.59, 'G': 17, 'P': 8.5, 'T': 10}  0.00.00   2.33   1.59   
3   {'A': 2.21, 'G': 17, 'P': 6.0, 'T': 10}        NaN   1.65   2.21   

              5  
0     0.00.00  
1  1.765
2.035  
2     0.00.00  
3    1.65
2.21