Make a table from a json output

Make a table from a json output - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Make a table from a json output (/thread-15009.html)

Pages: 1 2

Make a table from a json output - carstenlp - Dec-29-2018

Hi all

Hope someone can point me in the right direction here.
Im using api to call a website, where i get this response. (cleaned for a few things)

So below i got the data. I would like to make a table from that data. First just a simple table inside python, later on a table i can put in a browser. (using flask or others)

Task1:
But how do i do it?
Would like the headers listed in top: Date | Day | Week | Season .... | points | o:points etc.
Then the data from groups below that. 20110911| Sunday | 1 | 2011 .... | 30 | 13 etc.
20110918| Sunday | 2 | 2011 .... | 12 | 30 etc.

Task2:

Add a header so after the total header there is a another one called: SUM
And below that "column" i would like to make a calculation from the points and o:points columns. like below

Date | Day | Week | Season .... | points | o:points | SUM etc.
20110911| Sunday | 1 | 2011 .... | 30 | 13 | 43 etc.
20110918| Sunday | 2 | 2011 .... | 12 | 30 | 42 etc.

Its my first post in here, and im pretty new to these part of python, but hoping i have described it good enough.
Thanks in advance for all inputs.

/ Carsten

data = json.loads(temp)
pprint(data)

{'groups': [{'columns': [[20110911, 20110918],
                         ['Sunday', 'Sunday'],
                         [1, 2],
                         [2011, 2011],
                         ['Bears', 'Bears'],
                         ['Falcons', 'Saints'],
                         ['home', 'away'],
                         [30, 13],
                         [12, 30],
                         [2.0, 6.0],
                         [40.5, 47.0]],
             'sdql': 'team = Bears and season = 2011 and week < 3'}],
 'headers': ['date',
             'day',
             'week',
             'season',
             'team',
             'o:team',
             'site',
             'points',
             'o:points',
             'line',
             'total']}

RE: Make a table from a json output - Unisoftdev - Dec-29-2018

If I understand it well, then you ask how to separately parse particular data from JSON objects.

So when you need to extract a particular variable then you can construct it like:
your_variable = data["groups"][0]["columns"]... and so far.
When you parse JSON, you just create a crew/line.

Do you mean a beautiful HTML + CSS table on the client-side languages? Maybe, it's gonna take a time which could be spent in a better way (than a free work) and you would like to hire someone. If you have just another question on how you can work on it by yourself then here with it and you will get an answer.

Best regards

RE: Make a table from a json output - carstenlp - Dec-29-2018

(Dec-29-2018, 10:17 PM)Unisoftdev Wrote: If I understand it well, then you ask how to separately parse particular data from JSON objects.

So when you need to extract a particular variable then you can construct it like:
your_variable = data["groups"][0]["columns"]... and so far.
When you parse JSON, you just create a crew/line.

Do you mean a beautiful HTML + CSS table on the client-side languages? Maybe, it's gonna take a time which could be spent in a better way (than a free work) and you would like to hire someone. If you have just another question on how you can work on it by yourself then here with it and you will get an answer.

Best regards

Thanks for input. Didnt know i had to parse out each line. (Still learnig python expressions, and not beeing native english language i miss some of them, but do know parse now :) )

So what i did now is i made a new line for each data set i need:

# First making the headers for the table
title = data['headers']
# Then the data
date = data['groups'][0]['columns'][0]
day = data['groups'][0]['columns'][1]
week = data['groups'][0]['columns'][2]
season = data['groups'][0]['columns'][3]
team = data['groups'][0]['columns'][4]
oteam = data['groups'][0]['columns'][5]
site = data['groups'][0]['columns'][6]
points = data['groups'][0]['columns'][7]
opoints = data['groups'][0]['columns'][8]
line = data['groups'][0]['columns'][9]
total = data['groups'][0]['columns'][10]

