This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Python table data extraction

Hi!

I'm working a table like this:

enter image description here

and I need to access each one of the "GenBank Accessions" number, compare it to the second table "Info".

enter image description here

Then, for each row in the first table I want to add the count of the specific species. For example, in the first row of the table one I would have

Enterovirus A: 4 
Enterovirus G: 2

How can I do that?

Thanks a lot!

python
• 1,617 views • link
updated by Ram Ram • written by deniselavezzari • deniselavezzari

What have you tried? Have you Googled anything?

• 0 views • link
written by Ram Ram

Hi!

I'm trying for-loops, but only the first element of each row is exactly compared to the second table.

Maybe I need to do the opposite, it might be easier...

import pandas as pd
import time

file = pd.read_csv('full_DB.csv')

info = pd.read_csv('info.csv')

file['Sample'] = file['Sample'].str.replace('accn\|', '')
file.rename(columns={'Sample':'GenBank Accessions'}, inplace=True)

info_sub = info[['Species','GenBank Accessions']]

lista = info['Species'].unique().tolist()

for i in lista:
    file[i] = ""

subset = file.iloc[0:5,:]

for row in subset.index:
    t = subset['GenBank Accessions'][row]
    arr = t.split(',')
    for j in range(len(arr)):
        print(info_sub[info_sub['GenBank Accessions'] == arr[j]])
• 0 views • link
updated by Ram Ram • written by deniselavezzari • deniselavezzari

I've undeleted your question as it has already received some feedback from a couple of users.

Your description of the data is minimal and confusing, so we can't really help with the code. Can you explain what you wish to achieve in plain terms such as :

  1. Split column X from table Y using "," as the delimiter
  2. Match each value in the split column to column Z from table YY

etc.

• 0 views • link
written by Ram Ram

Are these real tables or CVS data files? You can use Python pandas library for this data merge. Create a dataframe for each table, loop through them, position the required value in table 1 and find the other value in table 2, and so on. At end build a new CSV file with the found desired values. In Machine Learning this is call data preprocessing or Exploratory Data Analysis (EDA). We do that all the time in any Machine Learning project. I hope this clarifies your task.

• 0 views • link
written by Ernest Bonat • Ernest Bonat

This does not answer OP's question - it's just a detour into how the genre of OP's question is common among ML projects. As such, I've moved it to a comment.

Data cleaning is common in any data analysis project, not just ML projects. Please stop pushing ML everywhere.

• 0 views • link
written by Ram Ram

0 answers

No answers yet.

Log in to answer this question.

More posts like this