evaluate every cell and return column head if not null pandas df

user_5004461 0
Viewed 554

I have pandas.df 233 rows * 234 columns and I need to evaluate every cell and return corresponding column header if not nan, so far I wrote the following:

#First get a list of all column names (except column 0):

col_list=[]

for column in df.columns[1:]:
    col_list.append(column)

#Then I try to iterate through every cell and evaluate for Null
#Also a counter is initiated to take the next col_name from col_list
#when count reach 233

for index, row in df.iterrows():
    count = 0
    for x in row[1:]:
        count = count+1
        for col_name in col_list:
            if count >= 233: break
            elif str(x) != 'nan':
                print col_name

The code does not do exactly that, what do I need to change to get the code to break after 233 rows and go to the next col_name?

Example:

    Col_1   Col_2    Col_3
1    nan     13       nan
2    10      nan      nan
3    nan      2        5
4    nan     nan       4

output:      
1   Col_2
2   Col_1
3   Col_2
4   Col_3
5   Col_3
pandas nested-loops iterator python
3 Answers

I think you need if first column is index stack - it remove all NaNs and then get values from second level of Multiindex by reset_index and selecting or by Series constructor with Index.get_level_values:

s = df.stack().reset_index()['level_1'].rename('a')
print (s)
0    Col_2
1    Col_1
2    Col_2
3    Col_3
4    Col_3
Name: a, dtype: object

Or:

s = pd.Series(df.stack().index.get_level_values(1))
print (s)
0    Col_2
1    Col_1
2    Col_2
3    Col_3
4    Col_3
dtype: object

If need output as list:

L = df.stack().index.get_level_values(1).tolist()
print (L)
['Col_2', 'Col_1', 'Col_2', 'Col_3', 'Col_3']

Detail:

print (df.stack())
1  Col_2    13.0
2  Col_1    10.0
3  Col_2     2.0
   Col_3     5.0
4  Col_3     4.0
dtype: float64
user_29010020

I'd use jezrael's stack solution.

However, if you're interested in Numpy way, which is usually faster.

In [4889]: np.tile(df.columns, df.shape[0])[~np.isnan(df.values.ravel())]
Out[4889]: array(['Col_2', 'Col_1', 'Col_2', 'Col_3', 'Col_3'], dtype=object)

Timings

In [4913]: df.shape
Out[4913]: (100, 3)

In [4914]: %timeit np.tile(df.columns, df.shape[0])[~np.isnan(df.values.ravel())]
10000 loops, best of 3: 35.8 µs per loop

In [4915]: %timeit df.stack().index.get_level_values(1)
1000 loops, best of 3: 335 µs per loop

In [4905]: df.shape
Out[4905]: (100000, 3)

In [4907]: %timeit np.tile(df.columns, df.shape[0])[~np.isnan(df.values.ravel())]
100 loops, best of 3: 5.98 ms per loop

In [4908]: %timeit df.stack().index.get_level_values(1)
100 loops, best of 3: 11.7 ms per loop

Choose based on your need (readability, speed, maintainability etc)

user_21372550

You can use dropna : 

df.dropna(axis=1).columns

axis : {0 or ‘index’, 1 or ‘columns’}

how : {‘any’, ‘all’}

Basically you use dropna to remove the null, axis = 1 is dropping columns, and how="any" to remove is at least one in the columns is null, .columns get the remaining header.

user_63410540
Related