Google a bit and found prettytable. That looks fine for me in python. Dont know what to pick from a webpage. Totally newbie there, so dont know anything about it.

so:

from prettytable import prettytable
# Starts the prettytable with the headings for all columns
t = PrettyTable(title)
# Adds all the data to the table, 1 row at a time
t.add_row([date[0], day[0], week[0], season[0], team[0], oteam[0], site[0], points[0], opoints[0], line[0], total[0]])
t.add_row([date[1], day[1], week[1], season[1], team[1], oteam[1], site[1], points[1], opoints[1], line[1], total[1]])

print[t]
+----------+--------+------+--------+-------+---------+------+--------+----------+------+-------+
|   date   |  day   | week | season |  team |  o:team | site | points | o:points | line | total |
+----------+--------+------+--------+-------+---------+------+--------+----------+------+-------+
| 20110911 | Sunday |  1   |  2011  | Bears | Falcons | home |   30   |    12    | 2.0  |  40.5 |
| 20110918 | Sunday |  2   |  2011  | Bears |  Saints | away |   13   |    30    | 6.0  |  47.0 |
+----------+--------+------+--------+-------+---------+------+--------+----------+------+-------+

So far so good :)
Now i would like to add SUM next to total.

title.append('SUM')

Then i would like to sum up the numbers from points and o:points columns. So i below sum gets 42 and 43 accordingly.

Also bonus question.
I never know if my api returns 1 line of data or 200 lines.
So how to write the t.add_row part when lines is dynamic.

Also if there is 200 lines, i only need to print out 10 lines or so. How to add that part.

Thanks

/ Carsten

RE: Make a table from a json output - stullis - Dec-30-2018

You can process this data more efficiently:

def pivot_table(data):
	output = []
    length = len(data[0])
	for i in range(length):
		output.append([datapoint[i] for datapoint in data])
	return output

pivoted_data = pivot_table(data["groups"][0]["columns"])

for row in pivoted_data:
    t.add_row(row)

The output of the function is:

Output:[[20110911, 'Sunday', 1, 2011, 'Bears', 'Falcons', 'home', 30, 12, 2.0, 40.5], 
[20110918, 'Sunday', 2, 2011, 'Bears', 'Saints', 'away', 13, 30, 6.0, 47.0]]

To retrieve only the first ten rows, you can modify the function above to only iterate over ten items. Although, it sounds like you may be more interested in doing aggregate calculations on the data set. Have you looked at namedtuple() in the collections module? That creates a data structure that's optimized for aggregate functions like sum, average, etc.

RE: Make a table from a json output - carstenlp - Dec-30-2018

Thanks for input stullis.
Have read the post 10 times or so, and still dont understand whats happening. And pasting it in, just gives me an error.
So not sure what to get out of this at this moment. :(

/ Carsten

(Dec-30-2018, 12:36 AM)stullis Wrote: You can process this data more efficiently:
def pivot_table(data):
	output = []
    length = len(data[0])
	for i in range(length):
		output.append([datapoint[i] for datapoint in data])
	return output

pivoted_data = pivot_table(data["groups"][0]["columns"])

for row in pivoted_data:
    t.add_row(row)
The output of the function is:
Output:[[20110911, 'Sunday', 1, 2011, 'Bears', 'Falcons', 'home', 30, 12, 2.0, 40.5], 
[20110918, 'Sunday', 2, 2011, 'Bears', 'Saints', 'away', 13, 30, 6.0, 47.0]]
To retrieve only the first ten rows, you can modify the function above to only iterate over ten items. Although, it sounds like you may be more interested in doing aggregate calculations on the data set. Have you looked at namedtuple() in the collections module? That creates a data structure that's optimized for aggregate functions like sum, average, etc.

RE: Make a table from a json output - stullis - Dec-30-2018

What error are you getting?

As for an explanation, the function accepts a list of sub-lists and transforms it. The transformation process (in the loop) takes the data in each sub-list for a specified index and aggregates them into a single list. Like so:

[[1,2,3],[1,2,3]] => [[1,1],[2,2],[3,3]]

This saves a lot of effort to change your data set into rows for the table.

RE: Make a table from a json output - carstenlp - Dec-30-2018

(Dec-30-2018, 08:43 PM)stullis Wrote: What error are you getting?

As for an explanation, the function accepts a list of sub-lists and transforms it. The transformation process (in the loop) takes the data in each sub-list for a specified index and aggregates them into a single list. Like so:

[[1,2,3],[1,2,3]] => [[1,1],[2,2],[3,3]]

This saves a lot of effort to change your data set into rows for the table.

Since i got that error from your code i tried a bit my self, and got below. But only gives me the first line, and since your code is working for me now, i stick to that :)

