How can one go about finding the last occurring non zero element in every column of a dataframe?
Input
A B
0 0 1
1 0 2
2 9 0
3 10 0
4 0 0
5 0 0
Output
A B
0 10 2
To select all columns except one column in Pandas DataFrame, we can use df. loc[:, df. columns != <column name>].
Using Count() You can count the number of rows in the dataframe using the count() function as well. count() will not count the NA or missing values. Hence, you can use this when you want to count only the columns with non-empty values.
You can convert 0
to missing values, use forward filling and select last row by indexing, last cast to integer:
df = df.mask(df==0).ffill().iloc[[-1]].astype(int)
print (df)
A B
5 10 2
Here's one approach using ndarray.argmax
and advanced indexing:
first_max = df.values[df.ne(0).values.argmax(0), range(df.shape[1])]
out = pd.DataFrame([first_max], columns=df.columns)
df = pd.DataFrame({'A': [0,0,0,10,0,0] , 'B': [0,2,0,0,0,0]})
first_max = df.values[df.ne(0).values.argmax(0), range(df.shape[1])]
# array([10, 2])
pd.DataFrame([first_max], columns=df.columns)
A B
0 10 2
Update
In order to find the last nonzero:
row_ix = df.shape[0]-df.ne(0).values[::-1].argmax(0)-1
first_max = df.values[row_ix, range(df.shape[1])]
out = pd.DataFrame([first_max], columns=df.columns)
Something like:
results = {}
for column in df.columns:
results[column] = df.loc[df[column]!=0, column].iloc[-1]
This will make a dictionary with all columns as keys and they last non-zero values as values.
EDIT: If you want it in a dataframe, plus dict comprehension for one-liner:
results = pd.DataFrame({column:[df.loc[df[column]!=0, column].iloc[-1]] for column in df.columns})
Loop over the columns then the rows and store the last non zero variable
list = []* number_of_columns
for i in range(len(df)):
dfcolumn = df[:,i]
for item in dfcolumn:
if item != 0:
list[i] = [i, item]
print(list)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With