Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
907 views
in Technique[技术] by (71.8m points)

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

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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  

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...