erase dataframe's columns containing 'illegal values'

user_9517800 0
Viewed 43

I was quite surprised looking on the web for something so basic, yet, I couldn't find any information regarding the issue.

I have the following dataframe: dataframe image

what i'm looking to do is to erase all columns which have values that are not between 0-1, or they have missing values.

I am not sure if it's very efficient way, but I was attempting to change the values that do not meet the requirements into NaNs, then to do dropna over the DataFrame, yet I was not able to find a way to change those invalid/missing values into NaN.

The code I have so far:

import pandas as pd
import numpy as np


def prepare_data(data_abs_path, cols_abs_path):
    df_crime = pd.DataFrame(data=get_data(data_file_abs_path), columns=organize_columns(columns_file_abs_path))
    df_crime = df_crime.iloc[:, 5:]
    df_crime = delete_nan_columns(df_crime)
    # df_crime = df_crime.apply(verify_valid_cols_values)
    # convert all values to float in order to examine data
    df_crime = df_crime.astype(float)
    # df_crime = df_crime.where(0 > df_crime > 1)
    # df_crime.loc[: , : , 0 > df_crime > 1] = np.nan
    print(df_crime)
    # ..
    return df_crime


def delete_nan_columns(df_crime1):
    df_crime1 = df_crime1.replace('?', np.nan)
    df_crime1 = df_crime1.dropna(axis='columns')
    return df_crime1


# function for apply method
# def verify_valid_cols_values(x):
#     if 1 < float(x[1]) < 0:
#         return np.nan

def verify_valid_cols_values(col):
    [np.nan for x in col if 1 > float(x) > 0]


def delete_invalid_values_cols(df_crime1):
    df_crime1.apply(verify_valid_cols_values, axis='columns')
    df_crime1.dropna()


def organize_columns(cols_abs_path):
    crime_file = open(cols_abs_path, "r")
    list_columns = []
    i = 0
    j = 0
    string_crime = crime_file.read().split('\n')
    for i in range(len(string_crime)):
        if len(string_crime[i]) > 10:
            if string_crime[i][0:10] == "@attribute":
                att_line_splitted = []
                att_line_splitted = string_crime[i].split(' ')
                if len(att_line_splitted) >= 2:
                    list_columns += [str(att_line_splitted[1])]
    return list_columns


def get_data(data_file_abs_path1):
    return np.loadtxt(data_file_abs_path1, dtype=str, delimiter=',')


if __name__ == '__main__':
    # file_path = input("please insert path to the desired folder./home/daniln/Documents/studies/BigData\n
    # format example: /home/danln/Documents/studies/BigData: ")
    # print("Thanks. Initializing files.")
    file_path = "/home/daniln/Documents/studies/BigData/ex3_home"
    columns_file_abs_path = file_path + "/communities.names"
    data_file_abs_path = file_path + "/communities.data"
    df_crime1 = prepare_data(data_file_abs_path, columns_file_abs_path)

Thanks :-)

pandas dataframe python
2 Answers

This might do everything on all your columns

df.loc[df < 0 ] = 5
df.loc[df > 1 ] = 5
df.loc[df == 5] = ''

Basically I am converting all the invalid values to 5 and then dropping all the values equal to 5.

if you want it as a function it could be

  def replace_values(DataFrame):
        DataFrame.loc[DataFrame < 0 ] = 5
        DataFrame.loc[DataFrame > 1 ] = 5
        DataFrame.loc[DataFrame == 5] = ''
        return DataFrame
user_42451920

Few things to notice:

  1. Make sure your data is of a numeric type. To do so, I have used df_crime = df_crime.astype(float, errors='raise') meaning, I wish to transfer data to type float, and wish that in case of receiving an invalid value, raise an exception.

  2. df_crime = df_crime[df_crime >= 0] makes any value smaller than 0 turn to 'NaN'. df_crime = df_crime[df_crime <= 1] makes any value greater than 1 turn to 'NaN'.

  3. df_crime = df_crime.dropna(axis='columns') dropping all columns containing AT LEAST one 'NaN' value.

user_95178000
Related