python Searching table/.csv from column, returning resulting row

Question

I have the following table in a .csv file:

1,123 Sesame Street,Imaginary,XL,Seven Eleven #398,,

2,124 Sesame Street,Imaginary,XL,Seven Eleven #399,,

3,125 Sesame Street,Imaginary,XL,Seven Eleven #400,,

4,126 Sesame Street,Imaginary,XL,Seven Eleven #401,,

5,127 Sesame Street,Imaginary,XL,Seven Eleven #402,,

6,128 Sesame Street,Imaginary,XL,Seven Eleven #403,,

7,129 Sesame Street,Imaginary,XL,Seven Eleven #404,,

8,130 Sesame Street,Imaginary,XL,Seven Eleven #405,,

9,131 Sesame Street,Imaginary,XL,Seven Eleven #406,,

10,132 Sesame Street,Imaginary,XL,Seven Eleven #407,,

11,133 Sesame Street,Imaginary,XL,Seven Eleven #408,,

12,134 Sesame Street,Imaginary,XL,Seven Eleven #409,,

13,135 Sesame Street,Imaginary,XL,Seven Eleven #410,,

14,136 Sesame Street,Imaginary,XL,Seven Eleven #411,,

15,137 Sesame Street,Imaginary,XL,Seven Eleven #412,,

16,138 Sesame Street,Imaginary,XL,Seven Eleven #413,,

The table breaks down into the following columns:

DeviceNumber,DeviceStreetAddress,DeviceCity,DeviceState,DeviceStoredAt,DeviceConnect,Keys

I am attempting to search the table (which has several thousand entries) for a certain DeviceNumber or DeviceStreetAddress in their respective columns. The search should return the entire row containing the searched information to give me the additional information about the device that I am searching for. Preferably this would be returned in a list rather than a string.

I have found some bits and pieces of code that I have attempted, but I usually end up with an error like:

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xd0 in position 0: invalid >continuation byte or NameError: name 'column' is not defined etc.

I have the following code currently after doing a lot of banging my head against a wall and starting over and over again:

import pandas as pd
df = pd.read_csv('path/to/file.csv')
dev_id = df[0] #this should theoretically give me a list of the device ids
dev_address = df[1] #this should theoretically give me a list of addresses

TBH, I don't really even know what I'm technically trying to do anymore, or rather how to go about this. If someone can help me out a bit, I'd greatly appreciate it!

PacketLoss · Accepted Answer · 2020-02-03 03:39:33Z

1

You need to call each column by its name, and would need to make sure your column names are set when importing.

An easy way to do this, is to put the following line at the top of your csv as pandas will automatically use the first line as the column names.

DeviceNumber,DeviceStreetAddress,DeviceCity,DeviceState,DeviceStoredAt,DeviceConnect,Keys

Then, you can simply call the following, which should work;

dev_id = df['DeviceNumber']
dev_address = df['DeviceStreetAddress']

To achieve the result you seem to be looking for, where is you search an address and then find a device ID, you can define a function where you pass the dataframe and query and have a result returned.

import pandas as pd

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


def locate(df, query):
    result = df.loc[(df['DeviceStreetAddress'].str.lower() == query)]['DeviceNumber'].reset_index(drop=True)

    return result[0] if result.any() else 'Not Found'


print(locate(df, input('Address :')))

Output:

Address :123 sesame street
1
>>>

Address :11
Not Found
>>>

edited Feb 3, 2020 at 3:39

answered Feb 2, 2020 at 23:57

PacketLoss

5,7661 gold badge12 silver badges29 bronze badges

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

5 Comments

D3alWyth1t Over a year ago

I have added the headers to the file and edited my code to be as follows:

import pandas as pd df = pd.read_csv('C:/path/DataStuff.csv') dev_id = df['DeviceNumber'] dev_address = df['DeviceStreetAddress'] search_for = df.filter(input('What are you looking for? ', dev_address, dev_id)) print(search_for)

However, I get the following error: TypeError: input expected at most 1 arguments, got 3 What would be the syntax for searching in these two columns simultameously? Should I be using a nested 'for' loop? I'm not familiar with the pandas library.

PacketLoss Over a year ago

What are you trying to achieve?

D3alWyth1t Over a year ago

I work in a call center, and I have spreadsheets of various devices that we have with the information on said devices. When I answer the phone, I am given either an address or a device number, and then have to search the spreadsheet for the correct device. I have other things that I need to do with that data once I have it, but I have the other aspects sorted. I need to be able to pull the row of data on a certain device and then use (particularly the dev number) as a variable in another part of my program. Does that make sense?

D3alWyth1t Over a year ago

As I understand the documentation it needs to be something more like this:

import pandas as pd df = pd.read_csv('C:/path/DataStuff.csv') #assiging columns to variables for searching dev_id = df['DeviceNumber'] dev_address = df['DeviceStreetAddress'] search_for = input('What are you looking for? ') #defining the search to go between these columns display = df.loc[search_for, dev_address:dev_id]#This should obtain the row from our search from search_for print(display)

However, this only gets errors. Do you see what I'm getting at?

PacketLoss Over a year ago

Check my edit, if you just want to search for results attached to address, you can perform that search.

Collectives™ on Stack Overflow

python Searching table/.csv from column, returning resulting row

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related