pandas dataframe to_csv works with sep='\n' but not sep='\t'

user_4121361 0
Viewed 14605

I try to print my large dataframe to csv file but the tab separation sep='\t' does not work. I then test with newline sep='\n', it seems work ok, break all the elements by newline. What are possibly wrong here?

The code is so simple like

df_M.to_csv('report'+filename, header=True, sep='\t', index=False)

The example of data (the protein column is very long), I mark where to separate by |

"protein |  cl      | pept |    [M] |  [M+1H+]1+ |  [M+2H+]2+"      
"ALBU_HUMAN_UPS Serum albumin (Chain 26-609) - Homo sapiens (Human)|    0|   AWAVAR|        672.37072|            673.378| out-of-range"        
"ALBU_HUMAN_UPS Serum albumin (Chain 26-609) - Homo sapiens (Human)|    0|  TPVSDR| 673.3394900000002|  674.3467700000002|  out-of-range"       
"ALBU_HUMAN_UPS Serum albumin (Chain 26-609) - Homo sapiens (Human)|    0|  NYAEAK| 694.3285900000001|  695.3358700000001|  out-of-range"       
"
pandas python-2.7 csv python
3 Answers

There is problem all rows are in " and then get one column DataFrame.

So need quoting=3 for QUOTE_NONE and then remove trailing " by strip:

df_M= pd.read_csv('test.csv', sep='|', quoting=3, skipinitialspace=True)
df_M.iloc[:, 0] = df_M.iloc[:, 0].str.strip('"')
df_M.iloc[:, -1] = df_M.iloc[:, -1].str.strip('"')
df_M.columns = df_M.columns.str.strip('"')
print (df_M)

                                            protein   cl         pept   \
0  ALBU_HUMAN_UPS Serum albumin (Chain 26-609) - ...         0  AWAVAR   
1  ALBU_HUMAN_UPS Serum albumin (Chain 26-609) - ...         0  TPVSDR   
2  ALBU_HUMAN_UPS Serum albumin (Chain 26-609) - ...         0  NYAEAK   

        [M]   [M+1H+]1+      [M+2H+]2+  
0  672.37072   673.37800  out-of-range  
1  673.33949   674.34677  out-of-range  
2  694.32859   695.33587  out-of-range

Another solution is read data to one column and then split:

df = pd.read_csv('test.csv', sep='^')
cols = df.columns.str.split('|').tolist() 
df_M = df.iloc[:, 0].str.split('|', expand=True)
df_M.columns = cols
print (df_M)

                                            protein    cl            pept   \
0  ALBU_HUMAN_UPS Serum albumin (Chain 26-609) - ...          0     AWAVAR   
1  ALBU_HUMAN_UPS Serum albumin (Chain 26-609) - ...          0     TPVSDR   
2  ALBU_HUMAN_UPS Serum albumin (Chain 26-609) - ...          0     NYAEAK   

                 [M]            [M+1H+]1+        [M+2H+]2+  
0           672.37072              673.378    out-of-range  
1   673.3394900000002    674.3467700000002    out-of-range  
2   694.3285900000001    695.3358700000001    out-of-range

And last to_csv working nice:

df_M.to_csv('report'+filename, header=True, sep='\t', index=False)
user_29010020

Are you saving the data as .tsv format? your data is tsv file as you are separating the data with '\t' which is tab. csv file must be separated by ",".

If you wanna save data with .csv format you need to separate by ","

Link for .csv RFC. http://www.ietf.org/rfc/rfc4180.txt

user_81470140

You can try with 

df_M.to_csv('report'+filename, header=True, sep='\t', index=False, encoding='utf-8')

You can also see here Pandas Data Frame to_csv with more separator

Or it is likely to be a version problem since I couldn't reproduce the problem, see pd.__version__ provided that the last one is '0.21.0'

Hope this is useful

user_82266460
Related