Make JSON into CSV with Python from Splunk

Display the status of COVID19 infection in Japan with Splunk (Revised 2nd edition) uses python to download data from HP and process it into CSV.

Splunk doesn't have pandas, and it seems that the threshold is high for Windows people, so I tried my best with python in Splunk.

code

dl_json_to_csv2.py


#!/usr/bin/env python
# -*- coding: utf-8 -*-

import requests
import json
import csv

headers={'accept': 'application/json', 'content-type': 'application/json'}
url = 'https://www3.nhk.or.jp/news/special/coronavirus/data/47patients-data.json'

response=requests.get(url,headers=headers)
json_obj = response.json()

##Storage of date data
header=["pref"]+[i for i in list(json_obj['category'])]

##Storage of infected person number data
lst = [[] for _ in range(len(json_obj['data47']))] ##Initialization
for j,json in enumerate(json_obj['data47']):
    lst[j].append(json['name'])
    for json2 in json['data']:
        lst[j].append(json2)

##CSV write
with open('japan_covid19.csv', 'w', encoding='UTF-8',newline='') as f:
    writer = csv.writer(f)
    writer.writerow(header)
    writer.writerows(lst)

I managed to get the above code

splunk


$SPLUNK_HOME/bin/splunk cmd python3 ./dl_json_to_csv2.py

Operation confirmed with

Correction by pythonista

dl_json_to_csv2.py


#!/usr/bin/env python
# -*- coding: utf-8 -*-

import requests
import json
import csv

headers={'accept': 'application/json', 'content-type': 'application/json'}
url = 'https://www3.nhk.or.jp/news/special/coronavirus/data/47patients-data.json'

response=requests.get(url,headers=headers)
json_obj = response.json()

lst = [['pref']+json_obj['category']] + [[json['name']]+json['data'] for json in json_obj['data47']]

with open('japan_covid19.csv', 'w', encoding='UTF-8',newline='') as f:
        writer = csv.writer(f)
        writer.writerows(lst)

: astonished: Too advanced skills are magic.

Commentary

python


a=['1']
b=['2','3']
c=[a+b]
d=['4']
c+d

The result of [['1', '2', '3'], '4'] If you enclose list in[], it becomes a list in the list.

python


d={'data47':[{'name':'4','data':['5','6','7']},{'name':'8','data':['9','10','11']}]}
e=[[json['name']] + json['data'] for json in d['data47']]
e

The result of [['4', '5', '6', '7'], ['8', '9', '10', '11']]

Therefore

python


a=['1']
b=['2','3']
c=[a+b]
d={'data47':[{'name':'4','data':['5','6','7']},{'name':'8','data':['9','10','11']}]}
e=[[json['name']] + json['data'] for json in d['data47']]
c+e

[['1', '2', '3'], ['4', '5', '6', '7'], ['8', '9', '10', '11']]

Finer

ʻE` is a list

--Whole inclusion: Expression with variables for variables in list --Expression: [json ['name']] + json ['data'] --Variable: json --List: d ['data47']

For example

python


d={'data47':[{'name':'4','data':['5','6','7']},{'name':'8','data':['9','10','11']}]}
e=[json['name'] + json['data'] for json in d['data47']]
e

Then Type Error: can only concatenate str (not" list ") to str. The return of json ['name'] is str, whereasjson ['data']is list list You can't combine unless you are comrades.

: smiling_imp: I understand something.

Summary

I made it while checking which array was used with type (). I was addicted to making a list in the list, and it turned out to be awkward code. ~~ Currently being corrected. ~~ I had pythonista correct it.

~~ When it's clean, I'd like to use it for the ~~ Splunk dashboard.

Recommended Posts

Make JSON into CSV with Python from Splunk
Make apache log csv with python
Precautions when inputting from CSV with Python and outputting to json to make it an exe
Csv output from Google search with [Python]! 【Easy】
Make Python scripts into Windows-executable .exes with Pyinstaller
[Python] Use JSON with Python
Make OpenCV3 available from python3 installed with pyenv
Generate an insert statement from CSV with Python.
Csv tinkering with python
[Part.3] Crawling with Python! It's JSON rather than CSV! ??
[AWS] Make friends with Lambda's JSON input (Python version)
How to convert JSON file to CSV file with Python Pandas
Python script to create a JSON file from a CSV file
Remove headings from multiple format CSV files with python
Read csv with python pandas
POST json with Python3 script
Make MeCab available from Python3
Make Puyo Puyo AI with Python
Make a fortune with Python
Format json with Vim (with python)
With skype, notify with skype from python!
Download csv file with python
Read json data with python
Notes on importing data from MySQL or CSV with Python
Stylish technique for pasting CSV data into Excel with Python
[Python] Make AWS resources mocked with Moto into pytest fixtures
I tried to make a generator that generates a C # container class from CSV with Python
Make Echolalia LINEbot with Python + heroku
Call C from Python with DragonFFI
Using Rstan from Python with PypeR
[Python] Write to csv file with Python
Let's make a GUI with python.
Create folders from '01' to '12' with python
Output to csv file with Python
JSON encoding and decoding with python
Make a recommender system with python
Run Aprili from Python with Orange
Handle Excel CSV files with Python
Call python from nim with Nimpy
Let's make a graph with python! !!
Reading and writing CSV with Python
Read fbx from python with cinema4d
[Python] Convert CSV file uploaded to S3 to JSON file with AWS Lambda
Make multiple numerical elevation data into a single picture with Python
How to import CSV and TSV files into SQLite with Python
Extract database tables with CSV [ODBC connection from R and python]
Make the Python console covered with UNKO
Collecting information from Twitter with Python (Twitter API)
2. Make a decision tree from 0 with Python and understand it (2. Python program basics)
Receive textual data from mysql with python
Get html from element with Python selenium
INSERT into MySQL with Python [For beginners]
[Note] Get data from PostgreSQL with Python
Let's make a shiritori game with Python
Data input / output in Python (CSV, JSON)
Create wordcloud from your tweet with python3
[Data science basics] I tried saving from csv to mysql with python
Read CSV file with python (Download & parse CSV file)
Python: Reading JSON data from web API
Convert from PDF to CSV with pdfplumber
Put protocol buffers into sqlite with python