CODEWITHSUNDEEP
pythoncsvsearch
D3alWyth1t
D3alWyth1t

Reputation: 69

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

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!

Upvotes: 0

Views: 429

Answers (1)

PacketLoss
PacketLoss

Reputation: 5746

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
>>>

Upvotes: 1

Related Questions