Culip
Culip

Reputation: 589

Python 3-x Select a column and a row from a CSV table using SQL-like conditions

Here's a CSV file kidsList.csv, comma-delimited, the first row is a header. The first column Id is unique so you can use this as a primary key.

Id,Name,Age
A1,Alice,6
A2,Becca,5
B1,Cindy,7

Now I want to find the name where Id is A2; the answer should be "Becca".

In SQL it is like SELECT Name FROM table WHERE Id = "A2"

How can I do this in Python 3.x? This operation is so simple that I want to use the Standard Library like csv rather than non-standard ones like pandas.

Upvotes: 2

Views: 2037

Answers (2)

puff_the_magic_dragon
puff_the_magic_dragon

Reputation: 41

You can use the csv library to convert the csv file to a 2d list, and then loop through the array:

import csv

key = 'A2'
category = 'Name'

with open('kidsList.csv', 'r') as file:
    contents = list(csv.reader(file))

index = contents[0].index(category)

for i in range(1, len(contents)):
    if contents[i][0] == key:
        print(contents[i][index])
        break

Upvotes: 1

mgrollins
mgrollins

Reputation: 651

I think the csv.DictReader class can be utilized to create a dictionary mapping that you can index by the value of the Id column:

import csv
from collections import OrderedDict

kids = OrderedDict()
_name = 0
_age = 1

with open('kidsList.csv', newline='') as csvfile:
    reader = csv.DictReader(csvfile, fieldnames=("Id",),restkey="data_list")
        for row in reader:
            kids[row["Id"]] = row["data_list"]

print(f"ID = A1 has data: name= {kids['A1'][_name]}, age= {kids['A1'][_age]} ")

# Expected Output:
# ID = A1 has data: name= Alice, age= 6

Upvotes: 2

Related Questions