Pfthb

I have the following dataframe:

df= pd.DataFrame('A': np.random.randn(10),
 'B': np.concatenate((np.repeat(np.nan, 4), np.array([0.7]), np.repeat(np.nan, 4), np.array([0.5]))),
 'C': np.random.randn(10),
 'D': np.concatenate((np.repeat(np.nan, 4), np.array([0.2]), np.repeat(np.nan, 4), np.array([0.15]))))

that looks something like this:

 A B C D
0 0.537109 NaN -0.204046 NaN
1 0.744488 NaN 0.903607 NaN
2 0.640995 NaN 0.712210 NaN
3 0.212758 NaN 0.293984 NaN
4 -1.786725 0.7 0.282747 0.20
5 1.486037 NaN -0.199659 NaN
6 0.267356 NaN 0.890397 NaN
7 0.697408 NaN -0.771626 NaN
8 0.044247 NaN -1.157836 NaN
9 0.345658 0.5 1.556390 0.15

I would like to calculate some statistics such as mean on slices of this dataframe, for example, I would like the result to look something like:

 MeanA B MeanC D
0 2.1 0.7 1.3 0.20
1 1.4 0.5 5.6 0.15 

It other words, I want to keep the non-NA values from columns B and D and calculate the mean and others of A and C of the values above/below the non-NA values from B and D (in this case, let's include -1.786725 in both means, similarly for 0.282747).

NOTE: The values from MeanA and MeanC are made up because of the use of np.random.randn().

Now of course, my real dataframe contains more columns and more rows, so it would be nice to generalise this.

This should do:

b_nans = df[~df.B.isnull()].index.values
cols_stats = ['A','C']
cols_nans = ['B','D']
df2 = pd.DataFrame(columns = [['mean_'+ col for col in cols] + cols_nans])

for col_stat, col_nan in zip(cols_stats, cols_nans):
 df2[col_nan] = df[~df[col_nan].isnull()][col_nan].reset_index(drop=True)
 i_prev = 0
 for k,i in enumerate(b_nans):
 df2.loc[k, 'mean_'+ col_stat] = df.loc[i_prev:i,col_stat].mean()
 i_prev = i

print(df2)

 mean_A mean_C B D
0 -0.059588 0.367446 0.7 0.20
1 0.037202 0.373243 0.5 0.15

One way of doing this is to manually work with the index.
Lets get the indices of the non-NaN rows, e.g. by

ind = np.array(df.dropna().index)

Now that you know the indices you can manually perform the operations.

You can approach the problem with below steps:

1. 
   

   Find out the index where you encounter 1st non-Nan value by following code:

   
   
   

   index = df['B'].first_valid_index() ## This will return 4

   
   


2. 
   

   Now slice df with these index value

   
   
   

   subset_df = df.iloc[0:index+1:,]

   
   


3. 
   

   take mean of the subset df for all the columns

   
   
   

   upper_half_mean = subset_df .mean(axis=0)

   
   

Do the steps 2 & 3 for the below part of the df and then just concat.

Here's a pretty simple way. The idea is to find the rows (index) where every column contains a value (the breakpoints). Iterate over those breakpoints and use those indices to get a sub slice of the dataframe. Calculate the mean for each column in that slice, which will return a single series for that slice. Then just concatenate those slices (series) together

np.random.seed(123)
# generate random data same way as in your question

# get indices of the breakpoints
idx = df[-df.isnull().any(axis=1)].index
idx = np.insert(idx, 0, 0) # idx will be [0, 4, 9]

# calculate mean across columns for each slice of the dataframe
# this creates a list of pandas series
slices = 

for i in range(len(idx)-1):
 slices.append(df[idx[i]:idx[i+1]+1].mean())

# concat the separate series together as rows
output = pd.concat(slices, axis=1).T

# this is needed to get the correct values of the nan columns
output[['B', 'D']] = df[['B', 'D']].dropna().values

Which gives:

 A B C D
0 -0.378040 0.7 -0.073018 0.20
1 -0.230593 0.5 0.817437 0.15