Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #39291 > unrolled thread

Import Json web data source to xls or csv

Back to article view | Back to comp.lang.python

Contents

  Import Json web data source to xls or csv io <maroso@libero.it> - 2013-02-19 23:48 +0000
    Re: Import Json web data source to xls or csv Michael Herman <hermanmu@gmail.com> - 2013-02-19 17:54 -0800
      Re: Import Json web data source to xls or csv io <maroso@libero.it> - 2013-02-20 07:13 +0000
        Re: Import Json web data source to xls or csv Michael Herman <hermanmu@gmail.com> - 2013-02-20 10:27 -0800
      Re: Import Json web data source to xls or csv rusi <rustompmody@gmail.com> - 2013-02-20 23:29 -0800
        Re: Import Json web data source to xls or csv Peter Otten <__peter__@web.de> - 2013-02-21 09:36 +0100
    Re: Import Json web data source to xls or csv Cousin Stanley <cousinstanley@gmail.com> - 2013-02-20 06:59 +0000
      Re: Import Json web data source to xls or csv io <maroso@libero.it> - 2013-02-20 08:19 +0000
        Re: Import Json web data source to xls or csv pyplexed <julius.welby@gmail.com> - 2013-02-20 02:47 -0800
          Re: Import Json web data source to xls or csv io <maroso@libero.it> - 2013-02-21 00:27 +0000

#39291 — Import Json web data source to xls or csv

Hi,

i'm new to python and programming with it and so for json format.
I have my excel 2010 program with vba that does the following :

- read the data flow from http://bitcoincharts.com/t/markets.json
- elaborate it and puts it in excel 2010 for further calculations

What i'm willing to do is the same using Linux (xubuntu) and libreoffice.

I thought learning python would be a smart idea for dealing with json 
format as it has a json library/module.

How do i manage to read the data source from http://bitcoincharts.com/t/
markets.json  and then place it in different cell of a new or existing 
xls worksheet?

I'm trying some code with SPE but i can't sort the problem and i'm 
receiving many errors.

I just need currency, symbol, bid, ask, volume
This is the source code i was trying to use :


import json
import csv
import urllib

url = "http://bitcoincharts.com/t/markets.json"
response = urllib.urlopen(url);
data = json.loads(response.read())

f = csv.writer(open('file.csv', 'wb+'))
# use encode to convert non-ASCII characters
for item in data:
    values = [ x.encode('utf8') for x in item['0'].values() ]
    f.writerow([item['currency'], item['high']] + values)
    
    

Thanks for any help :-)

[toc] | [next] | [standalone]

#39299

[Multipart message — attachments visible in raw view] — view raw

First - you can use Python in Excel. http://www.python-excel.org/ or
https://www.datanitro.com/

Updated code:

import json
import urllib
import csv

url = "http://bitcoincharts.com/t/markets.json"
response = urllib.urlopen(url);
data = json.loads(response.read())

f = open("bitcoin.csv","wb")
c = csv.writer(f)

# write headers
c.writerow(["Currency","Symbol","Bid", "Ask", "Volume"])


for d in data:

c.writerow([str(d["currency"]),str(d["symbol"]),str(d["bid"]),str(d["ask"]),str(d["currency_volume"])])



On Tue, Feb 19, 2013 at 3:48 PM, io <maroso@libero.it> wrote:

> Hi,
>
> i'm new to python and programming with it and so for json format.
> I have my excel 2010 program with vba that does the following :
>
> - read the data flow from http://bitcoincharts.com/t/markets.json
> - elaborate it and puts it in excel 2010 for further calculations
>
> What i'm willing to do is the same using Linux (xubuntu) and libreoffice.
>
> I thought learning python would be a smart idea for dealing with json
> format as it has a json library/module.
>
> How do i manage to read the data source from http://bitcoincharts.com/t/
> markets.json  and then place it in different cell of a new or existing
> xls worksheet?
>
> I'm trying some code with SPE but i can't sort the problem and i'm
> receiving many errors.
>
> I just need currency, symbol, bid, ask, volume
> This is the source code i was trying to use :
>
>
> import json
> import csv
> import urllib
>
> url = "http://bitcoincharts.com/t/markets.json"
> response = urllib.urlopen(url);
> data = json.loads(response.read())
>
> f = csv.writer(open('file.csv', 'wb+'))
> # use encode to convert non-ASCII characters
> for item in data:
>     values = [ x.encode('utf8') for x in item['0'].values() ]
>     f.writerow([item['currency'], item['high']] + values)
>
>
>
> Thanks for any help :-)
> --
> http://mail.python.org/mailman/listinfo/python-list
>

