Reputation: 704

combine data from different files

I have multiple files for question purpose I am showing only two files:

TXT1
id   value 
1    4
2    4
4    5
TXT2
id   value   
2    6   
3    5
5    3

desired output: first collect all the elements of id column from all 40 files and name the column header value_TXT1 (file name). if value found enter it else add 0.

    id   value_TXT1 value_TXT2   
    1    4          0
    2    4          6  
    3    0          5
    4    5          0
    5    3          0

I have ~40 files in a directory of which i need to make a final table like this so my final table will have

id   value_TXT1 value_TXT2........valueTXT40

Any pseudo code or tutorial would be helpful, apologies I have not tried anything as I am confused how to approach this.

EDIT: this is what I have tried so far from different sources:

import glob  
import os
data_dict = {}
path = '/Users/a/Desktop/combine/*.txt' 
paths = '/Users/a/Desktop/combine/' 
files=glob.glob(path)  
filelist = os.listdir(paths) #Make a file list
file_names=[os.path.splitext(x)[0] for x in filelist] #header
print file_names
for file in files:     
    f=open(file, 'r') 
    f.readline()
    for i in f:
        (key, value) = i.split()
        data_dict[key]=value

print data_dict

output: 
['combine', 'combine2']
{'1': '4', '3': '5', '2': '4', '5': '3', '4': '5'}

two files called

combine.txt
id   value   
2    6   
3    5
5    3 
combine1.txt
id   value 
1    4
2    4
4    5

Upvotes: 0

Answers (3)

J.GALT

Reputation: 26

I assume:

files are in the same folder
they all start with "TXT"
The text is tab separated

Requirement: pandas

Input:

here the code:

    import pandas as pd
    import glob

    path = "/my/full/path/"
    file_list = glob.glob1(path, "TXT*")
    res = pd.DataFrame()
    for filename in file_list:
        df = pd.read_csv(path+filename, header=None, sep="    ", index_col=0, names=["values_"+file])
        res = pd.concat([res,df], axis=1)
   res = res.fillna(0)
   print res.astype(int)

Output:

       values_TXT1  values_TXT2
    1            4            4
    2            3            4
    3            5            0
    4            3            5
    6            0            3
    7            5            0

also you can export it to csv again with: res.to_csv("export.csv", sep=",")
you can find more parameters in the documentation

Upvotes: 1

alec_djinn

Reputation: 10789

Here I propose you a solution based on the following assumption:

1) the files are all tab separated or comma separated

2) comma appears only as a separator

3) all the files you want to process are in the same folder

Here it goes:

#1 make a list fo files to precess
import glob
folder = 'path_to_my_folder'
extension = '*.txt' #it can be *.*
files = glob.glob(folder + '/' + extension)

#2 initialize a dict
data = {}

#3 read all the files and update the dict

for n, file in enumerate(files):
    with open(file, 'r') as f:
        separator = False
        for line in f:
            if line[0] == 'E': #check for ID-containing lines
                if ',' in line:
                    separator = ','
                else:
                    separator = '\t'
                id, value = line.strip().split(separator)
                try:
                    data[id].append(value)
                except KeyError:
                    data[id] = []
                    #fill with 0 the id not found on previous files
                    while len(data[id]) < n: 
                        data[id].append(0)
                    data[id].append(value)

     #fill with 0 the id not found on this file
     for k,v in data.items(): #.iteritems() on python2
         while len(v) < n+1: #if n=0 then len must be 1
             data[k].append(0)

#print the result
#first line
print('id', end='')
for file in files:
    print('\t{}'.format(file), end='')
#the rest
for k, v in data.items():
    print('\n{}'.format(k), end='')
    for item in v:
       print('\t{}'.format(item), end='')


#to write it in a file
with open('myfile.txt' , 'w') as f:
    #write header
    f.write('id')
    for file in files:
        f.write('\t{}'.format(file))
    f.write('\n') #go to the next line (optional)

    for k, v in data.items():
        f.write('\n{}'.format(k))
        for item in v:
           f.write('\t{}'.format(item))

Upvotes: 1

YLJ

Reputation: 2996

First parse all 40 files, and get a dictionary data_dict.

(pseudo code)

data_dict = {}
def parse_file(txt_i):
    for id, value in data_rows:
        if id not in data_dict:
            data_dict[id] = [0 ... 0]    # 40 zeros indicate the default values from each TXT file
        data_dict[id][i] = value     # set value of the ith TXT file

Then print out the content of data_dict in the format you want.

for id in data_dict:
    print id
    for value in data_dict[id]:
        print value

Remember to take care of headers. (id value_TXT1 value_TXT2........valueTXT40)

Upvotes: 1

combine data from different files

Answers (3)

Related Questions