Merging csv files into one (columnwise) in Python

volda 0
Viewed 38

I have many .csv files like this (with one column): picture

Id like to merge them into one .csv file, so that each of the column will contain one of the csv files data. The headings should be like this (when converted to spreadsheet): picture (the first number is the number of minutes extracted from the file name, the second is the first word in the file name behind "export_" in the name, and third is the whole name of the file).

Id like to work in Python. Can you please someone help me with this? I am new in Python.

Thank you very much.

I tried to join only 2 files, but I have no idea how to do it with more files without writing all down manually. Also, i dont know, how to extract headings from the file names:

import pandas as pd 

file_list = ['export_Control 37C 4h_Single Cells_Single Cells_Single Cells.csv', 'export_Control 37C 0 min_Single Cells_Single Cells_Single Cells.csv']
df = pd.DataFrame()
for file in file_list:
    temp_df = pd.read_csv(file)
    df = pd.concat([df, temp_df], axis=1)
    
print(df)


df.to_csv('output2.csv', index=False)
analysis merge csv python
1 Answers

Assuming that your .csv files they all have a header and the same number of rows, you can use the code below to put all the .csv (single-columned) one besides the other in a single Excel worksheet.

import os
import pandas as pd
              
csv_path = r'path_to_the_folder_containing_the_csvs'

csv_files = [file for file in os.listdir(csv_path)]

list_of_dfs=[]
for file in csv_files :
    temp=pd.read_csv(csv_path + '\\' + file, header=0, names=['Header'])
    time_number = pd.DataFrame([[file.split('_')[1].split()[2]]], columns=['Header'])
    file_title = pd.DataFrame([[file.split('_')[1].split()[0]]], columns=['Header'])
    file_name = pd.DataFrame([[file]], columns=['Header'])
    out = pd.concat([time_number, file_title, file_name, temp]).reset_index(drop=True)
    list_of_dfs.append(out)

final= pd.concat(list_of_dfs, axis=1, ignore_index=True)
final.columns = ['Column' + str(col+1) for col in final.columns]
final.to_csv(csv_path + '\output.csv', index=False)
final

For example, considering three .csv files, running the code above yields to :

>>> Output (in Jupyter)

enter image description here

>>> Output (in Excel)

enter image description here

abokey0
Related