Python Forum
Replacing values for specific columns in Panda data structure
Thread Rating:
  • 1 Vote(s) - 1 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Replacing values for specific columns in Panda data structure

Im trying to replace invalid values ( x< -3 and x >12) with 'nan's in a pandas data structure . The first two columns consist of ids and names respectively, and should not be modified. I want to make a general code for data with an unknown amount of column values, I know that the first two columns are ids and names but don't know the amount columns of number data that I will have to deal with.

My question is: How would I be able to modify data, replacing invalid values with nans for the third column and onwards (range(2,Columns)) ?

Using below mentioned code replaces all names and ids with nan, but does not do anything to the number data.

Data[(Data>=12) & (Data<=-3] = np.nan 
Using below mentioned code replaces all rows, from 2 to 6, with nan values.

Data[(Data.loc[range(2,Columns)] >=12) & (Data.loc[range(2,Columns)]<=-3)] = np.nan
I have uploaded a csv file..

Kind regards

The entirety of the code;

import numpy as np
import chardet
import pandas as pd

# Open and read csv file 
with open('testfilex1w.csv', 'rb') as f:
    #detect encoding of csv file, assigning encoding to -Result-
    Result = chardet.detect(
# Use panda to read csv file relative to above detected encoding    
Data = pd.read_csv('testfilex1w.csv', encoding=Result['encoding'], header= None)

#Display duplicates (student ids) found in pd.dataframe -Data-
print(Data[Data.duplicated([0], keep=False)])

# Drop duplicated rows based on IDs[0] and Names[1], IDS and Names are defined
# below

Data = Data.drop_duplicates([0], keep='last')
Data = Data.drop_duplicates([1], keep='last')

# Compute number of rows and columns of original data
Columns = len(Data.columns)
Rows    = len(Data.index)

# Create a selection of columns to group data
Ids = Data.iloc[:, 0]
Names = Data.iloc[:, 1]

# The amount of grades columns is unknown in the assignment, I must therefore
# create code that will work with x amount of columns
Grades = Data.iloc[:, range(2,Columns)]

# Compute number of rows and columns of dupliate ridden grades
Gradecols = len(Grades.columns)
Graderows =len(Grades.index)

Elements = (Gradecols * Graderows)

# Create a "memory" array of ones for indexing, in order to remove rows from
# original grades data by modifying this array with zeros based on below for/ if loop.
#Valid = pd.DataFrame(np.ones((len(Grades.index),len(Grades.columns))))

# Create variable to keep track of rows, where invalid data might exist
Colcount = 0
Gradecount = 0

# Create matrix array of grades... because I cant figure out how to iterate over
# a pandas dataframe :(
Gradesnp = Grades.values
# For loop with if statements in order to find occurences of values out of 
# range and modify the memory array with zeros so that I can remove out of range
# values from orginal grades data.

for row in Gradesnp:
    for item in row:
        if(item < -3.0 or item > 12.0):
            print("Invalid grade found! Grade was {} in Line {}.".format(item, Rowcount))
            #modify -Valid- array per index, with zero if the if statement is satisfied.


# Through the use of Boolean indexing, the data array is modified by the 
# memory vector(-Valid-) so that values corresponding to 0 are excluded and values 
#corresponding to 1 are included.

#Data[(Data>=12) & (Data<=-3] = np.nan
#Data[(Data.iloc[:, range(2,Columns)]>=12) & (Data.iloc[:, range(2,Columns)]<=-3)] = np.nan

#Data.loc[range(2,Columns)] = np.nan  


#Data[(Data.loc[range(2,Columns)] >=12) & (Data.loc[range(2,Columns)]<=-3)] = np.nan

Attached Files

.csv   testfilex1w.csv (Size: 745 bytes / Downloads: 166)
You guys are slow.

#Replace invalid grades with Nans

Data.iloc[:,2:] = Data.iloc[:,2:].where((Data < 12) & (Data > -3))

Possibly Related Threads…
Thread Author Replies Views Last Post
  Replacing "." in columns donnertrud 1 864 Jul-26-2021, 07:15 AM
Last Post: warnerarc
  Adding a new column to a Panda Data Frame rsherry8 2 435 Jun-06-2021, 06:49 PM
Last Post: jefsummers
  import columns of data from local csv file CatherineKan 2 1,192 May-10-2021, 05:10 AM
Last Post: ricslato
  pandas.to_datetime: Combine data from 2 columns ju21878436312 1 724 Feb-20-2021, 08:25 PM
Last Post: perfringo
  how to filter data frame dynamically with the columns psahay 0 777 Aug-24-2020, 01:10 PM
Last Post: psahay
  Can the data types be different for different columns? Robotguy 2 919 Aug-19-2020, 09:24 PM
Last Post: Robotguy
  Is there a better data structure than classes for a set of employes? Schlangenversteher 5 1,092 Feb-26-2020, 11:43 AM
Last Post: buran
  Identifying consecutive masked values in a 3D data array chai0404 12 2,371 Feb-01-2020, 12:59 PM
Last Post: perfringo
  How to filter data using a panda.DateFrame.loc pawlo392 1 1,490 May-27-2019, 08:47 PM
Last Post: michalmonday
  Data saving structure JosefFilosopio 0 1,015 May-04-2019, 10:48 AM
Last Post: JosefFilosopio

Forum Jump:

User Panel Messages

Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020