Question 111020 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Filter JSON using bs4 or other and organize in excel using openpyxl

I want to filter a json file from pool.pm

For example, https://pool.pm/wallet/$1111 and I want to get all the "policy"’s and how many time it repeats and organize it on a excel using openpyxl and in the first column the policies and in the 2nd column how many time it repeats

What I have:

import openpyxl
import json
import requests
from bs4 import BeautifulSoup


pooladd = str(input("Insira o address:t"))

api_pool = "https://pool.pm/wallet/{}"
api_pool2 = api_pool.format(pooladd)
data = requests.get(api_pool2).json()

Output:

Policy How Many
f0ff48bbb7bbe9d59a40f1ce90e9e9d0ff5002ec48f232b49ca0fb9a 10
8728079879ce4304fd305b12878e0fcea3b8a3a5435a21b5dec35a11 3
Tags:

2

Answers


  1. 0

    I don’t see tag but in case you’re interested, here is an approach with value_counts :

    #pip install pandas
import pandas as pd

out = (
        pd.Series([t["policy"] for t in data["tokens"]])
            .value_counts()
            .to_frame("How Many")
            .reset_index(names="Policy")
      )

    If you wanna make an Excel spreadsheet, use pandas.DataFrame.to_excel :

    out.to_excel("output_file.xlsx", sheet_name="test_name", index=False)

    # Output :

    print(out)
                                                     Policy  How Many
0  f0ff48bbb7bbe9d59a40f1ce90e9e9d0ff5002ec48f232b49ca0fb9a        10
1  8728079879ce4304fd305b12878e0fcea3b8a3a5435a21b5dec35a11         3
    Login or Signup to reply.
  2. 0

    another implementation!, comments are added to the code.
    Build the dictionary and then write the excel.

    import openpyxl
import json
import requests
from bs4 import BeautifulSoup

pooladd = str(input("Insira o address:t"))

api_pool = "https://pool.pm/wallet/{}"
api_pool2 = api_pool.format(pooladd)
data = requests.get(api_pool2).json()

policies= [e_tkn['policy'] for e_tkn in data['tokens']]
unique_policies=set(policies)
result={}
for each_policy in unique_policies:
    result[each_policy]=policies.count(each_policy)

result=sorted(((value,key) for (key,value) in result.items()),reverse=True)# sort in decending order, reverse=True
print(result)
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws['A1'] = 'Policy' # header
ws['B1'] = 'How Many' # header

row=2
for count,policy in result:
    ws['A' + str(row)]=policy # for each row, for example A2=policy
    ws['B' + str(row)]=count # for each row , for example B2 = count
    row=row+1
    
wb.save('policies.xlsx')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search