Python Forum
Merging multiple csv files with same X,Y,Z in each
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Merging multiple csv files with same X,Y,Z in each
#1
As the name suggests, looking for help building a script to merge multiple csv files. Each file has; X, Y, Z and Value(W). I am wanting to match the x,y,z in each csv file and paste the Value(W) in each subsequent column, so it is not a simple concatenation operation.
Reply
#2
What have you tried?
I welcome all feedback.
The only dumb question, is one that doesn't get asked.
My Github
How to post code using bbtags


Reply
#3
Could you provide an example of combining two files. I don't' understand what you mean by "match the X, Y, Z" and "paste value(W) in each subsequent column.
perfringo likes this post
Reply
#4
Data I am looking at is readings in 3D down a borehole. Various readings are taken at the same x,y,z location, but reside in different csv files. I need 1 csv file that lists the different reading types (in subsequent columns) at the same x,y,z location.

My workflow that I am up to is as follows

First I have to create headers in each file

import pandas as pd
import os

#define the directory where files are 
input_dir = '/path/'

#build list of files to amend
for filename in os.listdir(input_dir):
    if filename.endswith('.csv'):
        # Read the csv file
        filepath = os.path.join(input_dir,filename)
        data = pd.read_csv(filepath)
        
        #insert the column names "Hole", "X", "Y", "Z" & "Value" as headers
        data = pd.DataFrame(data, columns=['Hole','X','Y','Z','Value'])
        
        #Write the modified data to the same CSV file
        data.to_csv(filepath, index=False)
Then make a unique index for each record so I can match them together.

# define the output directory - wanting to create new versions rather than overwriting
output_dir = '/path/'

#Combine the values in columns a, b, c & d to make index
        data['index'] = data['a'].astype(str) + '_' + data['b'].astype(str)+ '_' + data['c'].astype(str)+ '_' + data['d'].astype(str)
        
        # write the modified data to a new csv file in teh output directory
        output_filepath = os.path.join(output_dir,filename)
        data.to_csv(output_filepath, index=False)
"What I am up to now"; Then I need to match index and merge records

The only problem is the column headers will not be unique. maybe put the filename into the header rather than "Value"?

What is the best way to match and merge?
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  python convert multiple files to multiple lists MCL169 6 1,436 Nov-25-2023, 05:31 AM
Last Post: Iqratech
  splitting file into multiple files by searching for string AlphaInc 2 816 Jul-01-2023, 10:35 PM
Last Post: Pedroski55
  unittest generates multiple files for each of my test case, how do I change to 1 file zsousa 0 918 Feb-15-2023, 05:34 PM
Last Post: zsousa
  Find duplicate files in multiple directories Pavel_47 9 2,926 Dec-27-2022, 04:47 PM
Last Post: deanhystad
  Extract parts of multiple log-files and put it in a dataframe hasiro 4 2,022 Apr-27-2022, 12:44 PM
Last Post: hasiro
  Search multiple CSV files for a string or strings cubangt 7 7,842 Feb-23-2022, 12:53 AM
Last Post: Pedroski55
  Rename part of filename in multiple files atomxkai 7 7,213 Feb-18-2022, 10:03 PM
Last Post: atomxkai
  Help needed with merging two CSV files eyadfr 5 2,866 Dec-14-2021, 07:34 PM
Last Post: paul18fr
  Process multiple pdf files Spartan314 1 1,297 Oct-27-2021, 10:46 PM
Last Post: Larz60+
  Generate Multiple sql Files With csv inputs vkomarag 13 4,110 Aug-20-2021, 07:03 PM
Last Post: vkomarag

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020