Using python to merge multiple columns with non-NaN values

Question

I have 3 dataframes with the same formats and columns and each data frame has a column criterion 1. Each data frame is filled with different persons and I want to combine the non-NaN values of this column in 3 data frames and then store it in an excel file such that NaNs are blank value in excel. How can I do this in Python? Moreover, I want to know if there is any row that is filled by at least two people or not?

DataFrame 1

ID       Criterion 1
1021     10
1022     NaN
1023     NaN
1024     NaN
1025     NaN

DataFrame 2

ID       Criterion 1
1021     NaN
1022     15
1023     NaN
1024     NaN
1025     NaN

DataFrame 3

ID       Criterion 1
1021     NaN
1022     NaN
1023     NaN
1024     NaN
1025     34

Joint DataFrame

ID       Criterion 1
1021     10
1022     15
1023     NaN
1024     NaN
1025     34

Excel File DataFrame 1

ID       Criterion 1
1021     10
1022     15
1023     
1024     
1025     34

Can you explain more Moreover, I want to know if there is any row that is filled by at least two people or not? ? — jezrael
– jezrael, Commented Mar 12, 2020 at 6:11
For example, if Criterion 1 is filled in both data frames 1 and 2. In this situation, I want to face an error. — Amin
– Amin, Commented Mar 12, 2020 at 6:12

jezrael · Accepted Answer · 2020-03-12 09:18:07Z

1

Idea is raise error, if there is 2 or more values for same ID for Criterion 1 column in custom function:

def func(dfs):

    dfs = [x.set_index('ID') for x in dfs]

    df = pd.concat([x['Criterion 1'] for x in dfs], 1)
    m = df.count(axis=1).gt(1)
    L = m.index[m].astype(str)
    if m.any():
        raise ValueError(f'For ID(s) {", ".join(L)} are multiple values')

    return df.groupby(axis=1, level=0).first()


df = func([df1, df2, df3])
print (df)
      Criterion 1
ID               
1021         10.0
1022         15.0
1023          NaN
1024          NaN
1025         34.0

Last, write this data frame into an Excel file format:

df.to_excel(file)

edited Mar 12, 2020 at 9:18

answered Mar 12, 2020 at 6:05

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Amin Over a year ago

Thank you so much. Great solution

jezrael Over a year ago

@Katatonia - ID is index? Or column?

Collectives™ on Stack Overflow

Using python to merge multiple columns with non-NaN values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related