Converting text file into json in a specific format ( python )

Question

I have data of some stock in text format and I want to convert it into JSON in a specific format. The data points in the text file are separated by commas (,) and each line contains data of 1 min interval. Also in some lines, there are extra unnecessary data at the end, so I want to make sure after conversion only the six datapoints are present ( excluding the first and any data after the 7th data point)

Input data:

BANKNIFTY_F1,20150228,15:27,19904.65,19924.00,19900.40,19920.20,31225
BANKNIFTY_F1,20150228,15:28,19921.05,19941.30,19921.05,19937.00,31525
BANKNIFTY_F1,20150228,15:29,19932.45,19945.00,19930.10,19945.00,38275
BANKNIFTY_F1,20150228,15:30,19947.00,19949.40,19930.00,19943.80,43400
BANKNIFTY_F1,20150302,09:16,20150.15,20150.15,20021.50,20070.00,91775,2026525
BANKNIFTY_F1,20150302,09:17,20071.50,20085.00,20063.50,20063.50,45700,2026525

Expected output data:

[{"date":"20150228","time":"15:27","open":"19904.65","high":"19924.00","low":"19900.40","close":"19920.20","volume":"31225"},{"date": "20150228", "time":"15:28", "open":"19921.05","high":"19941.30" ,"low":"19921.05","close":"19937.00", "volume":"31525"}, {"date":"20150228","time" :"15:29" ,"open": "19932.45" ,"high" :"19945.00 ","low":"19930.10","close" :"19945.00","volume":"38275"},{"date": "20150228","time ":" 15:30","open ":"19947.00","high" :"19949.40","low":"19930.00" ,"close":"19943.80", "volume":"43400"} , {"date": "20150302","time" :"09:16","open":"20150.15","high ":"20150.15", "low":"20021.50", "close":"20070.00 ","volume":"91775"}, {"date":"20150302", "time": "09:17","open": "20071.50", "high":"20085.00" , "low":"20063.50", "close":"20063.50", "volume": "45700"}

Please note in the expected output the last unnecessary datapoint as shown in the last two input lines is ignored.

The name of input file is "stockdata.txt", I want to read it and convert it into json. The data present in text file is line-by-line and I want the json in continuous format in a dictionary. — Injeel Ahmed
– Injeel Ahmed, Commented Oct 2, 2019 at 7:32
And what's your question exactly ? Where's your code, and what problem do you have with it ? — bruno desthuilliers
– bruno desthuilliers, Commented Oct 2, 2019 at 7:44

Pieter Sap · Accepted Answer · 2019-10-02 08:27:56Z

2

You want to transform a csv file to JSON. When working with CSV files in python, always think about Pandas dataframes. So first install Pandas (pip install pandas).

Read the csv file as a Pandas dataframe, set the column headers to your keys, and then transform to json using the Pandas built-in functionality to_dict. Just a few lines of code.

You will first need to clean out the lines of the file that you do not need. If you only want the first X columns, also use parameters in pd.read_csv to selectd specific columns. Then do this:

import pandas as pd

dataframe = pd.read_csv("stockdata.txt", header = None, names = ["date","time","open","high","low","close","volume"])

// this is a python dictionary
json_dictionary = dataframe.to_dict('records')

print(json_dictionary)

// optionally convert to a json string
json_string = json_dictionary.dumps()

You can alo use pd.read_csv to set specific data types for your columns

edited Oct 2, 2019 at 8:27

answered Oct 2, 2019 at 7:44

Pieter Sap

1793 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Injeel Ahmed Over a year ago

its a text file, not csv

Pieter Sap Over a year ago

The file extension does not matter, the format of your file is obviously csv. This will work, I tested it, and it's clean.

Akash Pagar · Accepted Answer · 2019-10-02 08:17:10Z

1

You can simply do this using file handling in python.

import json
stocks = []

with open('stocks.txt', 'r') as data:
    for line in data:
        line = line.strip()
        ldata = line.split(',')
        temp_stock = {
            'date':ldata[1],
            'time':ldata[2],
            'open':ldata[3],
            'high':ldata[4],
            'low':ldata[5],
            'close':ldata[6],
            'volume':ldata[7]
        }
        stocks.append(temp_stock)
with open('stocks.json', 'w') as fp:
    json.dump(stocks, fp, indent=4)
from pprint import pprint
pprint(stocks)

Or else

with open('stocks.txt', 'r') as data:
    res = [ {
            'date':line.strip().split(',')[1],
            'time':line.strip().split(',')[2],
            'open':line.strip().split(',')[3],
            'high':line.strip().split(',')[4],
            'low':line.strip().split(',')[5],
            'close':line.strip().split(',')[6],
            'volume':line.strip().split(',')[7]
        } for line in data ]

Output:

  'date': '20150228',
  'high': '19924.00',
  'low': '19900.40',
  'open': '19904.65',
  'time': '15:27',
  'volume': '31225'},
 {'close': '19937.00',
  'date': '20150228',
  'high': '19941.30',
  'low': '19921.05',
  'open': '19921.05',
  'time': '15:28',
  'volume': '31525'},
 {'close': '19945.00',
  'date': '20150228',
  'high': '19945.00',
  'low': '19930.10',
  'open': '19932.45',
  'time': '15:29',
  'volume': '38275'},
 {'close': '19943.80',
  'date': '20150228',
  'high': '19949.40',
  'low': '19930.00',
  'open': '19947.00',
  'time': '15:30',
  'volume': '43400'},
 {'close': '20070.00',
  'date': '20150302',
  'high': '20150.15',
  'low': '20021.50',
  'open': '20150.15',
  'time': '09:16',
  'volume': '91775'},
 {'close': '20063.50',
  'date': '20150302',
  'high': '20085.00',
  'low': '20063.50',
  'open': '20071.50',
  'time': '09:17',
  'volume': '45700'}]

edited Oct 2, 2019 at 8:17

answered Oct 2, 2019 at 7:48

Akash Pagar

64711 silver badges28 bronze badges

4 Comments

Injeel Ahmed Over a year ago

Thank you so much brother. Also why is it being printed in new line? Is there anyway to make it continuous?

Akash Pagar Over a year ago

Yes i have used pprint function for formated JSON representation.

Injeel Ahmed Over a year ago

How to finally convert and save this into a json file?

Akash Pagar Over a year ago

@InjeelAhmed for writing that dictionary into JSON file you need to write that using json.dumps and file handling. As above solution is changed.

Yoel Nisanov · Accepted Answer · 2019-10-02 10:46:00Z

0

Assumed all the lines in the text file are built the same way you could iterate on the text file line by line and break it in a strict way like:

my_tokens = []
for line in f.read():
    tokens = line.split(',')
    my_dict = {}
    try:
        my_dict['date'] = tokens[1]
        my_dict['time'] = tokens[2]
        my_dict['open'] = tokens[3]
        my_dict['high'] = tokens[4]
        my_dict['low'] = tokens[5]
        my_dict['close'] = tokens[6]
        my_dict['volume'] = tokens[7]
    except Exception as:
        continue
    my_tokens.append(my_dict)

That's not the prettiest answer but it works on your type of data (:

edited Oct 2, 2019 at 10:46

answered Oct 2, 2019 at 7:45

Yoel Nisanov

1,05410 silver badges17 bronze badges

Collectives™ on Stack Overflow

Converting text file into json in a specific format ( python )

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related