[toc] | [prev] | [next] | [standalone]

#39321

Hi Michael (name of my son) and thanks for the big help.

I'm starting to love python sintax (did they call it python for the slim 
it is compared to other languages?)

Your code didn't work immediatley as it was givin an indentation error 
that i sorted out quickly fixing the last two lines.

It worked like a charm!  :-)

About your suggestions, i tried datanitro but that it only for excel and 
seems to not work under linux on playonlinux (as no linux support is 
given)

I haven't tried the other solution you suggested, i will give it a try as 
soon as possible.

What i'm trying to do is find a new programming language that can 
integrate with softwares like libre office cal or openoffice cal or excel 
as i had my prg working on windows and vba didn't have any issues, but 
now that i moved and try to do all on linux vba isn't 100 percent working 
and many system calls cannot be done.

Do you know any place where i can start learning seriously python on the 
web?


Last question : how can i set a condition in order to exclude from 
importing those rows that have both bid and ask values = none?
I'll try to figure it out in the meantime but i'm a noob so any help will 
be appreciated.

I actually started using SPE and have winpdb installed on my linux box.

Thanks again for all the help.

[toc] | [prev] | [next] | [standalone]

#39370

[Multipart message — attachments visible in raw view] — view raw

Looks like you got it figured out. The indentation error probably occurred
from the copy and paste job into the email.

If you're interested in getting up to speed quickly on Python and Python
Web Development, I have a kickstarter going - http://kck.st/VQj8hq

The $25 pledge will give you access to both courses - learning Python
syntax and Python web development

Let me know if you have any questions. Thanks!

On Tue, Feb 19, 2013 at 11:13 PM, io <maroso@libero.it> wrote:

> Hi Michael (name of my son) and thanks for the big help.
>
> I'm starting to love python sintax (did they call it python for the slim
> it is compared to other languages?)
>
> Your code didn't work immediatley as it was givin an indentation error
> that i sorted out quickly fixing the last two lines.
>
> It worked like a charm!  :-)
>
> About your suggestions, i tried datanitro but that it only for excel and
> seems to not work under linux on playonlinux (as no linux support is
> given)
>
> I haven't tried the other solution you suggested, i will give it a try as
> soon as possible.
>
> What i'm trying to do is find a new programming language that can
> integrate with softwares like libre office cal or openoffice cal or excel
> as i had my prg working on windows and vba didn't have any issues, but
> now that i moved and try to do all on linux vba isn't 100 percent working
> and many system calls cannot be done.
>
> Do you know any place where i can start learning seriously python on the
> web?
>
>
> Last question : how can i set a condition in order to exclude from
> importing those rows that have both bid and ask values = none?
> I'll try to figure it out in the meantime but i'm a noob so any help will
> be appreciated.
>
> I actually started using SPE and have winpdb installed on my linux box.
>
> Thanks again for all the help.
>
>
>
> --
> http://mail.python.org/mailman/listinfo/python-list
>

[toc] | [prev] | [next] | [standalone]

#39401

On Feb 20, 6:54 am, Michael Herman <herma...@gmail.com> wrote:
> First - you can use Python in Excel.http://www.python-excel.org/orhttps://www.datanitro.com/
>
> Updated code:
>
> import json
> import urllib
> import csv
>
> url = "http://bitcoincharts.com/t/markets.json"
> response = urllib.urlopen(url);
> data = json.loads(response.read())
>
> f = open("bitcoin.csv","wb")
> c = csv.writer(f)
>
> # write headers
> c.writerow(["Currency","Symbol","Bid", "Ask", "Volume"])
>
> for d in data:
>
> c.writerow([str(d["currency"]),str(d["symbol"]),str(d["bid"]),str(d["ask"]),str(d["currency_volume"])])



Looks neat.
Tried it with and without the str and there are small differences.

Why do you use the str?

[toc] | [prev] | [next] | [standalone]

#39403

rusi wrote:

