Linked Questions

Popular Questions

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

Related Questions