entries = len(date)

t = PrettyTable(title)

count = 1
while count <= entries:
    t.add_row(
        [date[0], day[0], week[0], season[0], team[0], oteam[0], site[0], points[0], opoints[0], line[0], total[0]])
    if count == entries:
        break
    count +=1

Just tried running your code again. And it worked perfect.. Have no idea what i did earlier today. :D
It looks great. Does exactly what i needed :)

How do i add a header column? Like sum.. Where i can add the points and opoints

Thanks for input stullis. Taking it all in and learning for future stuff :)

RE: Make a table from a json output - snippsat - Dec-30-2018

(Dec-30-2018, 09:35 PM)carstenlp Wrote: How do i add a header column? Like sum.. Where i can add the points and opoints

pandas is also an good option,make it easier to do stuff like calculate,sum,plot..ect.
Also when have a Dataframe it's easier to put on web as i done several times with Flask.

As your JSON(json.loads) output give back a dictionary.
Can clean up a little and make and take into pandas with pd.DataFrame.from_dict(d).
Can also read JSON directly with pandas.read_json().
i just add d = in front of data output in first post.

>>> record = {}
>>> for i in zip(d['headers'], d['groups'][0]['columns']):
...     record[i[0]]  = i[1] 
...     
>>> record
{'date': [20110911, 20110918],
 'day': ['Sunday', 'Sunday'],
 'line': [2.0, 6.0],
 'o:points': [12, 30],
 'o:team': ['Falcons', 'Saints'],
 'points': [30, 13],
 'season': [2011, 2011],
 'site': ['home', 'away'],
 'team': ['Bears', 'Bears'],
 'total': [40.5, 47.0],
 'week': [1, 2]}

Then in pandas:

RE: Make a table from a json output - carstenlp - Jan-01-2019

(Dec-30-2018, 11:04 PM)snippsat Wrote: ...

Hi snippsat

Thx for input. Have heard about panda before, and its on my to do list to look more into. Think i can use it to a lot.

But when i print(df) i get this:

       date     day  line  o:points ...   site   team  total week
0  20110911  Sunday   2.0        12 ...   home  Bears   40.5    1
1  20110918  Sunday   6.0        30 ...   away  Bears   47.0    2

[2 rows x 11 columns]

How do i get it to print all 11 columns ?

Also i problably didnt explain it good enough.
For row 0 i want points and o:points to be added, and that showed in new column 12 named Sum.
Same with 1 etc.

I got ideas for about 5 columns more, all data for those columns is from calculations, so think if i just learn to do one of them i can always add on.

/ Carsten

RE: Make a table from a json output - snippsat - Jan-01-2019

(Jan-01-2019, 08:30 PM)carstenlp Wrote: How do i get it to print all 11 columns ?

It can be better to work in Jupyter Notebook with pandas DataFrame.
All the data is still there,it's just dos it for easier display of long DataFames.
Will force it to display any number of columns.

pandas.set_option('display.max_columns', None)

Also a great editor like VS Code can run Notebook Cell(#%%) inline in Editor.
Also no need for print().
VS Code from start