Python Forum
DataFrame: To print a column value which is not null out of 5 columns - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: Data Science (https://python-forum.io/forum-44.html)
+--- Thread: DataFrame: To print a column value which is not null out of 5 columns (/thread-25051.html)

DataFrame: To print a column value which is not null out of 5 columns - mani - Mar-17-2020

Hello All,

I have an excel sheet with 6 columns: app_name,app_owner,bus_owner,it_lead,app_maintainer,last_updater.
Here column 2 to 6 are people names in the mentioned order.

For a given app_name, there can be names in one or more columns.

Here is a sample test data:

app_name,app_owner,bus_owner,it_lead,app_maintainer,last_updater
CRM,Tom,Hanks,Tim,Bob,Venkat
SAP,NaN,Carpenter,Velu,Karen,Lisa
eCRM,NaN,NaN,Mani,Pinky,Waren
mSales,NaN,NaN,NaN,Rosy,Lily
Mzone,NaN,NaN,NaN,NaN,Matt

I need to print the people name for app_name as input.
So, if value is present in app_owner then it should be printed, else check next column which is bus_owner for value and so on.

Expected output for the test data should be:

CRM,Tom
SAP,Carpenter
eCRM,Mani
mSales,Rosy
Mzone,Matt

I am trying to use panda's where function, but no luck.

Any help is very much appreciated.

RE: DataFrame: To print a column value which is not null out of 5 columns - snippsat - Mar-17-2020

The tricky part could be solved bye df.apply(lambda x: pd.Series(x.dropna().values),1)
Can do a test,and can use to_csv or to_excel get the output.
import pandas as pd
from io import StringIO

data = StringIO("""\
app_name,app_owner,bus_owner,it_lead,app_maintainer,last_updater
CRM,Tom,Hanks,Tim,Bob,Venkat
SAP,NaN,Carpenter,Velu,Karen,Lisa
eCRM,NaN,NaN,Mani,Pinky,Waren
mSales,NaN,NaN,NaN,Rosy,Lily
Mzone,NaN,NaN,NaN,NaN,Matt""")

df = pd.read_csv(data, sep=",")

df = df.apply(lambda x: pd.Series(x.dropna().values),1)
df
	0	1	2	3	4	5
0	CRM	Tom	Hanks	Tim	Bob	Venkat
1	SAP	Carpenter	Velu	Karen	Lisa	NaN
2	eCRM	Mani	Pinky	Waren	NaN	NaN
3	mSales	Rosy	Lily	NaN	NaN	NaN
4	Mzone	Matt	NaN	NaN	NaN	NaN

first_2 = df[[0, 1]]
first_2
0	CRM	Tom
1	SAP	Carpenter
2	eCRM	Mani
3	mSales	Rosy
4	Mzone	Matt

print(first_2.to_csv(header=None, index=False))
CRM,Tom
SAP,Carpenter
eCRM,Mani
mSales,Rosy
Mzone,Matt


RE: DataFrame: To print a column value which is not null out of 5 columns - mani - Mar-18-2020

Hello Snippsat,

Thank you very much for this solution. It worked perfect.
Appreciate your help.

Regards,