How to select rows in a csv file based on a list

Question

I have been met with a problem. I have a list, called list A, and I have a data frame that has many columns. There are many time points in the first column. I want to select those rows that have the same values from list A. I tried to set the first column as an index. But somehow, it didn't work.

Any ideas as to what I'm doing wrong?

A=[8.1, 11.7, 13.475, 14.855, 15.125, 17.465, 19.82, 24.55]

First column is "Time". It's

[24.5,24.505,24.51,24.515,24.52,24.525,24.53,24.535,24.54,24.545,24.55,
24.555,24.56,24.565,24.57,24.575,24.58]

Those numbers from list A comes from the big list. Now I want to select those rows based on those numbers. — Ben Wang, Aug 02 '18 at 15:09
"_But somehow, it didn't work_". What didn't work? Let's see your code. — Kevin, Aug 02 '18 at 15:23
import pandas as pd import numpy as np coda3 = pd.read_csv('(1) hoof2_01.csv',index_col = 0) coda3 index = coda3.index — Ben Wang, Aug 02 '18 at 15:28
marker_thirteen = coda3.loc[[closest_time_points],['13:-.X', '13:-.Y', '13:-.Z']] — Ben Wang, Aug 02 '18 at 15:28
KeyError("None of [Index([(8.1, 11.7, 13.475, 14.855, 15.125, 17.465, 19.82, 22.5)], dtype='object', name='Time')] are in the [index]",) — Ben Wang, Aug 02 '18 at 15:29
the closest_time_points is list A. coda3 is the df. I used the first column as index — Ben Wang, Aug 02 '18 at 15:29
`df.loc[df['Time'].isin(A)]` inspired by answer to [Select rows from a DataFrame based on values in a column in pandas](https://stackoverflow.com/questions/17071871/SELECT-ROWS-FROM-A-DATAFRAME-BASED-ON-VALUES-IN-A-COLUMN-IN-PANDAS) — Steven Rumbalski, Aug 03 '18 at 04:06

Gigaflop · Answer 1 · 2018-08-02T15:14:15.087

-1

If you want the value,

for item in longList:
    if item in A:
        #The list item is found somewhere within the list A

Alternatively, if you want the index and/or the value,

for i in range(len(longList)):
    if longList[i] in A:
        #Index of item is i, value is longList[i]

edited Aug 02 '18 at 15:14

answered Aug 02 '18 at 15:09

Gigaflop

390
1
13

Thanks. I also want to choose the rows based on the values we get from list A – Ben Wang Aug 02 '18 at 15:15
OP has a `pandas` `Dataframe`. Even if the OP didn't have that data structure your algorithm is inefficient. Better would be `set_A = set(A); found = [item for item in longlist if item in set_A`]. Look ups into a set are much more efficient than linear search into a list. But that's neither here nor there because `pandas` will end up with a very different looking solution. – Steven Rumbalski Aug 02 '18 at 15:19
@StevenRumbalski I filter out questions for `pandas`, this seems to have slipped through. Also, I didn't read the entire list of items (way too big) and assumed that there may have been duplicate values. – Gigaflop Aug 02 '18 at 15:40
They're time points so there is no duplicate value. I deleted some data. thanks – Ben Wang Aug 02 '18 at 15:51

How to select rows in a csv file based on a list

1 Answers1