CODEWITHSUNDEEP
pythonpandas
sdiabr
sdiabr

Reputation: 509

Pandas, reading excel column values, but stop when no more values are present in that column

I want to import some values from an excel sheet with Pandas.

When I read values with Pandas, I would like to read column by column, but stop reading values when the rows of each column are empty.

Since in my excel file different columns have different number of rows, what I am getting now are arrays with some numbers, but then filled up with "nan" values until they reach the maximum number (i.e., the number rows of the excel column having the greatest number of rows)

I hope the explanation was not too confusing.

The code snippet is not a great example, it is not reproducible, but hopefully will help understanding what I am trying to do.

In the second part of the snippet (below #Removing nan) I was trying to remove the "nan" after having already imported them, but that was not working either, I was getting this error: ufunc 'isnan' not supported for the input types, and the inputs could not be safely coerced to any supported types according to the casting rule ''safe'' The same happened with np.isfinite

df = pandas.read_excel(file_name)

for i in range(number_of_average_points):

    #Reading column values (includes nan)
    force_excel_col = df[df.columns[index_force]].values[13:]
    acceleration1_excel_col = df[df.columns[index_acceleration1]].values[13:]
    acceleration2_excel_col = df[df.columns[index_acceleration2]].values[13:]

    #Trying to remove nan
    force.append(force_excel_col[np.logical_not(np.isnan(force_excel_col))])
    acceleration1.append(acceleration1_excel_col[np.isfinite(acceleration1_excel_col)])
    acceleration2.append(acceleration1_excel_col[np.isfinite(acceleration2_excel_col)])

Upvotes: 0

Views: 1514

Answers (1)

Josh Friedlander
Josh Friedlander

Reputation: 11657

This might be doable, but it is not efficient and bad practice. Having NaN data in a dataframe is a regular part of any data analysis in Pandas (and in general).

I'd encourage you rather to read in the entire excel file. Then, to get rid of all NaNs, you can either replace them (with 0s, for example), using Pandas' builtin dropna() method, or even drop all rows from your dataframe that contain NaN values. Happy to expand on this if you are interested.

Upvotes: 1

Related Questions