python - Convert pandas df multi index to columns -
first post here, assistance appreciated.
after reading csv file response api request using python urllib2, left multi index df. contains 19 index' 2 'columns'.
how convert these 19 index' additional columns please?
i tried resting index, no luck.
from urllib2 import request, urlopen, urlerror import pandas pd
url = 'url string here' response = urlopen(request) df = pd.read_csv(response) df.reset_index().head() to clear, example if index 1 contains letters a,b,c,d, change index column tittled 'letter' every row containing 1 of these letters. when reset_index, indeed populate every row letter, however, column still index..
edit.. .added more code first part gets df.
from urllib2 import request, urlopen, urlerror import pandas pd host = 'testapi.bmreports.com' port = '443' rep_name = 'detsysprices' version = 'v1' key = 'ldytgh1ylq0k92c' sd = '2016-05-26' sp = 20 criteria = (host,port,rep_name,version,key,sd,sp) url = 'https://%s:%s/bmrs/%s/%s?apikey=%s&settlementdate=%s&settlementperiod=%d&servicetype=csv' % criteria request = request(url) #print url response = urlopen(request) df = pd.read_csv(response) df hdr indicative system price stack data bid 20160526 20 1 1 nan nan f t f f nan 266.0329 -230.211 -230.211 -149.786 -149.786 -48.05 266.0329 1.00000 -48.050 -12782.88 2 t_didcb6 109615 -1 f f f f nan 26.0000 -1.950 -1.950 -1.950 -1.950 -1.95 26.0000 0.98947 -1.929 -50.17 3 t_coso-1 119674 -1 t f f f nan 25.9000 -0.279 0.000 0.000 0.000 0.00 0.0000 0.00000 0.000 0.00 offer 20160526 20 1 t_wburb-2 25968 2 f f f f 0 46.0000 16.163 16.163 0.000 0.000 0.00 0.0000 0.00000 0.000 0.00 2 t_wburb-2 25968 1 f f f f 0 46.0000 3.037 3.037 0.000 0.000 0.00 0.0000 0.00000 0.000 0.00 3 t_cnqps-4 45744 1 f f f f 0 50.0000 0.975 0.000 0.000 0.000 0.00 0.0000 0.00000 0.000 0.00 4 t_damc-1 85044 1 f f f f 0 64.5000 4.583 4.583 0.000 0.000 0.00 0.0000 0.00000 0.000 0.00 5 t_damc-1 85045 1 f f f f 0 64.5000 0.083 0.083 0.000 0.000 0.00 0.0000 0.00000 0.000 0.00 6 t_damc-1 85046 1 f f f f 0 64.5000 22.000 22.000 0.000 0.000 0.00 0.0000 0.00000 0.000 0.00 7 t_bage-1 33725 1 t f f f 0 70.0000 1.583 1.583 0.000 0.000 0.00 0.0000 0.00000 0.000 0.00 8 t_sutb-1 68274 1 t f f f 0 72.0000 4.000 4.000 0.000 0.000 0.00 0.0000 0.00000 0.000 0.00 9 t_humr-1 99956 2 f f f f 0 82.4700 9.250 9.250 0.000 0.000 0.00 0.0000 0.00000 0.000 0.00 10 t_humr-1 99956 1 f f f f 0 82.4700 0.250 0.000 0.000 0.000 0.00 0.0000 0.00000 0.000 0.00 11 t_humr-1 99955 2 f f f f 0 82.4700 0.647 0.647 0.000 0.000 0.00 0.0000 0.00000 0.000 0.00 12 t_humr-1 99955 1 f f f f 0 82.4700 0.033 0.000 0.000 0.000 0.00 0.0000 0.00000 0.000 0.00 13 t_humr-1 99956 3 f f f f 0 95.9700 10.317 10.317 0.000 0.000 0.00 0.0000 0.00000 0.000 0.00 14 t_humr-1 99955 3 f f f f 0 95.9700 0.004 0.004 0.000 0.000 0.00 0.0000 0.00000 0.000 0.00 15 t_foye-1 92982 1 t f f f 0 103.0000 1.258 1.258 0.000 0.000 0.00 0.0000 0.00000 0.000 0.00 16 t_foye-1 92983 1 t f f f 0 103.0000 7.500 7.500 0.000 0.000 0.00 0.0000 0.00000 0.000 0.00 ftr 19 nan nan nan nan nan nan nan nan nan nan nan nan nan nan nan nan nan nan nan nan in second part, attempt reset...
df.reset_index() df.index multiindex(levels=[[u'bid', u'ftr', u'offer'], [19, 20160526], [20.0], [1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0, 11.0, 12.0, 13.0, 14.0, 15.0, 16.0], [u'1', u't_bage-1', u't_cnqps-4', u't_coso-1', u't_damc-1', u't_didcb6', u't_foye-1', u't_humr-1', u't_sutb-1', u't_wburb-2'], [25968.0, 33725.0, 45744.0, 68274.0, 85044.0, 85045.0, 85046.0, 92982.0, 92983.0, 99955.0, 99956.0, 109615.0, 119674.0], [-1.0, 1.0, 2.0, 3.0], [u'f', u't'], [u'f', u't'], [u'f'], [u'f'], [0.0], [25.9, 26.0, 46.0, 50.0, 64.5, 70.0, 72.0, 82.47, 95.97, 103.0, 266.0329], [-230.211, -1.95, -0.279, 0.004, 0.033, 0.083, 0.25, 0.647, 0.975, 1.258, 1.583, 3.037, 4.0, 4.583, 7.5, 9.25, 10.317, 16.163, 22.0], [-230.211, -1.95, 0.0, 0.004, 0.083, 0.647, 1.258, 1.583, 3.037, 4.0, 4.583, 7.5, 9.25, 10.317, 16.163, 22.0], [-149.786, -1.95, 0.0], [-149.786, -1.95, 0.0], [-48.05, -1.95, 0.0], [0.0, 26.0, 266.0329], [0.0, 0.98947, 1.0]], labels=[[0, 0, 0, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1], [1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1], [0, 1, 2, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, -1], [0, 5, 3, 9, 9, 2, 4, 4, 4, 1, 8, 7, 7, 7, 7, 7, 7, 6, 6, -1], [-1, 11, 12, 0, 0, 2, 4, 5, 6, 1, 3, 10, 10, 9, 9, 10, 9, 7, 8, -1], [-1, 0, 0, 2, 1, 1, 1, 1, 1, 1, 1, 2, 1, 2, 1, 3, 3, 1, 1, -1], [0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 1, 1, -1], [1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1], [-1, -1, -1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1], [10, 1, 0, 2, 2, 3, 4, 4, 4, 5, 6, 7, 7, 7, 7, 8, 8, 9, 9, -1], [0, 1, 2, 17, 11, 8, 13, 5, 18, 10, 12, 15, 6, 7, 4, 16, 3, 9, 14, -1], [0, 1, 2, 14, 8, 2, 10, 4, 15, 7, 9, 12, 2, 5, 2, 13, 3, 6, 11, -1], [0, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, -1], [0, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, -1], [0, 1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, -1], [2, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1], [2, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1]])
i think can add parameter inplace reset_index:
df.reset_index(inplace=true) or assign back:
df = df.reset_index() sample:
import pandas pd import io temp=u"""hdr;indicative;system;price;stack;data bid;20160526;20;1;1;nan;nan;f;t;f;f;nan;266.0329;-230.211;-230.211;-149.786;-149.786;-48.05;266.0329;1.00000;-48.050;-12782.88 2;t_didcb6;109615;-1;f;f;f;f;nan;26.0000;-1.950;-1.950;-1.950;-1.950;-1.95;26.0000;0.98947;-1.929;-50.17 3;t_coso-1;119674;-1;t;f;f;f;nan;25.9000;-0.279;0.000;0.000;0.000;0.00;0.0000;0.00000;0.000;0.00 offer;20160526;20;1;t_wburb-2;25968;2;f;f;f;f;0;46.0000;16.163;16.163;0.000;0.000;0.00;0.0000;0.00000;0.000;0.00 2;t_wburb-2;25968;1;f;f;f;f;0;46.0000;3.037;3.037;0.000;0.000;0.00;0.0000;0.00000;0.000;0.00 3;t_cnqps-4;45744;1;f;f;f;f;0;50.0000;0.975;0.000;0.000;0.000;0.00;0.0000;0.00000;0.000;0.00 4;t_damc-1;85044;1;f;f;f;f;0;64.5000;4.583;4.583;0.000;0.000;0.00;0.0000;0.00000;0.000;0.00 5;t_damc-1;85045;1;f;f;f;f;0;64.5000;0.083;0.083;0.000;0.000;0.00;0.0000;0.00000;0.000;0.00 6;t_damc-1;85046;1;f;f;f;f;0;64.5000;22.000;22.000;0.000;0.000;0.00;0.0000;0.00000;0.000;0.00 7;t_bage-1;33725;1;t;f;f;f;0;70.0000;1.583;1.583;0.000;0.000;0.00;0.0000;0.00000;0.000;0.00 8;t_sutb-1;68274;1;t;f;f;f;0;72.0000;4.000;4.000;0.000;0.000;0.00;0.0000;0.00000;0.000;0.00 9;t_humr-1;99956;2;f;f;f;f;0;82.4700;9.250;9.250;0.000;0.000;0.00;0.0000;0.00000;0.000;0.00 10;t_humr-1;99956;1;f;f;f;f;0;82.4700;0.250;0.000;0.000;0.000;0.00;0.0000;0.00000;0.000;0.00 11;t_humr-1;99955;2;f;f;f;f;0;82.4700;0.647;0.647;0.000;0.000;0.00;0.0000;0.00000;0.000;0.00 12;t_humr-1;99955;1;f;f;f;f;0;82.4700;0.033;0.000;0.000;0.000;0.00;0.0000;0.00000;0.000;0.00 13;t_humr-1;99956;3;f;f;f;f;0;95.9700;10.317;10.317;0.000;0.000;0.00;0.0000;0.00000;0.000;0.00 14;t_humr-1;99955;3;f;f;f;f;0;95.9700;0.004;0.004;0.000;0.000;0.00;0.0000;0.00000;0.000;0.00 15;t_foye-1;92982;1;t;f;f;f;0;103.0000;1.258;1.258;0.000;0.000;0.00;0.0000;0.00000;0.000;0.00 16;t_foye-1;92983;1;t;f;f;f;0;103.0000;7.500;7.500;0.000;0.000;0.00;0.0000;0.00000;0.000;0.00 ftr;19;nan;nan;nan;nan;nan;nan;nan;nan;nan;nan;nan;nan;nan;nan;nan;nan;nan;nan;nan;nan""" #after testing replace io.stringio(temp) filename df = pd.read_csv(io.stringio(temp), sep=";", index_col=none, parse_dates=false) #print (df) print (df.index) multiindex(levels=[['10', '11', '12', '13', '14', '15', '16', '2', '3', '4', '5', '6', '7', '8', '9', 'bid', 'ftr', 'offer'], ['19', '20160526', 't_bage-1', 't_cnqps-4', 't_coso-1', 't_damc-1', 't_didcb6', 't_foye-1', 't_humr-1', 't_sutb-1', 't_wburb-2'], [20.0, 25968.0, 33725.0, 45744.0, 68274.0, 85044.0, 85045.0, 85046.0, 92982.0, 92983.0, 99955.0, 99956.0, 109615.0, 119674.0], [-1.0, 1.0, 2.0, 3.0], ['1', 'f', 't', 't_wburb-2'], ['25968', 'f'], ['2', 'f'], ['f'], ['0', 'f', 't'], ['103.0000', '25.9000', '26.0000', '46.0000', '50.0000', '64.5000', '70.0000', '72.0000', '82.4700', '95.9700', 'f'], ['-0.279', '-1.950', '0.004', '0.033', '0.083', '0.250', '0.647', '0.975', '1.258', '1.583', '10.317', '22.000', '3.037', '4.000', '4.583', '7.500', '9.250', 'f'], [-1.95, 0.0, 0.004, 0.083, 0.647, 1.258, 1.583, 3.037, 4.0, 4.583, 7.5, 9.25, 10.317, 22.0], [-1.95, 0.0, 46.0, 266.0329], [-230.211, -1.95, 0.0, 16.163], [-230.211, -1.95, 0.0, 16.163], [-149.786, 0.0, 26.0]], labels=[[15, 7, 8, 17, 7, 8, 9, 10, 11, 12, 13, 14, 0, 1, 2, 3, 4, 5, 6, 16], [1, 6, 4, 1, 10, 3, 5, 5, 5, 2, 9, 8, 8, 8, 8, 8, 8, 7, 7, 0], [0, 12, 13, 0, 1, 3, 5, 6, 7, 2, 4, 11, 11, 10, 10, 11, 10, 8, 9, -1], [1, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 2, 1, 3, 3, 1, 1, -1], [0, 1, 2, 3, 1, 1, 1, 1, 1, 2, 2, 1, 1, 1, 1, 1, 1, 2, 2, -1], [-1, 1, 1, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, -1], [-1, 1, 1, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, -1], [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1], [2, -1, -1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1], [10, 2, 1, 10, 3, 4, 5, 5, 5, 6, 7, 8, 8, 8, 8, 9, 9, 0, 0, -1], [17, 1, 0, 17, 12, 7, 14, 4, 11, 9, 13, 16, 5, 6, 3, 10, 2, 8, 15, -1], [-1, 0, 1, 1, 7, 1, 9, 3, 13, 6, 8, 11, 1, 4, 1, 12, 2, 5, 10, -1], [3, 0, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, -1], [0, 1, 2, 3, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, -1], [0, 1, 2, 3, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, -1], [0, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, -1]]) df.reset_index(inplace=true) print (df.index) rangeindex(start=0, stop=20, step=1)
Comments
Post a Comment