Bottom Page

Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
 Make a table from a json output
#1
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']}
Quote
#2
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
carstenlp likes this post
Quote
#3
(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
Quote
#4
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.
carstenlp likes this post
Quote
#5
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.
Quote
#6
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.
Quote
#7
(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 :)
Quote
#8
(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:
İmage
Quote
#9
(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
Quote
#10
(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

İmage
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Problem Table Output Phil 4 138 May-19-2019, 12:17 PM
Last Post: Phil
  Transposing Table salihozturk 3 229 Mar-03-2019, 06:55 PM
Last Post: salihozturk
  CiscoConfParse to json output anna 6 361 Feb-12-2019, 10:40 AM
Last Post: anna
  Python - create JSON output dragan979 1 268 Dec-28-2018, 12:20 PM
Last Post: dragan979
  times table aocii 3 436 Dec-26-2018, 01:30 PM
Last Post: metulburr
  make a frequency table davidm 6 358 Dec-23-2018, 12:02 PM
Last Post: davidm
  How to pivote table SriRajesh 1 344 Dec-06-2018, 07:14 AM
Last Post: scidam
  Unexpected output when searching for a string from os.popen output FujiJean 3 500 Oct-02-2018, 11:39 AM
Last Post: volcano63
  json.load() is making json file into string and not python disctionary Heisenberg 2 692 Jun-10-2018, 08:29 AM
Last Post: Larz60+
  Unable to parse JSON output dragan979 1 685 Apr-20-2018, 02:24 PM
Last Post: dragan979

Forum Jump:


Users browsing this thread: 1 Guest(s)