Find cells with data and use as index in dataframe
<p>I'm reading an excel file, but for this question purposes I will provide an example of what my dataframe looks like.
I have a <code>dataframe</code> like so:</p>

<pre><code>df = pd.DataFrame([
        ['Texas 1', '111', '222', '333'],
        ['Texas 1', '444', '555', '666'],
        ['Texas 2', '777','888','999']
    ])
df[2] = df[2].replace('222', '')


          0    1    2    3
a   Texas 1  111       333
b   Texas 1  444  555  666
c   Texas 2  777  888  999
</code></pre>

<p>And I want to be able to define a multiindex based on the values of the first row that are not blank.
So something like this:</p>

<pre><code>      0     1    3
Texas 1   111  333 444  555  666
Texas 2   111  333 777  888  999
</code></pre>

<p>The problem is that the values in row <code>a</code> will not always be in the same column, so I need a way to find which columns have a value in the first row and use that column number as an index. So far, I read my excel file like so:</p>

<pre><code>df1 = pd.read_excel('excel.XLS', index_col=[1,11,24,37])
</code></pre>

<p>And I've been looking for a way to read the cells that are not <code>NaN</code> and are in <code>row a</code> and find their column number to store in a list and use that as for my <code>index_col=()</code>. But I can't figure out how. Any pointers in the right direction would be awesome!  </p>

<p>first of all, you say "where is not NaN" but you <code>replace</code> with <code>''</code>.<br>
I'll replace <code>''</code> with <code>np.nan</code> then <code>dropna</code></p>

<pre><code>df.iloc[0].replace('', np.nan).dropna().index

Int64Index([0, 1, 3], dtype='int64')
</code></pre>

<hr>

<pre><code>df[df.iloc[0].replace('', np.nan).dropna().index]
</code></pre>

<p><a href="https://i.stack.imgur.com/1arU5.png" rel="nofollow"><img src="https://i.stack.imgur.com/1arU5.png" alt="enter image description here"></a></p>

python pandas