0

With a data like this (where each row has 3 data values) from Excel:

A   400     398       0

B   265      0        126

C   0          0          2

D   0          0          0

E   200     300     250

I want to do the following:
If all values are 0, keep whole row [eg. row D]
If all values are less than 70, keep whole row [eg. rows C and D]
If all values are greater than 70, keep whole row [eg. row E]
But if most of the row has values greater than 70 except for 1 or 2 values that are less than 70, change those small values to NA [eg. rows A and B]

(This is all being imported from a CSV Excel file with about 4300 rows of varying degrees of A-E)

Thank you!

brynh
  • 11
  • 3
  • 1
    What have you tried so far? How are you importing the data from the Excel? Can you show us what you have? – francisco sollima Jan 30 '18 at 17:43
  • I'm very new to Python so I'm not really sure the best way about it. I've been importing using 'import csv' and trying various if-else statements but I'm unsure the right commands to keep a row the same and/or change just one value to NA. But I've been fiddling with stuff like:import csv with open('Test.csv') as csvfile: reader = csv.DictReader(csvfile) for row in reader: if row = 0: – brynh Jan 31 '18 at 06:41

1 Answers1

1

Read your excel file into a numpy array. This has already been answered before.

Assuming your imported data is in_data

in_data = np.array([[400, 398, 0],[265, 0 ,126],[0,0,2], [0,0,0],[200,300, 250]])

index = [np.all(in_data[i] > 70) | np.all(in_data[i] < 70) for i in range(len(in_data))] print in_data[index]

array([[ 0, 0, 2], [ 0, 0, 0], [200, 300, 250]])

skmth
  • 164
  • 6
  • my Excel file has over 4000 rows to it, so I don't really want to type out every single value for each row. – brynh Jan 31 '18 at 00:27
  • added link to creating numpy array from csv file – skmth Jan 31 '18 at 18:36
  • alright, thanks, I put it into an array. but unless I'm missing something, this doesn't really do what I wanted help with. I'm trying to change certain values into NA depending on the other values in the row and this basically just prints out the same numbers as I initially imported. – brynh Feb 05 '18 at 02:54