1

I have a CSV file of stock price data that I would like to put into a dictionary containing the Date and Close price.

Here is what the CSV looks like: date close volume open high low 2017/09/22 151.89 46575410 152.02 152.27 150.56 2017/09/21 153.39 37350060 155.8 155.8 152.75 2017/09/20 156.07 52126240 157.9 158.26 153.83 2017/09/19 158.73 20565620 159.51 159.77 158.44

I would like the end dictionary to be arranged like this:

perfect_dict = [
{'Date': '2017/09/22', 'Close': '151.89'},
{'Date': '2017/09/21', 'Close': '153.39'},
...]

My current code grabs the CSV data and creates two separate lists for the dates and the close prices. I've tried using dict(zip(dates, close_prices) but that doesn't format the new dictionary the way I mentioned above. This is my code:

import csv
from collections import defaultdict

# --->
columns = defaultdict(list)

with open('mydata.csv') as f:
    reader = csv.DictReader(f) 
    for row in reader: value2,...}
        for (k,v) in row.items(): 
            columns[k].append(v) 

dates = columns['date']
close_prices = columns['close']

# This is what doesn't format it right
#stock_dict = dict(zip(dates, close_prices))
#pprint.pprint(stock_dict)

If anyone could point me in the right direction that would be awesome, thanks!

jblew
  • 274
  • 1
  • 8
  • 21
  • I have a question. Why is your comma-separated values file not separated by commas? – hyper-neutrino Sep 26 '17 at 01:54
  • There are two things which are wrong. You are expecting dictionary of dictionaries as output. However, there is NO key associated with each sub-dictionary. Also you dont have comma in csv file. – Abhishek Kulkarni Sep 26 '17 at 02:23
  • @HyperNeutrino I see what you mean, lol. This data is just copied a Google Spreadsheet that's why – jblew Sep 26 '17 at 02:29

4 Answers4

3

You can use dictionary comprehension:

import csv

data = list(csv.reader(open('filename.csv')))
final_data = [{a:b for a, b in zip(["Date", "Close"], i[:2])} for i in data[1:]]

Note that you cannot store the dictionaries in a set as the dictionaries themselves are unhashable.

Ajax1234
  • 69,937
  • 8
  • 61
  • 102
  • This does not work; it just makes a list of maps from `"Date"` to that row as a string. Not helpful to OP... **edit** I think my CSV wasn't comma-separated... sorry – hyper-neutrino Sep 26 '17 at 01:53
  • @HyperNeutrino no, this should work. The OP requests that listing of dictionaries be created with keys "Date" and "Close", which this code creates. However, if there is a glaring syntax error that I have not seen please let me know. – Ajax1234 Sep 26 '17 at 02:00
2

I don't think the format you are aiming for is possible - do you mean to say that you want a list of dictionaries? As written, this is a dictionary of dictionaries but the outer dictionary does not have any keys.

Additionally, if you want to set the value for a given dictionary key, you may want to do something like:

columns[k] = v

EDIT:

Does this get closer to what you're looking for? Instantiate columns as an empty list, and then format each row of your csv as a dictionary and append to that list.

columns = []

with open('mydata.csv') as f:
    reader = csv.DictReader(f) 
    for row in reader:
        row_as_dict = {k: v for k, v in row.items()}
            columns.append(row_as_dict)
speedyturkey
  • 126
  • 9
  • I think a list of dictionaries would be better, yes. I don't know what that would look like exactly, but i just want each date and it's corresponding close price to be individual entities that make up an overall list. Is that possible? – jblew Sep 26 '17 at 02:28
  • Additional info added. The output should now look more like: list_of_dicts = [ {'Date': '2017/09/22', 'Close': '151.89'}, {'Date': '2017/09/21', 'Close': '153.39'}, ...] – speedyturkey Sep 26 '17 at 02:41
  • I ran the code you added at the bottom, and it returns an error: `columns[k].append(row_as_dict) NameError: name 'k' is not defined` – jblew Sep 26 '17 at 11:03
  • Sorry, just need to remove the [k] - see updated answer. – speedyturkey Sep 26 '17 at 12:04
1

By using pandas to read the csv file

  • first read the date and close column and store as a list
  • than make a list of dictionary which format we needed.

The code

import pandas as pd
df = pd.read_csv("file_name.csv")
# read the date and close column and store as a list.
time_list = list(df['date'])
close_list = list(df['close'])
perfect_dict = []
# here take the minimum length
# because avoiding index error
take_length = min(len(time_list),len(close_list))
for i in range(take_length):
    temp_dict={}
    temp_dict["Date"]=time_list[i]
    temp_dict["Close"] = close_list[i]
    perfect_dict.append(temp_dict)
print(perfect_dict)

The another possible way.

import csv
perfect_dict=[]
with open('file.csv') as f:
    reader = list(csv.reader(f))
    for row in reader[1:]:
        temp_dict = {}
        temp_dict["Date"] = row[0]
        temp_dict["Close"] = row[1]
        perfect_dict.append(temp_dict)
print(perfect_dict)
R.A.Munna
  • 1,699
  • 1
  • 15
  • 29
1

Maybe a litte late, but you may try the following solution with a "normal" csv reader and transform the data later on:

columns = list()
with open('mydata.csv') as f:
    reader = list(csv.reader(f))
    header = reader[0]
    for row in reader[1:]:
        temp_dict = dict()
        for idx, item in enumerate(row):
            if idx < 2:
                temp_dict[header[idx]] = item
        columns.append(new_dict)

Assuming your csv is structured as you presented (header as first row and the order of columns), the code converts a raw csv input into a list of dictionaries. Moreover, idx < 2 ensures that only "date" and "close" is mapped to the new output.
If you prefer capitalised column headers, just add header = list(map(lambda x: x.capitalize(), header)) after line 4.

Amateur
  • 113
  • 2
  • 5