insert multiple columns based on column name with partial match

user_15707526 0
Viewed 125

i think there will be a simple solution here but i am missing something...

I have two data frames. An original, and a second with some processed data. The column names in the second data frame partially match columns in the first data frame. There are multiple column names with partial matches. The multiple columns from the second data frame need to be reinserted into the same location with a single, partially matched column name in the original data frame (so naturally, the size of the data frame will increase). Examples of the data and the expected output are copied below. it needs to be 'programmatic' as I have lots of these columns/instances.

Any comments are super helpful to me.

Thanks so much!

original data

df1 = {'id': ['1','2','3','4'],
        'ab': ['1', '2', '3', '4'],
        'xy': ['1', '2', '3','4'],
        'cd': ['1', '2', '3','4'],
        'ef':['1', '2', '3', '4'],
        'lm':['1', '2', '3', '4'],
        'fa':['1', '2', '3', '4'] }

df1 = pd.DataFrame(df1, columns = ['id','ab', 'xy', 'cd', 'ef', 'lm', 'fa'])

processed data

df2 = {'id': ['1','2','3','4'],
        'ab? op':  ['green', 'red', 'blue', 'None'],
        'ab? 1': ['red', 'yellow', 'None', 'None'],
        'cd': ['L', 'XL', 'M','L'],
        'efab? cba' : ['husband', 'wife', 'husband', 'None'],
        'efab? 1':['son', 'grandparent', 'son', 'None'],
        'efab? 2':['None', 'son', 'None', 'None'],
        'fab? 4':['9', '10', '5', '3'], 
        'fab? po':['England', 'Scotland', 'Wales', 'NA'] }

df2 = pd.DataFrame(df2, columns = ['id','ab? op', 'ab? 1', 'cd', 'efab? cba', 'efab? 1', 'efab? 2', 'fab? 4', 'fab? po'])

expected output

e = {'id': ['1','2','3','4'],
        'ab? op':  ['green', 'red', 'blue', 'None'],
        'ab? 1': ['red', 'yellow', 'None', 'None'],
        'xy': ['1', '2', '3','4'], 
        'cd': ['L', 'XL', 'M','L'],
        'lm':['1', '2', '3', '4'], 
        'efab? cba' : ['husband', 'wife', 'husband', 'None'],
        'efab? 1':['son', 'grandparent', 'son', 'None'],
        'efab? 2':['None', 'son', 'None', 'None'],
        'fab? 4':['9', '10', '5', '3'], 
        'fab? po':['England', 'Scotland', 'Wales', 'NA'] }

expected = pd.DataFrame(e, columns = ['id','ab? op', 'ab? 1', 'xy', 'cd', 'lm', 'efab? cba', 'efab? 1', 'efab? 2', 'fab? 4', 'fab? po'])
pandas string-matching partial rename python
2 Answers

If Python 3.8+, then

result = pd.concat([df1[col]
                    if (candidate := df2.loc[:, df2.columns.str.startswith(col)]).empty
                    else candidate
                    for col in df1],
                   axis=1)

For each column of df1, we look for candidate columns in df2 that startswith the column name in df1. If such column(s) exist, put the candidate to the result, else keep the column in df1.

to get

  id ab? op   ab? 1 xy  cd efab? cba      efab? 1 efab? 2 lm fab? 4   fab? po
0  1  green     red  1   L   husband          son    None  1      9   England
1  2    red  yellow  2  XL      wife  grandparent     son  2     10  Scotland
2  3   blue    None  3   M   husband          son    None  3      5     Wales
3  4   None    None  4   L      None         None    None  4      3        NA

if 3.8-,

cols = []
for col in df1:
    candidate = df2.loc[:, df2.columns.str.startswith(col)]
    cols.append(df1[col] if candidate.empty else candidate)

result = pd.concat(cols, axis=1)
user_93321870

Something with argsort after you combine with two diff with modified columns name

c2 = df2.columns.str[:2]
c1 = df1.columns
x = pd.concat([df2,df1[c1[~c1.isin(c2)]]],axis=1)
x = x.iloc[:,x.columns.str[:2].map(dict(zip(c1,range(len(c1))))).argsort()]
x
Out[115]: 
  id ab? op   ab? 1 xy  cd efab? cba      efab? 1 efab? 2 lm fab? 4   fab? po
0  1  green     red  1   L   husband          son    None  1      9   England
1  2    red  yellow  2  XL      wife  grandparent     son  2     10  Scotland
2  3   blue    None  3   M   husband          son    None  3      5     Wales
3  4   None    None  4   L      None         None    None  4      3        NA
user_79645270
Related