CONCAT and DELETE nan in each row and have the result in a other Column

Question

i just learn the basic in Panda and im looking a way to concat and delete nan value and get the result in a new column of my dataframe.

I know how to concat, how to create list but not realy how to iterate trought the column delete the nan value and finally concat the result in the new column.

i have a table with different number and i would like to create a column with panda (CONTACT[CALLER_PHONE] = ...) with all the number from each row and no null values. Exemple of result that i want in a table:

Number1     Number2      Number3      CALLER_PHONE
0675416952  0675416941   0675416930   0675416952,067541694,0675416930
Nan         0675417080   0675417082   0675417080,0675417082
Nan         Nan          0675837759   0675837759

My Code :

import pandas as pd

CONTACT = pd.read_excel('O:/16_GIS_Team/X_Tools/Model Builder And Parcels Package/Contact_20200807/CONTACT_20200807.xlsx')

pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)

CONTACT['CALLER_NAME'] = CONTACT['First Name'].str.cat(CONTACT['Last Name'], sep =" ")

cols = CONTACT[['Work Phone','Mobile','Home Phone','SMS marketing phone','Other Phone Number','Details (USA): Caller Phone']]

print(cols)

columns = list(cols)

for i in columns:
    Clean_Columns = cols.dropna(axis=1, how='any')
    print (Clean_Columns[i][2])

My files is an Excel CONTACT is my dataframe

I try to iterate trought the column, than use dropna and get a cain of result with the list but its not working and i didn't dig deeper.

Error with my list peace of code

Im open to any advise thank you very much by advance!

Hello, please add your code as text, instead of images and make sure that you provide a minimal, reproducible example, so that others can help you (https://stackoverflow.com/help/minimal-reproducible-example). — Kim Tang, Aug 20 '20 at 13:50
Skalpa, Welcome to SO. To ensure your question is answered as soon as possible, take some time to read and understand [this post](https://stackoverflow.com/help/how-to-ask) about asking good questions. The best questions include a [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example). — pnovotnyq, Aug 20 '20 at 13:57
Does this answer your question? [String concatenation of two pandas columns](https://stackoverflow.com/questions/11858472/string-concatenation-of-two-pandas-columns) — pnovotnyq, Aug 20 '20 at 15:33
@pnovotnyq my problem is i want to concat but without the nan values, i dont how to do it — Skalpa, Aug 20 '20 at 17:08

pnovotnyq · Answer 1 · 2020-08-20T15:24:53.403

0

You can define your own function that will take the numbers you select and return as a string where the numbers are delimited with ','.

# get the data
cols = CONTACT[['Work Phone','Mobile','Home Phone','SMS marketing phone','Other Phone Number','Details (USA): Caller Phone']]

def concatenate_numbers(s):
    """Remove all NA values from a series and return as a string joined by ','"""
    s = s.dropna()
    return ','.join([str(number) for number in s])

# create a new column by applying the above function to every row of the dataframe.
df['all_phones'] = df.apply(concatenate_numbers, axis=1)

pandas.Series.dropna returns a pandas.Series with dropped NA values, so you need to assign that to a variable. You can then create a new column in the dataframe from the result.

edited Aug 20 '20 at 15:24

answered Aug 20 '20 at 14:41

pnovotnyq

547
3
12

Hy, thank you for you answer, sorry if my question is not clear enought, i put an exemple in the question right now, may be you will understand better. I don – Skalpa Aug 20 '20 at 14:57
You may want to look into [this thread](https://stackoverflow.com/questions/11858472/string-concatenation-of-two-pandas-columns). – pnovotnyq Aug 20 '20 at 15:31
It may help but how can i do to don t concat the NaN value ? – Skalpa Aug 20 '20 at 17:00
my problem is i want to concat but without the nan values, i dont how to do it – Skalpa Aug 20 '20 at 17:11
Your problem might be that your "NaN values" are not actual missing values, but rather the strings "Nan". This code only gets rid of actual missing values. – pnovotnyq Aug 21 '20 at 08:44

score -1 · Answer 2 · answered Aug 20 '20 at 13:52

-1

You don't have to create a list for your columns. Just use df["column_name"].columns

df = df.dropna() or df = df[df["columns_name"] != np.nan]

answered Aug 20 '20 at 13:52

nikolaichev

21
3

`dropna()` is the correct answer. np.nan doesn't equal anything, not even np.nan. In other words, `np.nan == np.nan` evaluates to False. To test for NaN values, you can use `pd.isnull()`. – pnovotnyq Aug 20 '20 at 14:45
the think is with dropna you delete all the row no ? – Skalpa Aug 20 '20 at 14:59
Yes, where `dropna()` decides to drop, it will drop the whole row (or column). – pnovotnyq Aug 20 '20 at 15:01
the think is i want to keep the row because there is other value that i want to keep and after concat in the new column (you can look the table example that i put) – Skalpa Aug 20 '20 at 15:09

CONCAT and DELETE nan in each row and have the result in a other Column

2 Answers2