How to read first and last column in pandas?

user_10998672 0
Viewed 1664

I looking for solution but can't find anything working.

So I have a txt file. It's looks like this:

geneName1, sampleName1, Allel, allel2, 055, 33, tumor
geneName2, sampleName1, Allel, allel2, 321, 1, ntn
geneName3, sampleName1, Allel, allel2, 32, 44, ddd
geneName4, sampleName1, Allel, allel2, 123, 2, aga2
geneName1, sampleName2, Allel, allel2, 01255, 23, tumorD
geneName2, sampleName2, Allel, allel2, 33, 1, ad2
geneName1, sampleName3, Allel, allel2, yyu
geneName2, sampleName3, Allel, allel2, hhf
geneName4, sampleName3, Allel, allel2, vgv
geneName5, sampleName3, Allel, allel2, aga5

And it's don't have a header. But for clear:

nameOfGene, sampleNumber, Allel1, Allel2, GS1, GS2, descr

Do as u can see some lines has not GS1, GS2 data, but all have a descr. All what I need is [nameOfGene,sampleNumber,Allel1,Allel2,descr]

And that's the problem. I tried many solution from internet but can't solve it.

I tried to modify usecols=[] parameter in pd.read_csv to usecols=[0,1,2,3,4,:-1], but pandas don't understand something like that and I got olny syntax error. I tried read whole df but then desc is readed as GS1 (if GS1 is not in line).

I tried concat, but result is the same like above, and when line don't have GS1 the desc is loaded as GS1, and next to cols are 0 or NaN.

Maybe I just ommit something and exist good solution to solve that problem?

pandas dataframe python
5 Answers

I assume your text file is named myfile.txt

import pandas as pd

my_file = open("myfile.txt")
text = [k.split()[0:4]+[k.split()[-1]] for k in my_file.readlines()]
my_file.close()
df= pd.DataFrame(text)
# next line is optional, just if you want named columns
df.columns = ['nameOfGene','sampleNumber','Allel1','Allel2','descr']
print(df)

   nameOfGene  sampleNumber  Allel1   Allel2   descr
0  geneName1,  sampleName1,  Allel,  allel2,   tumor
1  geneName2,  sampleName1,  Allel,  allel2,     ntn
2  geneName3,  sampleName1,  Allel,  allel2,     ddd
3  geneName4,  sampleName1,  Allel,  allel2,    aga2
4  geneName1,  sampleName2,  Allel,  allel2,  tumorD
5  geneName2,  sampleName2,  Allel,  allel2,     ad2
6  geneName1,  sampleName3,  Allel,  allel2,     yyu
7  geneName2,  sampleName3,  Allel,  allel2,     hhf
8  geneName4,  sampleName3,  Allel,  allel2,     vgv
9  geneName5,  sampleName3,  Allel,  allel2,    aga5
user_47244580

First read in just the first row as a datframe. Then, read in two separate dataframes with the required columns with the second datframe syntax being [-2:-1] to read the last column. Then merge the datframes together on index:

cols = pd.read_csv(file, nrows=1).columns
df1 = pd.read_csv(file, usecols=cols[0:3])
df2 = pd.read_csv(file, usecols=cols[-2:-1])
df = pd.merge(df1, df2, how='left',left_index=True, right_index=True)
df
user_63667700

You might have to edit your file to include values that are not available in GS1 and GS2. Might work by adding 2 commas before descr. After you import it as a dataframe in pandas, use the code

new_df = dataframe[['nameOfGene','sampleNumber','Allel1','Allel2','descr']]

This will store the new dataframe containing only nameOfGene, sampleNumber, Allel1, Allel2, descr columns. If you want only the first and last columns exclude sampleNumber, Allel1 and Allel2.

user_139357690

The below code takes your text file and creates a new column containing descr information. We then remove unnecessary columns from this dataframe to leave you with only what you want to keep.

import pandas as pd

file='*.txt' ##put your file path and filename here
DATA = pd.read_csv(file, sep=" ", header=None)

new_data=[]
#print(DATA)
#print(len(DATA))
for i in range(len(DATA)):
    #print(DATA.loc[i][6])
    if not DATA.isnull().loc[i,6]: 
        new_data.append(DATA.loc[i][6])
    else:
        new_data.append(DATA.loc[i][4])
DATA['NewColumn'] = new_data
DATA=DATA.drop(DATA.columns[[4,5,6]], axis=1)
print(DATA)
user_138847450

There are many ways of doing this. You can try the following, being careful about how you read the data, and then putting everything together into the desired Dataframe.

columns = ['nameOfGene', 'sampleNumber', 'Allel1', 'Allel2', 'descr']
file = open('test_data.txt', 'r')
lines = file.readlines()

df = pd.DataFrame(columns = columns)
for line in lines:
     split_line = line.split(',')
     if len(split_line) == 7:
           row_data = split_line[0:4] + [split_line[6]]
     elif len(split_line) == 5:
           row_data = split_line

     row = pd.Series(row_data, index=columns)
     df = df.append(row, ignore_index=True)

Result:

   nameOfGene  sampleNumber  Allel1  Allel2      descr
0  geneName1   sampleName1   Allel   allel2      tumor
1  geneName2   sampleName1   Allel   allel2      ntn
2  geneName3   sampleName1   Allel   allel2      ddd
3  geneName4   sampleName1   Allel   allel2      aga2
4  geneName1   sampleName2   Allel   allel2      tumorD
5  geneName2   sampleName2   Allel   allel2      ad2
6  geneName1   sampleName3   Allel   allel2      yyu
7  geneName2   sampleName3   Allel   allel2      hhf
8  geneName4   sampleName3   Allel   allel2      vgv
9  geneName5   sampleName3   Allel   allel2      aga5
user_30187620
Related