Using Pandas to transfer data from CSV format to JSON with large amount of fieldnames

Question

Trying to take a file that looks like this

and turn it into Json with the below code. It isn't printing the values properly. The values aside from the top columns are print as NULL.

import numpy as np
import pandas as pd
import csv
import json


df = pd.read_csv('1mins data.csv', 'r', ",")
df.head()
fieldnames = df.columns[1:-1]
print(fieldnames[:])

jsonfile = open('1mins data.json', 'w')
fieldnames=fieldnames
reader = csv.DictReader( df, fieldnames)
for row in reader:
    json.dump(row, jsonfile)
    jsonfile.write('\n')

After taking the advice given below I've updated the script.

import numpy as np
import pandas as pd
import csv
import json


df = pd.read_csv('1mins data.csv', 'r', ",")
df.head()

df.columns[1:-1].to_json(path_or_buf='1minsdata.json')

Now i received this error,

AttributeError: 'Index' object has no attribute 'to_json'

Fixed it by doing this

    import numpy as np
import pandas as pd
import csv
import json


df = pd.read_csv('1mins data.csv', 'r', ",")
df.set_index('Time')df.to_json()

df.to_json(path_or_buf='1mins data.json')
df.to_json()

Thanks in advance,

Answer 1

You can turn a pandas DataFrame into a json string with

df.to_json()

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_json.html