csv - Splitting a Dataframe Column in Python -

-

i trying drop rows pandas dataframe df. looks , has 180 rows , 2745 columns. want rid of rows have curv_typ of pyc_rt , ycif_rt. want rid of geo\time column. extracting data csv file , have realize curv_typ,maturity,bonds,geo\time , characters below pyc_rt,y1,gbaaa,ea in 1 column:

 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

i decided try , split column , drop resulting individual columns, getting error keyerror: 'curv_typ,maturity,bonds,geo\time' in last line of code df_new = pd.dataframe(df['curv_typ,maturity,bonds,geo\time'].str.split(',').tolist(), df[1:]).stack()

import os import urllib2 import gzip import stringio import pandas 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')   open(outfilepath, 'w') outfile:     outfile.write(decompressedfile.read())  #now have deal tsv file import csv  open(outfilepath,'rb') tsvin, open('ecb.csv', 'wb') csvout:     tsvin = csv.reader(tsvin, delimiter='\t')     writer = csv.writer(csvout)     data in tsvin:         writer.writerow(data)   csvout = 'c:\users\sidney\ecb.csv' #df = pd.dataframe.from_csv(csvout) df = pd.read_csv('c:\users\sidney\ecb.csv', delimiter=',', encoding="utf-8-sig") print df df_new = pd.dataframe(df['curv_typ,maturity,bonds,geo\time'].str.split(',').tolist(), df[1:]).stack()

edit: reptilicus's answer used code below:

#now have deal tsv file import csv  outfilepath = filename.split('/')[1][:-3] #as in code above, put here reference csvout = 'c:\users\sidney\ecb.tsv' outfile = open(csvout, "w") open(outfilepath, "rb") f:     line in f.read():         line.replace(",", "\t")         outfile.write(line) outfile.close()  df = pd.dataframe.from_csv("ecb.tsv", sep="\t", index_col=false)

i still same exact output before.

thank you

the format of csv awful, there comma , tab separated data in there.

get rid of commas first:

tr ',' '\t' < irt_euryld_d.tsv > test.tsv

if can't use tr can in python:

outfile = open("outfile.tsv", "w") open("irt_euryld_d.tsz", "rb") f:     line in f.read():         line.replace(",", "\t")         outfile.write(line) outfile.close()

then can load nicely in pandas:

in [9]: df = dataframe.from_csv("test.tsv", sep="\t", index_col=false)  in [10]: df out[10]:     curv_typ maturity    bonds geo\time  2015m06d17   2015m06d16   \ 0     pyc_rt       y1    gbaaa       ea        -0.23        -0.24 1     pyc_rt       y1  gba_aaa       ea        -0.05        -0.02 2     pyc_rt      y10    gbaaa       ea         0.94         0.94 3     pyc_rt      y10  gba_aaa       ea         1.66         1.67 in [11]: df[df["curv_typ"] != "pyc_rt"] out[11]:     curv_typ maturity    bonds geo\time  2015m06d17   2015m06d16   \ 60   ycif_rt       y1    gbaaa       ea        -0.22        -0.23 61   ycif_rt       y1  gba_aaa       ea         0.04         0.08 62   ycif_rt      y10    gbaaa       ea         2.00         1.97

Comments

Post a Comment

Popular posts from this blog

PHP DOM loadHTML() method unusual warning -

-
i had following code works fine on localhost: $document = new domdocument(); $document->loadhtml($p_result); $form = $document->getelementsbytagname('form')->item(0); // code continues using $form variable after same code updated on outside server, loadhtml() failed , issued warning. warning: domdocument::loadhtml() expects parameter 1 valid path, string given in path/name/to/script.php returned null instead of object code pretty gets fatal error. note contents of $p_result absolutely same on outside server , on localhost. but why displays kind of warning , why not work? doesn't loadhtml() expects argument 1 string in first place? why method expects parameter 1 valid path ? just make clear i'm not calling loadhtmlfile() , i'm calling loadhtml() . thanks. you're affected one of php bugs . issue present in php 5.6.8 , 5.6.9. have affected php version on server, , bug-free version on localhost. the bug forbids null
Read more

python - How to create jsonb index using GIN on SQLAlchemy? -

-
here's current code creating index jsonb. index("mytable_data_idx_id_key", mytable.data['id'].astext, postgresql_using='gin') but got error. sqlalchemy.exc.programmingerror: (psycopg2.programmingerror) data type text has no default operator class access method "gin" hint: must specify operator class index or define default operator class data type. [sql: "create index event_data_idx_id_key on event using gin ((data ->> 'id'))"] is there way create index on sqlalchemy? the postgresql specific sqlalchemy docs @ http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#operator-classes mention postgresql_ops dictionary provide "operator class" used postgresql, , provide example illustrating use: index('my_index', my_table.c.id, my_table.c.data, postgresql_ops={ 'data': 'text_pattern_ops',
Read more

c# - TransactionScope not rolling back although no complete() is called -

-
i'm using transactionscope rollback transaction fail bool errorreported = false; action<importerrorlog> newerrorcallback = e => { errorreported = true; errorcallback(e); }; using (var transaction = new transactionscope()) { foreach (importtaskdefinition task in taskdefinition) { loader.load(streamfile, newerrorcallback, task.destinationtable, processingtaskid); } if (!errorreported) transaction.complete(); } i'm sure there no transactionscope started ahead or after code. i'm using entity framework insert in db. regardless state of errorreported transaction never rolled in case of error. what missing ? transactionscope sets transaction.current . that's does. wants transacted must @ property. i believe ef each time connection opened reason. probably, connection open when scope installed. open connection inside of scope or enlist manually. ef has nasty "design decision": default opens
Read more