Finding a match in two files and merging the data

user_17287602 0
Viewed 122

Input: file1

0        1             
EXE    sldk  
EXE1   vkrk
TPO    dlfk
EXE2   sdfs

Input: file2

0               1
DD=CMD     asldkjfalsdkfj
DD=EXE     mbnwjnjcxjic
DD=DMFF    pklckwkflkdf
DD=EXE2    okvwokmvfv
DD=EXE1    ksdjfokwoekc

Expected output: file1#

0       1         2    
EXE    sldk     mbnwjnjcxjic
EXE1   vkrk     ksdjfokwoekc
TPO    dlfk     NaN
EXE2   sdfs     okvwokmvfv

Result: file1#

0       1         2    
EXE    "sldk"     "mbnwjnjcxjic"
EXE    "sldk"     "ksdjfokwoekc"
EXE    "sldk"     "okvwokmvfv"
EXE1   "vkrk"     "ksdjfokwoekc"
TPO    "dlfk"     "NaN"
EXE2   "sdfs"     "okvwokmvfv"

Code

import pandas as pd
import numpy as np

with open(file1, 'r') as file1, open(file2, 'r') as file1:
    lines_1 = file1.readlines()
    lines_2 = file2.readlines()
    lines_1_column = [x.split(',') for x in lines_1]
    lines_2_column = [y.split(',') for y in lines_2]

df_file1 = pd.DataFrame(lines_1_column)
df_file2 = pd.DataFrame(lines_2_column)

extract_from_two_files = file2[0].str.extract(f'({"|".join(file1[0])})', expand=False)

merge_two_files = file1.merge(file2[[1]], how='left', left_on=0, right_on=extract_from_two_files)
merge_two_files.columns = np.arange(len(merge_two_files.columns))

merge_two_files.to_csv(file 1#, index=False, sep=',', header=None)

I wanna get the 'Expected output: file1#', but the result is just like 'Result: file1#' with this code. I'm sorry but is there any suggestions you have?

pandas extract merge file python
1 Answers

You could create a temporary variable from file2, without the DD=, and merge data found in both DataFrames in the first column (0) using the on parameter.

import pandas as pd

df1 = pd.read_csv('file1.csv', sep='\s+')
df2 = pd.read_csv('file2.csv', sep='\s+')

df_tmp = df2.replace('DD=','', regex=True)

result = pd.merge(left=df1, right=df_tmp, on=['0'], how='left')
result.columns = range(len(result.columns))
print(result)

Output from result

      0     1             2
0   EXE  sldk  mbnwjnjcxjic
1  EXE1  vkrk  ksdjfokwoekc
2   TPO  dlfk           NaN
3  EXE2  sdfs    okvwokmvfv
user_131441430
Related