Json List to Pandas Dataframe

Question

I've a json list and I can't convert to Pandas dataframe (various rows and 19 columns)

Link to response : https://www.byma.com.ar/wp-admin/admin-ajax.php?action=get_historico_simbolo&simbolo=INAG&fecha=01-02-2018

json response:

[
    {"Apertura":35,"Apertura_Homogeneo":35,"Cantidad_Operaciones":1,"Cierre":35,"Cierre_Homogeneo":35,"Denominacion":"INSUMOS AGROQUIMICOS S.A.","Fecha":"02\/02\/2018","Maximo":35,"Maximo_Homogeneo":35,"Minimo":35,"Minimo_Homogeneo":35,"Monto_Operado_Pesos":175,"Promedio":35,"Promedio_Homogeneo":35,"Simbolo":"INAG","Variacion":-5.15,"Variacion_Homogeneo":0,"Vencimiento":"48hs","Volumen_Nominal":5},
    {"Apertura":34.95,"Apertura_Homogeneo":34.95,"Cantidad_Operaciones":2,"Cierre":34.95,"Cierre_Homogeneo":34.95,"Denominacion":"INSUMOS AGROQUIMICOS S.A.","Fecha":"05\/02\/2018","Maximo":34.95,"Maximo_Homogeneo":34.95,"Minimo":34.95,"Minimo_Homogeneo":34.95,"Monto_Operado_Pesos":5243,"Promedio":-79228162514264337593543950335,"Promedio_Homogeneo":-79228162514264337593543950335,"Simbolo":"INAG","Variacion":-0.14,"Variacion_Homogeneo":-0.14,"Vencimiento":"48hs","Volumen_Nominal":150},
    {"Apertura":32.10,"Apertura_Homogeneo":32.10,"Cantidad_Operaciones":2,"Cierre":32.10,"Cierre_Homogeneo":32.10,"Denominacion":"INSUMOS AGROQUIMICOS S.A.","Fecha":"07\/02\/2018","Maximo":32.10,"Maximo_Homogeneo":32.10,"Minimo":32.10,"Minimo_Homogeneo":32.10,"Monto_Operado_Pesos":98756,"Promedio":32.10,"Promedio_Homogeneo":32.10,"Simbolo":"INAG","Variacion":-8.16,"Variacion_Homogeneo":-8.88,"Vencimiento":"48hs","Volumen_Nominal":3076}
]

I use the next piece of code to convert this json to dataframe:

def getFinanceHistoricalStockFromByma(tickerList): 
     dataFrameHistorical = pd.DataFrame()  
     for item in tickerList:
         url = 'https://www.byma.com.ar/wp-admin/admin-ajax.php?action=get_historico_simbolo&simbolo=' + item + '&fecha=01-02-2018'
         response = requests.get(url)
         if response.content : print 'ok info Historical Stock'
         data = response.json()                
         dfItem = jsonToDataFrame(data)                
         dataFrameHistorical = dataFrameHistorical.append(dfItem, ignore_index=True)    
    return dataFrameHistorical

def jsonToDataFrame(jsonStr):    
     return json_normalize(jsonStr)

The result of json_normalize is 1 row and a lot of columns. How can I convert this json response to 1 row per list?

json_normalize does not expect a string.

Stop harming Monica
– Stop harming Monica

2018-02-08 14:54:02 +00:00
Commented Feb 8, 2018 at 14:54 — Stop harming Monica
– Stop harming Monica, Commented Feb 8, 2018 at 14:54

Blazina · Accepted Answer · 2018-02-08 14:54:25Z

85

If you change this line in your function: dfItem = jsonToDataFrame(data) to:

dfItem = pd.DataFrame.from_records(data)

it should work. I tested your function with this line replaced, using ['INAG'] as a parameter passed to your getFinanceHistoricalStockFromByma function, and it returned a DataFrame.

answered Feb 8, 2018 at 14:54

Blazina

1,32610 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2552108 Over a year ago

One thing to remember here is that data must be converted to json first (using json.loads for example). Using string on pd.DataFrame.from_records will split all the strings into each single character into columns :D

Kanthishere Over a year ago

TypeError: 'tuple' object is not callable

cottontail · Accepted Answer · 2023-01-31 23:42:37Z

You can directly call pd.DataFrame() directly on a list of dictionaries as in the sample in OP (.from_records() is not necessary). Try:

df = pd.DataFrame(data)

For the function in the OP, since pd.DataFrame.append() is deprecated, the best way to write it currently (pandas >= 1.4.0) is to collect the json responses in a Python list and create a DataFrame once at the end of the loop.

def getFinanceHistoricalStockFromByma(tickerList): 
    dataHistorical = []                                 # <----- list
    for item in tickerList:
        url = 'https://www.byma.com.ar/wp-admin/admin-ajax.php?action=get_historico_simbolo&simbolo=' + item + '&fecha=01-02-2018'
        response = requests.get(url)
        if response.content:
            print('ok info Historical Stock')
        data = response.json()
        dataHistorical.append(data)                     # <----- list.append()
    dataFrameHistorical = pd.DataFrame(dataHistorical)  # <----- dataframe construction
    return dataFrameHistorical

Collectives™ on Stack Overflow

Json List to Pandas Dataframe

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related