How do I turn multiple lists into a dataframe format? [duplicate]

Question

I have extract a bunch of data using API and read_csv them into a df. The data is in list of list format as below:

[["Datestamp","A","B","C","D","E","F"],
["2019-02-25T00:00:00",88,91,90,88,85,88.4],
["2019-02-26T00:00:00",88,91,90,88,85,88.4],
["2019-02-27T00:00:00",87,90,89,87,84,87.4],
["2019-02-28T00:00:00",87,90,89,87,84,87.4],
["2019-03-01T00:00:00",87,90,89,87,84,87.4],
["2019-03-04T00:00:00",87,90,89,87,84,87.4],
["2019-03-05T00:00:00",87,90,89,87,84,87.4],
["2019-03-06T00:00:00",88,91,90,88,85,88.4],...]

This first list should be the header How do I change it into below dataframe format, where each list is one row of the dataframe:

DataStamp	A	B	C	D	E	F
2019-02-25T00:00:00	88	80	90	84	80	90
2019-02-25T00:00:00	88	80	90	84	80	90
2019-02-25T00:00:00	88	80	90	84	80	90

Thank you for your help!

Posting the full code here:

import requests
import json
import pandas as pd
import io



# Get Authentication token
url = "https://analytics.bmocm.com/auth/login"

payload = json.dumps({
    "username": "<username>",
    "password": "<password>"
})

headers = {
    'Content-Type': "application/json",
    'Cache-Control': "no-cache"
}

response = requests.request("POST", url, data=payload, headers=headers)

# print(response.text)

authData = json.loads(response.text)

token = authData["token"]

# query for historical data
query = {
    "StartDate": "2019-02-24T00:00:00",
    "EndDate": "2020-02-24T00:00:00",
    "SortOrder": "asc",
    "JoinType": "outer",
    "returnHeader": "true",
    "Commands": [
        [1, "CorpSpreads,bmo,5", "true", 1, "A      Senior,5"],
        [2, "CorpSpreads,bns,5", "true", 1, "B       Senior,5"],
        [3, "CorpSpreads,cm,5", "true", 1, "C      Senior,5"],
        [4, "CorpSpreads,ry,5", "true", 1, "D     Senior, 5"],
        [5, "CorpSpreads,td,5", "true", 1, "E         Senior,5"],
        [6, "=(A1+A2+A3+A4+A5)/5", "true", 0, "Average"]
    ]
}

jsonQuery = json.dumps(query)

url = "https://analytics.bmocm.com/sapphire/sapphireQuery"

payload = jsonQuery

headers = {
    'Content-Type': "application/json",
    'Authorization': "Bearer " + token,
    'Cache-Control': "no-cache"
}

# Sapphire query request
response = requests.request("POST", url, data=payload, headers=headers)

#print(response.text)
list = pd.read_csv(io.StringIO(response.text),header=None)

df=pd.DataFrame(list[1:],columns=list[0])

df.head()

df=pd.DataFrame(lst[1:],columns=lst[0]) here lst is your list of list — Anurag Dabas
– Anurag Dabas, Commented Jun 17, 2021 at 3:50
Does this answer your question? Nested List to Pandas Dataframe with headers — Sam Varghese
– Sam Varghese, Commented Jun 17, 2021 at 3:55
This is a duplicate of the answer referenced by @SamVarghese — patrick
– patrick, Commented Jun 17, 2021 at 14:11

Bruno Vermeulen · Accepted Answer · 2021-06-17 06:24:38Z

You can import the list into a Panda's DataFrame as follows:

import pandas as pd

data_list = [
    ["Datestamp", "A", "B", "C", "D", "E", "F"],
    ["2019-02-25T00:00:00", 88, 91, 90, 88, 85, 88.4],
    ["2019-02-26T00:00:00", 88, 91, 90, 88, 85, 88.4],
    ["2019-02-27T00:00:00", 87, 90, 89, 87, 84, 87.4],
    ["2019-02-28T00:00:00", 87, 90, 89, 87, 84, 87.4],
    ["2019-03-01T00:00:00", 87, 90, 89, 87, 84, 87.4],
    ["2019-03-04T00:00:00", 87, 90, 89, 87, 84, 87.4],
    ["2019-03-05T00:00:00", 87, 90, 89, 87, 84, 87.4],
    ["2019-03-06T00:00:00", 88, 91, 90, 88, 85, 88.4],
]

data_df = pd.DataFrame(data_list[1:], columns=data_list[0])
print(data_df.head())

             Datestamp   A   B   C   D   E     F
0  2019-02-25T00:00:00  88  91  90  88  85  88.4
1  2019-02-26T00:00:00  88  91  90  88  85  88.4
2  2019-02-27T00:00:00  87  90  89  87  84  87.4
3  2019-02-28T00:00:00  87  90  89  87  84  87.4
4  2019-03-01T00:00:00  87  90  89  87  84  87.4

Collectives™ on Stack Overflow

How do I turn multiple lists into a dataframe format? [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related