Python how to read csv by column

Question

i have a csv like this:

USD, JPY, BGN, CZK, DKK, GBP, HUF, PLN, RON, SEK, CHF, ISK, NOK, HRK, RUB, TRY, AUD, BRL, CAD, CNY, HKD, IDR, ILS, INR, KRW, MXN, MYR, NZD, PHP, SGD, THB, ZAR, 
1.0867, 118.33, 1.9558, 26.909, 7.4657, 0.87565, 354.76, 4.5586, 4.8330, 10.9455, 1.0558, 155.90, 11.2143, 7.6175, 80.6900, 7.3233, 1.7444, 5.5956, 1.5265, 7.6709, 8.4259, 17243.21, 3.8919, 82.9275, 1322.49, 26.0321, 4.7136, 1.8128, 54.939, 1.5479, 35.665, 19.6383,

How do i read it column by column? so that i could have the currency name and its rate at the same time. I want to insert this to mongodb so i want a json object with 'currency' and 'rate'.

It's need a time for reading the csv package docs, OP could do it self. — frost-nzcr4
– frost-nzcr4, Commented Apr 17, 2020 at 14:05

Eric M · Accepted Answer · 2020-04-17 14:02:44Z

3

This is very simple if you are willing to use Pandas:

import pandas as pd

df = pd.read_csv('your_file_name.csv')

print(df['USD'])

answered Apr 17, 2020 at 14:02

Eric M

1,42815 silver badges22 bronze badges

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

1 Comment

Eric M Over a year ago

Loop through the whole file like show each column? Try print(df) to show the dataframe.

Mark Tolonen · Accepted Answer · 2020-04-17 17:02:09Z

A concrete example of the output would help. The input has extra trailing commas as well, but if you remove those, here's my guess:

import csv
import json

with open('data.csv',newline='') as f:
    r = csv.DictReader(f,skipinitialspace=True)

    j = []
    for row in r:
        for k,v in row.items():
            j.append({'currency':k,'rate':float(v)})

print(json.dumps(j,indent=2))

Output:

[
  {
    "currency": "USD",
    "rate": 1.0867
  },
  {
    "currency": "JPY",
    "rate": 118.33
  },
  {
    "currency": "BGN",
    "rate": 1.9558
  },
  {
    "currency": "CZK",
    "rate": 26.909
  },
  {
    "currency": "DKK",
    "rate": 7.4657
  },
  {
    "currency": "GBP",
    "rate": 0.87565
  },
  {
    "currency": "HUF",
    "rate": 354.76
  },
  {
    "currency": "PLN",
    "rate": 4.5586
  },
  {
    "currency": "RON",
    "rate": 4.833
  },
  {
    "currency": "SEK",
    "rate": 10.9455
  },
  {
    "currency": "CHF",
    "rate": 1.0558
  },
  {
    "currency": "ISK",
    "rate": 155.9
  },
  {
    "currency": "NOK",
    "rate": 11.2143
  },
  {
    "currency": "HRK",
    "rate": 7.6175
  },
  {
    "currency": "RUB",
    "rate": 80.69
  },
  {
    "currency": "TRY",
    "rate": 7.3233
  },
  {
    "currency": "AUD",
    "rate": 1.7444
  },
  {
    "currency": "BRL",
    "rate": 5.5956
  },
  {
    "currency": "CAD",
    "rate": 1.5265
  },
  {
    "currency": "CNY",
    "rate": 7.6709
  },
  {
    "currency": "HKD",
    "rate": 8.4259
  },
  {
    "currency": "IDR",
    "rate": 17243.21
  },
  {
    "currency": "ILS",
    "rate": 3.8919
  },
  {
    "currency": "INR",
    "rate": 82.9275
  },
  {
    "currency": "KRW",
    "rate": 1322.49
  },
  {
    "currency": "MXN",
    "rate": 26.0321
  },
  {
    "currency": "MYR",
    "rate": 4.7136
  },
  {
    "currency": "NZD",
    "rate": 1.8128
  },
  {
    "currency": "PHP",
    "rate": 54.939
  },
  {
    "currency": "SGD",
    "rate": 1.5479
  },
  {
    "currency": "THB",
    "rate": 35.665
  },
  {
    "currency": "ZAR",
    "rate": 19.6383
  }
]

Cerenia · Accepted Answer · 2020-04-17 14:36:59Z

If you don't wanna import any module you can do it this way (I assume your file is called "csv_currencies"):

with open("csv_currencies") as f:
    # get the two lines seperated by the linebreak character "\n" as strings
    rows = f.read().split("\n")
    # split by "," to get the individual elements into one list per row
    rows = [row_string.split(",") for row_string in rows] 
    # merge the two lists. This results in a list of tuples where whatever
    # was below each other in the original list, are grouped into one tuple
    currency_tuples = list(zip(rows[0], rows[1]))

The result looks like this: [('USD', '1.0867'), (' JPY', ' 118.33'), (' BGN', ' 1.9558'), (' CZK', ' 26.909'), (' DKK', ' 7.4657'), (' GBP', ' 0.87565'), (' HUF', ' 354.76'), (' PLN', ' 4.5586'), (' RON', ' 4.8330'), (' SEK', ' 10.9455'), (' CHF', ' 1.0558'), (' ISK', ' 155.90'), (' NOK', ' 11.2143'), (' HRK', ' 7.6175'), (' RUB', ' 80.6900'), (' TRY', ' 7.3233'), (' AUD', ' 1.7444'), (' BRL', ' 5.5956'), (' CAD', ' 1.5265'), (' CNY', ' 7.6709'), (' HKD', ' 8.4259'), (' IDR', ' 17243.21'), (' ILS', ' 3.8919'), (' INR', ' 82.9275'), (' KRW', ' 1322.49'), (' MXN', ' 26.0321'), (' MYR', ' 4.7136'), (' NZD', ' 1.8128'), (' PHP', ' 54.939'), (' SGD', ' 1.5479'), (' THB', ' 35.665'), (' ZAR', ' 19.6383'), ('', '')]

Note that the last tuple is empty since the linebreak was preceeded by a comma and the last element of the file is a comma as well. You can delete it by calling del(currency_tuples[-1]).

But as others pointed out, there are a variety of libraries that handle csv files so have a look at those if this is an option.

frost-nzcr4 · Accepted Answer · 2020-04-17 14:08:03Z

0

See example from https://docs.python.org/3/library/csv.html#csv.DictReader

import csv
with open('names.csv', newline='') as csvfile:
    reader = csv.DictReader(csvfile)
    for row in reader:
        print(row['USD'])

answered Apr 17, 2020 at 14:08

frost-nzcr4

1,62012 silver badges16 bronze badges

1 Comment

haruhi Over a year ago

no it's not what i want... my data doesn't have column name. i couldn't just insert USD, YEN or sth manually.

Collectives™ on Stack Overflow

Python how to read csv by column

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Related