Search code examples
pandastextconcatenationcontains

Concatenate all columns values into 1 column when columns contain certain text

I want to create a new column called "part_1_total" that pastes together all the values of the columns that contain the string 'Part 1' (same should be done for the next set of columns that contain 'Part 2' , Part 3' etc...)

Is there a quick way to do this?

My attempts:

# Attempt 1 yields 0 as it is to sum up numbers 
def calc_total(df,string='Part 1'):
    return df.loc[:,[x for x in df.columns if string in x]].sum(axis=1)

# Attempt number 2 pastes the column names into all the cells
asos['part_1_total'] = ''.join(asos.loc[:,[x for x in asos.columns if 'Part 1' in x]])

enter image description here

Solution

  • I think this is just str concatenation for a subset of columns.

    import pandas as pd
import numpy as np

df = pd.DataFrame(
    {'Part 1 - Body':[np.nan, '100% Other Material'],
     'Part 2 - Back':['43% Nickle', '20% Aluminum'],
     'Part 1 - Lining':['93% Cotton', '23% Spandex']}
    )

df['part_1_total'] = df[[c for c in df.columns if 'Part 1' in c]].apply(
        lambda x: x.str.cat(sep=', '), axis=1)

    Resulting dataframe:

             Part 1 - Body Part 2 - Back Part 1 - Lining                      part_1_total
0                  NaN    43% Nickle      93% Cotton                        93% Cotton
1  100% Other Material  20% Aluminum     23% Spandex  100% Other Material, 23% Spandex

    You can adjust how you want to concatenate the strings (with a comma, space, etc.) by adjusting the sep parameter. See this answer for more on concatenating string columns in pandas. You could use ''.join in apply but this doesn't seem to work with NaNs.