Script not working fully: JSON to EXCEL Python script

Question

I have been looking around, but I cannot seem to find a solid answer to this.

I am using Python3 and I wish to design a JSON to EXcel (not CSV) coverter. I would this would be relatively easy, but it does seem to be giving me issues. It convert the data a bit, but I need to have the data transposed (rows to columns) and have the system generated hash key removed from the resulting excel.

Here is my code so far:

import sys
import pandas as pd

try:
      fileInput = sys.argv[1]
      fileOutput = sys.argv[2]

except:
      print ("\nusage: json-to-excel.py <inputfile.json> <outputfil.xlsx>")
      print ("Note:  Uses the openpyxl library\n")
      sys.exit(1)

pd.read_json(fileInput).to_excel(fileOutput)

Update: The script is working, BUT the data is formatted wrong.

Below is what I mean. The area in brown is the data output, but I want it to be formatted in green (transpose the data and remove the hash keys).

It would be helpful to see what your input and output datasets are to reproduce your issue. Can you provide examples? — Maximilian Press, Apr 28 '20 at 18:59
Hi Maximilian. I have updated my original posting. Any ideas? — , Apr 29 '20 at 05:44

score 0 · Answer 1 · answered Apr 29 '20 at 17:05

It's still a little hard to reproduce without samples of the actual data rather than screenshots of the output, but with regard to the updated question, it appears that what you want to do is transpose a pandas DataFrame:

>>> import pandas as pd
# just a TSV i had lying around
>>> test = pd.read_csv("~/Downloads/test.tsv", sep="\t")
>>> test
                 BAM  abc_test.bam
0          num_reads       107.000
1          zero_pairs      0.355
2          10k_pairs       0.009
3          different_ctg   0.047
4          split           0.028
5          duplicate       0.009
>>> test.transpose()
                      0           1          2              3      4          5
BAM           num_reads  zero_pairs  10k_pairs  different_ctg  split  duplicate
abc_test.bam        107       0.355      0.009          0.047  0.028      0.009

# if you need to reorder the columns:
>>> test.transpose().loc[:, [5,4,3,2,1,0]]
                      5      4              3          2           1          0
BAM           duplicate  split  different_ctg  10k_pairs  zero_pairs  num_reads
abc_test.bam      0.009  0.028          0.047      0.009       0.355        107

Does that solve your issue? With regard to writing without an index, which it seems you may also want, see here.

Script not working fully: JSON to EXCEL Python script

1 Answers1