Computing the first non-missing value from each column in a DataFrame [duplicate]

Question

I have a DataFrame which looks like this:

            1125400  5430095  1095751
2013-05-22   105.24      NaN  6507.58
2013-05-23   104.63      NaN  6393.86
2013-05-26   104.62      NaN  6521.54
2013-05-27   104.62      NaN  6609.31
2013-05-28   104.54    87.79  6640.24
2013-05-29   103.91    86.88  6577.39
2013-05-30   103.43    87.66  6516.55
2013-06-02   103.56    87.55  6559.43

I would like to compute the first non-NaN value in each column.

As Locate first and last non NaN values in a Pandas DataFrame points out, first_valid_index can be used. Unfortunately, it returns the first row where at least one element is not NaN and does not work per-column.

Answer 1

You should use the apply function which applies a function on either each column (default) or each row efficiently:

>>> first_valid_indices = df.apply(lambda series: series.first_valid_index())
>>> first_valid_indices
1125400   2013-05-22 00:00:00
5430095   2013-05-28 00:00:00
1095751   2013-05-22 00:00:00

first_valid_indiceswill then be a series containing the first_valid_index for each column.

You could also define the lambda function as a normal function outside:

def first_valid_index(series):
    return series.first_valid_index()

and then call apply like this:

df.apply(first_valid_index)

Answer 2

The built in function DataFrame.groupby().column.first() returns the first non null value in the column, while last() returns the last.

http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.GroupBy.first.html

If you don't wish to get the first value for each group, you can add a dummy column of 1s. Then get the first non null value using the groupby & first functions.

from Pandas import DataFrame

df = DataFrame({'a':[None,1,None],'b':[None,2,None]})
df['dummy'] = 1
df.groupby('dummy').first()
df.groupby('dummy').last()