I have searched but could not find anything similar. I have a space delimited TXT file and a tab-delimited CSV file like below
file1.txt(space delimited)
a b c d e f
b1 2 3 4 5 6
c7 8 9 3 2 1
d6 2 3 5 9 9
file2.csv (tab delimited)
f G h s
d6 0.2 0.7 9
b1 3 8 2
c7 2 2 7
I need to check column 1 in both files. if any column 1 value in file2 is equal to column 1 value in file1, I want to replace the value in column 3 of file 1 with the value from column 4 of file 2 and write the whole content to a new file. the headers can be anything so I will not be able to call column by name. It does not have to be in pandas and hopefully, there is a better way if the file is large
My Current code
import pandas as pd
f1 = pd.read_csv("f1.txt",delimiter =" ", header = None)
f2 = pd.read_csv("f2.csv",delimiter =" \t", header = 0)
with open("rr.csv", "w") as f:
for i in f2.iloc[:, [0]].values:
for x in f1.iloc[:, [0]].values:
if i == x:
f1.iloc[:, [2]].values = f2.iloc[:, [3]].values
f1.to_csv(f, sep = " ", index = False)
else:
f1.to_csv(f, sep = " ", index = False)
Expected result:
a b c d e f
b1 2 9 4 5 6
c7 8 2 3 2 1
d6 2 7 5 9 9
I did it in pandas but I am not getting my intended result what I got was this (I truncated because it is too long)
0 1 2 3 4 5
a b c d e f
b1 2 3 4 5 6
c7 8 9 3 2 1
d6 2 3 5 9 9
0 1 2 3 4 5
a b c d e f
b1 2 3 4 5 6
c7 8 9 3 2 1
d6 2 3 5 9 9
0 1 2 3 4 5
a b c d e f
b1 2 3 4 5 6