Home > Enterprise >  String-join pandas dataframe colums and skip nan values
String-join pandas dataframe colums and skip nan values

Time:04-22

I'm trying to join column values into new column but I want to skip nan values:

df['col'] = 'df['col1'].map(str)   ','   df['col2'].map(str)   ','   df['col3'].map(str)'

For example if a col2 value is nan, corresponding col value become:

val1,,val3

Want it to be:

val1,val3

Sample df:

col1 col2 col3
---------------
val11 nan val13
nan val22 val23
nan   nan val33

Desired output:

col1 col2 col3   col
---------------------
val11 nan val13  val11,val13  
nan val22 val23  val22,val23
nan   nan val33  val33

CodePudding user response:

When you read the dataframe from csv file then use:

df.read_csv(path , na_filter=False)

If you already have the dataframe then you can replace nan with empty string in this way:

df = df.fillna('')

Updated:

From what I understand in your question you want to include only column values that aren't nan. You can use this statement for each column you want to join (only not nan values):

~df['col1'].isnull()

Another solution:

You can add a condition before aggregating each column value to the desired result column col on each row of dataframe:

df['col'] = ""
for index, row in df.iterrows():
    if not pd.isnull(row['col1']):
        df.at[index,'col'] = f"{row['col1']},"
    if not pd.isnull(row['col2']):
        df.at[index, 'col']  = f"{row['col2']},"
    if not pd.isnull(row['col3']):
        df.at[index, 'col']  = f"{row['col3']}"

CodePudding user response:

df.apply worked out for me, as @Oghli suggested:

def joiner():
    if x['col1']:
        x['col']  = x['col1'].map(str)   ','
    if x['col2']:
        x['col']  = x['col2'].map(str)   ','
    if x['col3']:
        x['col']  = x['col3'].map(str)   ','

    return x

df = df.apply(joiner, axis=1)
  • Related