Extracting multiple new tab delimited files from an existing one

user_4504988 0
Viewed 34

Hi I am trying to split up a a large metadate file in Python. I started using pandas and wasn't able to figure it out. Right now it's a tab delimited file that looks something like:

id       count
MD1_G1   k123
MD1_G2   k34
MD2_G3   k5678
MD2_G4   k50633
MD4_G5   k100

First I wanted to try and split that first column into two. This is what I have so far:

df = pd.read_table('Table1.txt',names=["id","count"])
#This below was my attempt at trying to split that first column
df_tmp = pd.DataFrame(df.id.str.split('_',1).tolist(),columns=['id','descriptor'])

I managed to split that first column but I don't know how to do it while keeping the original count column.

Once I split that column I wanted to try and iteratively go through and create a new tab delimited file for each unique ID. So in this example I want to get to a situation where I make 3 new files from the original that contain information only from the unique ID. So using the example, I want to get to a situation where I ultimately have 3 new files:

file 1: 

MD1  G1  k123
MD1  G2  k34

file 2:

MD2  G3   k5678
MD2  G4   k50633

file 3:

MD4  G5   k100

If anyone has any ideas please let me know!

pandas python
2 Answers

use pd.concat

df2 = pd.concat([pd.DataFrame(df.id.str.split('_').tolist()), df['count']], axis=1)
for a,b in df2.groupby(0):
    b.to_excel(f'{a}.xlsx')
user_25356110

You can do:

df[['id','new_col']] = df['id'].str.split('_',expand=True)

for g,data in df.groupby('id'):
    data[['id','new_col','count']].to_csv(f'file_{g}.csv',header=None,index=None,sep='\t')

This will give you 3 files, looking like:

file_MD1.csv:

MD1 G1  k123
MD1 G2  k34
user_66711760
Related