CODEWITHSUNDEEP
pythonpandas
user131983
user131983

Reputation: 3927

How to replace commas with tabs in TSV File

In my dataframe below I am trying to replace the commas in the column curv_typ,maturity,bonds,geo\time with tabs and in the strings below it too, so that I can then create new columns from this.

 curv_typ,maturity,bonds,geo\time  2015M06D16   2015M06D15   2015M06D11   \
0                 PYC_RT,Y1,GBAAA,EA        -0.24        -0.24        -0.24   
1               PYC_RT,Y1,GBA_AAA,EA        -0.02        -0.03        -0.10   
2                PYC_RT,Y10,GBAAA,EA         0.94         0.92         0.99   
3              PYC_RT,Y10,GBA_AAA,EA         1.67         1.70         1.60   
4                PYC_RT,Y11,GBAAA,EA         1.03         1.01         1.09

The code is as follows, but it is not getting rid of the commas and this is where I am struggling.

import os
import urllib2
import gzip
import StringIO
import pandas as pd

baseURL = "http://ec.europa.eu/eurostat/estat-navtree-portlet-prod/BulkDownloadListing?file="
filename = "data/irt_euryld_d.tsv.gz"
outFilePath = filename.split('/')[1][:-3]

response = urllib2.urlopen(baseURL + filename)
compressedFile = StringIO.StringIO()
compressedFile.write(response.read())

compressedFile.seek(0)

decompressedFile = gzip.GzipFile(fileobj=compressedFile, mode='rb') 

with open(outFilePath, 'w') as outfile:
    outfile.write(decompressedFile.read())

#Now have to deal with tsv file
import csv

outFilePath = filename.split('/')[1][:-3] #As in the code above, just put here for reference
csvout = 'C:\Users\Sidney\ECB.tsv'
outfile = open(csvout, "w")
with open(outFilePath, "rb") as f:
    for line in f.read():
        line.replace(",", "\t")
        outfile.write(line)
outfile.close()

df = pd.DataFrame.from_csv("ECB.tsv", sep="\t", index_col=False)

Thank You

Upvotes: 2

Views: 1122

Answers (2)

nocibambi
nocibambi

Reputation: 2421

I had the same problem. Downloaded data from Eurostat, which has the same structure. I tried @EdChum's solution, but I could not do it in one move, so here are further steps I needed:

vc.head() # The original DataFrame
Out[150]: 
  expend,unit,geo\time 2015    2014   2013   2012   2011    2010    2009   \
0       INV,MIO_EUR,AT  109    106.0   86.0  155.0  124.0   130.0   140.0   
1       INV,MIO_EUR,BE  722    664.0  925.0  522.0  590.0   476.0  1018.0   
2       INV,MIO_EUR,BG   16      1.0    2.0   65.0   11.0     5.0     6.0   
3       INV,MIO_EUR,CH  640   1237.0  609.0  662.0  640.0  1555.0   718.0   
4       INV,MIO_EUR,CZ   13     14.0   24.0   17.0  193.0    37.0    61.0   


cols = 'expend,unit,geo\time'.split(',') # Getting the columnns

clean = vc.iloc[:,0].str.split(',').apply(pd.Series) # Creating a clean version
clean = clean.rename(columns = lambda x: cols[x]) # Adding the column names to the clean version

vc = pd.concat([clean, vc.iloc[:,1:]], axis = 1) # Concatenating the two tables

vc.head()
Out[155]: 
  expend     unit geo\time 2015    2014   2013   2012   2011    2010    2009   \
0    INV  MIO_EUR       AT  109    106.0   86.0  155.0  124.0   130.0   140.0   
1    INV  MIO_EUR       BE  722    664.0  925.0  522.0  590.0   476.0  1018.0   
2    INV  MIO_EUR       BG   16      1.0    2.0   65.0   11.0     5.0     6.0   
3    INV  MIO_EUR       CH  640   1237.0  609.0  662.0  640.0  1555.0   718.0   
4    INV  MIO_EUR       CZ   13     14.0   24.0   17.0  193.0    37.0    61.0

Upvotes: 0

EdChum
EdChum

Reputation: 393973

Split the column name to produce your new column names and then call the vectorised str split method with param expand=True:

In [26]:
cols = 'curv_typ,maturity,bonds,geo\\time'.split(',')
df[cols] = df['curv_typ,maturity,bonds,geo\\time'].str.split(',', expand=True)
df

Out[26]:
  curv_typ,maturity,bonds,geo\time  2015M06D16  2015M06D15  2015M06D11  \
0               PYC_RT,Y1,GBAAA,EA       -0.24       -0.24       -0.24   
1             PYC_RT,Y1,GBA_AAA,EA       -0.02       -0.03       -0.10   
2              PYC_RT,Y10,GBAAA,EA        0.94        0.92        0.99   
3            PYC_RT,Y10,GBA_AAA,EA        1.67        1.70        1.60   
4              PYC_RT,Y11,GBAAA,EA        1.03        1.01        1.09   

  curv_typ maturity    bonds geo\time  
0   PYC_RT       Y1    GBAAA       EA  
1   PYC_RT       Y1  GBA_AAA       EA  
2   PYC_RT      Y10    GBAAA       EA  
3   PYC_RT      Y10  GBA_AAA       EA  
4   PYC_RT      Y11    GBAAA       EA

EDIT

For pandas versions 0.16.0 and older then you'll need to use the following line instead:

df[cols] = df['curv_typ,maturity,bonds,geo\\time'].str.split(',').apply(pd.Series)

Upvotes: 2

Related Questions