Merge two dataframes based on part of string Pandas

Question

I've two dfs like this:

I need the final output like this:

Search on column 'unit' from df1 and get the values (type and version)and if 'unit' data in df2 contains 'unit' data from df1, merge two dfs.

I tried several code, for example:

df['join'] = 1
df2['join'] = 1

dataFrameFull = df.merge(
    df_sql, on='join').drop('join', axis=1)

df1.drop('join', axis=1, inplace=True)

dataFrameFull['match'] = dataFrameFull.apply(
    lambda x: x.unit.find(x.unit), axis=1).ge(0)

But doesn't work.

I also tried:

variable = lambda x: process.extractOne(x, df["unit"])[2]  
df2['type'] = df2.loc[df["unit"].map(best_city).values, 'type'].values

Same result.

Answer 1

I didn't quite get what you ask for (I don't have enough reputation to comment), but if you want to merge df1 with df2, you can do:

df_merged = df1.merge(df2, how='outer', on='unit')

Where 'how' is how you want to join: 'outer', 'inner', etc.

Answer 2

You can concatenate two dataframes in python like this.

concatenated = pd.concat([df1, df2])

Answer 3

I think this should do what you want.

Just to test, I made some dataframes with some of your data.

df_1 = pd.DataFrame({"unit" :["00_some_gh", "01_quit_df", "003/kit_ni"], "type": [52,52,56], "version" : [1.2,1.2, 2.2]})

df_2 = pd.DataFrame({"unit" :["00_some_gh2", "00_some_gh3", "01_quit_df23", "01_quit_df43", "01_quit_df001", "003/kit_ni456"], "state": ["some_string","some_string","some_string","some_string","some_string","some_string"], "hardware" : ["yes", "no","yes", "no","yes", "no",]})

Iterate through the first df rows and find the rows in the second which start with the same string. Assign the type and version values.

for index, row in df_1.iterrows():
    df_2.loc[df_2['unit'].str.startswith(row['unit']), 'type'] = row['type']
    df_2.loc[df_2['unit'].str.startswith(row['unit']), 'version'] = row['version']