I have a dataframe with duplicate columns (number not known a priori) like this example:

I need to be able to aggregate the columns by summing their values (by rows) and returning NaN if at least one value, in one of the columns among the duplicates, is NaN.

I have tried this code:

import numpy as np
import pandas as pd

df = pd.DataFrame([[1,1,1,1,1], [1,np.nan,1,1,1]], columns=['a','a','a','b','b'])
df = df.groupby(axis=1, level=0).sum()

The result i get is as follows, but it does not return NaN in the second row of column 'a'.

In the documentation of pandas.DataFrame.sum, there is the skipna parameter which might suit my case. But I am using the function pandas.core.groupby.GroupBy.sum which does not have this parameter, but the min_count which does what i want but the number is not known in advance and would be different for each duplicate column.

For example, a min_count=3 solves the problem for column 'a', but obviously returns NaN on the whole of column 'b'.

The result I want to achieve is:

CodePudding user response：

One workaround might be to use apply to get the DataFrame.sum:

df.groupby(level=0, axis=1).apply(lambda x: x.sum(axis=1, skipna=False))

Output:

     a    b
0  3.0  2.0
1  NaN  2.0

CodePudding user response：

Another possible solution:

cols, ldf = df.columns.unique(), len(df)

pd.DataFrame(
    np.reshape([sum(df.loc[i, x]) for i in range(ldf) for x in cols],
               (len(cols), ldf)), 
    columns=cols)

Output:

    a    b
0  3.0  2.0
1  NaN  2.0