How to use PyCall in Julia to convert Python output to Julia DataFrame

Question

I would like to get some data from quandland analyze it in Julia. Unfortunately, there is no official API for this (yet). I know this solution , but it is still very limited in functionality and does not match the same syntax as the original Python API.

I thought it would be wise to use PyCallJulia to retrieve data using the official Python API from within. This gives the result, but I'm not sure how I can convert it to a format that I could use in Julia (ideally a DataFrame).

I have tried the following.

using PyCall, DataFrames
@pyimport quandl

data = quandl.get("WIKI/AAPL", returns = "pandas");

Julia converts this output to Dict{Any,Any}. When used returns = "numpy"instead, returns = "pandas"I get PyObject rec.array.

How can I get datalike Julia DataFrameas it quandl.jlwill return it? Please note that quandl.jlthis is not an option for me, since it does not support automatic extraction of several assets and does not have several other functions, so it is important that I can use the Python API.

Thanks for any suggestions!

Answer 1

Python/ Pandas. , , ; Pandas 0.18.0 Python 2 Pandas 0.19.1 Python 3. @niczky12 , Dict{Any,Any} . , - , PyCall Pandas, . :

• :

  data = quandl.get("WIKI/AAPL", returns = "pandas")
  cols = keys(data)
  df = DataFrame(Any[collect(values(data[c])) for c in cols], map(Symbol, cols))
  

• PyCall niczky12, . , data[:Open] , data["Open"] PyObject.

  data = pycall(quandl.get, PyObject, "WIKI/AAPL", returns = "pandas")
  cols = data[:columns]
  df = DataFrame(Any[Array(data[c]) for c in cols], map(Symbol, cols))
  

, , , . :

df[:Date] = collect(data[:index])

Answer 2

:

data:

julia> colnames = map(Symbol, data[:columns]);
12-element Array{Symbol,1}:
 :Open                
 :High                
 :Low                 
 :Close               
 :Volume              
 Symbol("Ex-Dividend")
 Symbol("Split Ratio")
 Symbol("Adj. Open")  
 Symbol("Adj. High")  
 Symbol("Adj. Low")   
 Symbol("Adj. Close") 
 Symbol("Adj. Volume")

DataFrame:

julia> y = DataFrame(Any[Array(data[c]) for c in colnames], colnames)

6×12 DataFrames.DataFrame
│ Row │ Open  │ High  │ Low   │ Close │ Volume   │ Ex-Dividend │ Split Ratio │
├─────┼───────┼───────┼───────┼───────┼──────────┼─────────────┼─────────────┤
│ 1   │ 28.75 │ 28.87 │ 28.75 │ 28.75 │ 2.0939e6 │ 0.0         │ 1.0         │
│ 2   │ 27.38 │ 27.38 │ 27.25 │ 27.25 │ 785200.0 │ 0.0         │ 1.0         │
│ 3   │ 25.37 │ 25.37 │ 25.25 │ 25.25 │ 472000.0 │ 0.0         │ 1.0         │
│ 4   │ 25.87 │ 26.0  │ 25.87 │ 25.87 │ 385900.0 │ 0.0         │ 1.0         │
│ 5   │ 26.63 │ 26.75 │ 26.63 │ 26.63 │ 327900.0 │ 0.0         │ 1.0         │
│ 6   │ 28.25 │ 28.38 │ 28.25 │ 28.25 │ 217100.0 │ 0.0         │ 1.0         │

│ Row │ Adj. Open │ Adj. High │ Adj. Low │ Adj. Close │ Adj. Volume │
├─────┼───────────┼───────────┼──────────┼────────────┼─────────────┤
│ 1   │ 0.428364  │ 0.430152  │ 0.428364 │ 0.428364   │ 1.17258e8   │
│ 2   │ 0.407952  │ 0.407952  │ 0.406015 │ 0.406015   │ 4.39712e7   │
│ 3   │ 0.378004  │ 0.378004  │ 0.376216 │ 0.376216   │ 2.6432e7    │
│ 4   │ 0.385453  │ 0.38739   │ 0.385453 │ 0.385453   │ 2.16104e7   │
│ 5   │ 0.396777  │ 0.398565  │ 0.396777 │ 0.396777   │ 1.83624e7   │
│ 6   │ 0.420914  │ 0.422851  │ 0.420914 │ 0.420914   │ 1.21576e7   │

@Matt B. .

, Any . , , :

# first, guess the Julia equivalent of type of the object
function guess_type(x::PyCall.PyObject)
  string_dtype = x[:dtype][:name]
  julia_string = string(uppercase(string_dtype[1]), string_dtype[2:end])

  return eval(parse("$julia_string"))
end

# convert an individual column, falling back to Any array if the guess was wrong
function convert_column(x)
  y = try Array{guess_type(x)}(x) catch Array(x) end
  return y
end

# put everything together into a single function
function convert_pandas(df)
  colnames =  map(Symbol, data[:columns])
  y = DataFrame(Any[convert_column(df[c]) for c in colnames], colnames)

  return y
end

, data , , Float64:

y = convert_pandas(data);
showcols(y)
9147×12 DataFrames.DataFrame
│ Col # │ Name        │ Eltype  │ Missing │
├───────┼─────────────┼─────────┼─────────┤
│ 1     │ Open        │ Float64 │ 0       │
│ 2     │ High        │ Float64 │ 0       │
│ 3     │ Low         │ Float64 │ 0       │
│ 4     │ Close       │ Float64 │ 0       │
│ 5     │ Volume      │ Float64 │ 0       │
│ 6     │ Ex-Dividend │ Float64 │ 0       │
│ 7     │ Split Ratio │ Float64 │ 0       │
│ 8     │ Adj. Open   │ Float64 │ 0       │
│ 9     │ Adj. High   │ Float64 │ 0       │
│ 10    │ Adj. Low    │ Float64 │ 0       │
│ 11    │ Adj. Close  │ Float64 │ 0       │
│ 12    │ Adj. Volume │ Float64 │ 0       │

Answer 3

There is an API. Just use Quandl.jl: https://github.com/milktrader/Quandl.jl

using Quandl
data = quandlget("WIKI/AAPL")

This gives an additional advantage in obtaining data in a useful Julia format (TimeArray), which has the appropriate methods defined for working with such data.