Getting a value in a row from a column value

Question

I have a csv file that looks like this:

----------------------------------------------------------
|Student ID |   Name   | Username | Password | Moderator |
----------------------------------------------------------    
|   0001    | Foo Bar  |   test   |   abc123  |     N    |
|   0002    |  Baz Qux |   bob    |   bcd986  |     Y    |
----------------------------------------------------------

How would I go about getting a value in a row from a column value ?

For example, if I had the username 'test' how can I get the value of 'Name' in the corresponding row (Foo Bar)?

Do you have these |, - delimiters in file or you just added them for readability? — Anthony
– Anthony, Commented Mar 30, 2018 at 11:43
you read the csv line by line, split it, check for test in 3rd place, if yes return second element — Ankush Rathi
– Ankush Rathi, Commented Mar 30, 2018 at 11:44

michaelg · Accepted Answer · 2018-03-30 11:58:01Z

2

Reading a csv file with pandas

import pandas as pd
# Here you can use pd.read_csv() instead
df = pd.read_clipboard(sep=',')

Accessing value in a row from a column value

df[df.Username == 'test']['Name']

[out]:

0    Foo Bar
Name: Name, dtype: object

If you need the result as a string:

df[df.Username == 'test'].Name.values[0]

[out]:

'Foo Bar'

edited Mar 30, 2018 at 11:58

answered Mar 30, 2018 at 11:50

michaelg

9546 silver badges12 bronze badges

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

2 Comments

newbie Over a year ago

Hello, thanks for the answer. When I try print(df[df.Username == 'admin'].Name.values[0]) I get the error: AttributeError: 'DataFrame' object has no attribute 'Name' however, I done print(df) and the Name is present, df[df.Username == 'admin'].Password.values[0] and df[df.Username == 'admin'].Moderator.values[0] works though. Any idea why Name isn't working? Also how would I get columns with spaces e.g:``df[df.Username == 'admin'].Ful Name.values[0]` returns a syntax error.

michaelg Over a year ago

Try print(df[df.Username == 'admin']['Name'].values[0])

Anthony · Accepted Answer · 2018-03-30 12:43:37Z

1

There are several option to achieve your task. First, as @michaelg proposed, using pandas - it's fast and library handles it very well. Here is a link for pandas. Second option would be a build in csv module. There you can find a short example of scv reader with delimiters. And the third option would be just to treat your .scv file as an ordinary file. For instance,

with open("test.csv", "r") as f:
    data = f.readlines()
    # we skip first line because it is your header,
    # e.g. id, name, pswd, etc.
    for line in data[1:]:
        for field in line.split("|"): # place your delimiter
            # strip data of leading and trailing whitespaces 
            print(field.strip())

You can use this approach to search for you corresponding value. The snippet above would produce this output:

0001
Foo Bar
test
abc123
N

If you want to access values by their indexes, use this:

with open("test.csv", "r") as f:
    data = f.readlines()
    values = [x.split("|") for x in data[1:]]

The above snippet would give you a list of this format [[..], [..],..] where values[0] is your line 1 of your file and values[0][1] = "Foo Bar" name.

edited Mar 30, 2018 at 12:43

answered Mar 30, 2018 at 12:25

Anthony

4191 gold badge5 silver badges13 bronze badges

1 Comment

newbie Over a year ago

Thanks for the alternative csv answer.

Patrick Artner · Accepted Answer · 2018-03-30 12:19:57Z

The format is not really a csv. You could rewrite it into something usefull and parse that with DictReader:

import csv

def rewriteCsv(filename): 
    """Rewrite silly file into something usefull"""
    header = None
    rwfn = "mod_"+filename
    with open (filename,"r") as r, open(rwfn,"w") as w:
        for row in r:
            if row.startswith("---"):
                continue # skip it
            if not header:
                header = ','.join(x.strip() for x in row.split("|") if x.strip())
                w.write(header+'\n')
                continue

            w.write(','.join( x.strip() for x in row.strip()[1:-1].split("|") ) +"\n")
    return rwfn

def createFile(filename):
    """Create the silly file..."""
    with open(filename,"w") as f:
        f.write("""----------------------------------------------------------
|Student ID |   Name   | Username | Password | Moderator |
----------------------------------------------------------    
|   0001    | Foo Bar  |   test   |   abc123  |     N    |
|   0002    |  Baz Qux |   bob    |   bcd986  |     Y    |
----------------------------------------------------------
""")

createFile("file.txt") # create the silly one
fn = rewriteCsv("file.txt") # rewrite into something useful

with open(fn,"r") as r: 
    reader = csv.DictReader(r)
    for row in reader: # access earch ordered dict by columnname
        print(row["Student ID"], row["Name"], row["Username"])

Collectives™ on Stack Overflow

Getting a value in a row from a column value

3 Answers 3

Reading a csv file with pandas

Accessing value in a row from a column value

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Reading a csv file with pandas

Accessing value in a row from a column value

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related