> On Feb 20, 6:54 am, Michael Herman <herma...@gmail.com> wrote:
>> c = csv.writer(f)
>> c.writerow([str(d["currency"]),str(d["symbol"]),str(d["bid"]),
>> str(d["ask"]),str(d["currency_volume"])])

> Tried it with and without the str and there are small differences.

The actual differences: float values are converted using repr() instead of 
str(), None values are converted to the empty str "". 
Both special cases make sense to me. So
 
> Why do you use the str?

[toc] | [prev] | [next] | [standalone]

#39320

io wrote:

> ....
> How do i manage to read the data source 
> from http://bitcoincharts.com/t/markets.json
> ....
> I just need currency, symbol, bid, ask, volume
> ....

  Following is a simple way load the json data
  and write the desired fields to a  .csv  file


import json
import urllib

url        = "http://bitcoincharts.com/t/markets.json"

response   = urllib.urlopen( url ) ;

data       = json.loads( response.read() )

list_dicts = [ dict( this ) for this in data ]

f          = open( 'markets.csv' , 'w' ) 

for this_dict in list_dicts :

    currency  = str( this_dict[ 'currency'] )
    symbol    = str( this_dict[ 'symbol' ] )
    bid       = str( this_dict[ 'bid' ] )
    ask       = str( this_dict[ 'ask' ] )
    volume    = str( this_dict[ 'volume' ] )

    this_list = [ currency , symbol , bid , ask , volume ]

    this_str  = ','.join( this_list )

    f.write( this_str + '\n' )

f.close()
  

-- 
Stanley C. Kitching
Human Being
Phoenix, Arizona

[toc] | [prev] | [next] | [standalone]

#39323

Il Wed, 20 Feb 2013 06:59:46 +0000, Cousin Stanley ha scritto:

> io wrote:
> 
>> ....
>> How do i manage to read the data source from
>> http://bitcoincharts.com/t/markets.json ....
>> I just need currency, symbol, bid, ask, volume ....
> 
>   Following is a simple way load the json data and write the desired
>   fields to a  .csv  file
> 
> 
> import json import urllib
> 
> url        = "http://bitcoincharts.com/t/markets.json"
> 
> response   = urllib.urlopen( url ) ;
> 
> data       = json.loads( response.read() )
> 
> list_dicts = [ dict( this ) for this in data ]
> 
> f          = open( 'markets.csv' , 'w' )
> 
> for this_dict in list_dicts :
> 
>     currency  = str( this_dict[ 'currency'] )
>     symbol    = str( this_dict[ 'symbol' ] )
>     bid       = str( this_dict[ 'bid' ] )
>     ask       = str( this_dict[ 'ask' ] )
>     volume    = str( this_dict[ 'volume' ] )
> 
>     this_list = [ currency , symbol , bid , ask , volume ]
> 
>     this_str  = ','.join( this_list )
> 
>     f.write( this_str + '\n' )
> 
> f.close()

Thanks Stanley!
That's another nice code to use!

I was trying to sort out some condition but can't get this working :

import json
import urllib
import csv

url = "http://bitcoincharts.com/t/markets.json"
response = urllib.urlopen(url);
data = json.loads(response.read())

f = open("bitcoin.csv","wb")
c = csv.writer(f)

# write headers
c.writerow(["Currency","Symbol","Bid", "Ask", "Volume"])

#if str(d["bid"])and str(d["ask"])[0] not in ( 'none' ):
    
for d in data where str(d["bid"])and str(d["ask"])[0] not in ( 'none' ):
    c.writerow([str(d["currency"]),str(d["symbol"]),str(d["bid"]),str(d
["ask"]),str(d["currency_volume"])])

[toc] | [prev] | [next] | [standalone]

#39339

Try something like:

for d in data:
    if d["bid"] is not None and d["ask"] is not None:
        c.writerow([str(d["currency"]),str(d["symbol"]),str(d["bid"]),str(d["ask"]),str(d["currency_volume"])])

I've used 'is not None' in case 0 or 0.0 are acceptable bid or offer values. If you want to exclude rows with these values as well as None, you can just use:

for d in data:
    if d["bid"] and d["ask"]:
        # Do stuff

There are other ways to do this kind of thing, by the way. Check out list comprehensions sometime.

[toc] | [prev] | [next] | [standalone]

#39387

That worked perfectley!

Thanks alot.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web