CODEWITHSUNDEEP
python
Brian
Brian

Reputation: 737

How to parse CSV file and search by item in first column

I have a CSV file with over 4,000 lines formatted like...

name, price, cost, quantity

How do I trim my CSV file so only the 20 names I want remain? I am able to parse/trim the CSV file, I am coming up blank on how to search column 1.

Upvotes: 3

Views: 212

Answers (4)

Andy M
Andy M

Reputation: 6065

Not sure I understood you right but, can the snippet below do what you want ?

def FilterCsv(_sFilename, _aAllowedNameList):
  l_aNewFileLines = []
  l_inputFile = open(_sFilename, 'r')
  for l_sLine in l_inputFile:
    l_aItems = l_sLine.split(',')
    if l_aItems[0] in _aAllowedNameList:
      l_aNewFileLines.append(l_sLine)
  l_inputFile.close()

  l_outputFile = open('output_' + _sFilename, 'w')
  for l_sLine in l_aNewFileLines:
    l_outputFile.write(l_sLine)
  l_outputFile.close()

Hope this can be of any help!

Upvotes: 1

vk1011
vk1011

Reputation: 7179

Use pandas!

import pandas as pd

df = pd.DataFrame({'name': ['abc', 'ght', 'kjh'], 'price': [7,5,6], 'cost': [9, 0 ,2], 'quantity': [1,3,4]})

df = pd.read_csv('input_csv.csv') # Your case you would import like this

>>> df

   cost name  price  quantity
0     9  abc      7         1
1     0  ght      5         3
2     2  kjh      6         4

>>> names_wanted = ['abc','kjh']

>>> df_trim = df[df['name'].isin(names_wanted)]

>>> df_trim

      cost   name   price  quantity
  0      9    abc       7         1
  2      2    kjh       6         4

Then export the file to csv:

>>> df_trim.to_csv('trimmed_csv.csv', index=False)

Done!

Upvotes: 2

Todd Minehardt
Todd Minehardt

Reputation: 241

You could create an ASCII test file with each of the 20 names on a separate line (perhaps called target_names). Then, with your CSV file (perhaps called file.csv), on the command line (bash):

for name in $(cat target_names); do grep $name file.csv >> my_new_small_file.csv; done

If you have issues with case sensitivity, use grep -i.

Upvotes: 1

Robert Jacobs
Robert Jacobs

Reputation: 3360

You can loop though the csv.reader(). It will return you rows. Rows consist of lists. Compare the first element of the list ie row[0]. If it is what you want, add the row to an output list.

Upvotes: 1

Related Questions