将多个.txt文件合并到一个csv中 [英] Merging multiple .txt files into a csv

问题描述

* Python的新功能.

*New to Python.

我正在尝试将多个文本文件合并到1个csv中；下面的示例-

I'm trying to merge multiple text files into 1 csv; example below -

filename.csv

filename.csv

Alpha

0
0.1
0.15
0.2
0.25
0.3

text1.txt

text1.txt

Alpha,Beta
0,10
0.2,20
0.3,30

text2.txt

text2.txt

Alpha,Charlie
0.1,5
0.15,15

text3.txt

text3.txt

Alpha,Delta
0.1,10
0.15,20
0.2,50
0.3,10

csv文件中所需的输出:-

Desired output in the csv file: -

filename.csv

filename.csv

Alpha  Beta  Charlie  Delta
  0     10     0        0
  0.1    0     5        10
  0.15   0     15       20
  0.2   20     0        50
  0.25   0     0        0
  0.3   30     0        10

我一直在使用的代码以及其他提供的代码给我一个类似于页面底部的答案

The code I've been working with and others that were provided give me an answer similar to what is at the bottom of the page

def mergeData(indir="Dir Path", outdir="Dir Path"):
    dfs = []
    os.chdir(indir)
    fileList=glob.glob("*.txt")
    for filename in fileList:
        left= "/Path/Final.csv"
        right = filename
        output = "/Path/finalMerged.csv"
        leftDf = pandas.read_csv(left)
        rightDf = pandas.read_csv(right)
        mergedDf = pandas.merge(leftDf,rightDf,how='inner',on="Alpha", sort=True)
        dfs.append(mergedDf)
    outputDf = pandas.concat(dfs, ignore_index=True)
    outputDf = pandas.merge(leftDf, outputDf, how='inner', on='Alpha', sort=True, copy=False).fillna(0)
    print (outputDf)

    outputDf.to_csv(output, index=0)

mergeData()

但是我得到的答案不是期望的结果:-

The answer I get however is instead of the desired result: -

Alpha  Beta  Charlie  Delta
  0     10     0        0
  0.1    0     5        0
  0.1    0     0        10
  0.15   0     15       0
  0.15   0     0        20
  0.2   20     0        0
  0.2    0     0        50
  0.25   0     0        0
  0.3   30     0        0
  0.3    0     0        10

推荐答案

IIUC，您可以创建所有DataFrames-dfs的列表，循环添加mergedDf和最后一个

IIUC you can create list of all DataFrames - dfs, in loop append mergedDf and last concat all DataFrames to one:

import pandas
import glob
import os

def mergeData(indir="dir/path", outdir="dir/path"):
    dfs = []
    os.chdir(indir)
    fileList=glob.glob("*.txt")
    for filename in fileList:
        left= "/path/filename.csv"
        right = filename
        output = "/path/filename.csv"
        leftDf = pandas.read_csv(left)
        rightDf = pandas.read_csv(right)
        mergedDf = pandas.merge(leftDf,rightDf,how='right',on="Alpha", sort=True)
        dfs.append(mergedDf)
    outputDf = pandas.concat(dfs, ignore_index=True)
    #add missing rows from leftDf (in sample Alpha - 0.25) 
    #fill NaN values by 0
    outputDf = pandas.merge(leftDf,outputDf,how='left',on="Alpha", sort=True).fillna(0)
    #columns are converted to int
    outputDf[['Beta', 'Charlie']] = outputDf[['Beta', 'Charlie']].astype(int) 
    print (outputDf)

    outputDf.to_csv(output, index=0)

mergeData()

   Alpha  Beta  Charlie
0   0.00    10        0
1   0.10     0        5
2   0.15     0       15
3   0.20    20        0
4   0.25     0        0
5   0.30    30        0

问题是您将第二个merge中的参数how='left'更改为how='inner':

Problem is you change parameter how='left' in second merge to how='inner':

def mergeData(indir="Dir Path", outdir="Dir Path"):
    dfs = []
    os.chdir(indir)
    fileList=glob.glob("*.txt")
    for filename in fileList:
        left= "/Path/Final.csv"
        right = filename
        output = "/Path/finalMerged.csv"
        leftDf = pandas.read_csv(left)
        rightDf = pandas.read_csv(right)
        mergedDf = pandas.merge(leftDf,rightDf,how='inner',on="Alpha", sort=True)
        dfs.append(mergedDf)
    outputDf = pandas.concat(dfs, ignore_index=True)
    #need left join, not inner
    outputDf = pandas.merge(leftDf, outputDf, how='left', on='Alpha', sort=True, copy=False)
                     .fillna(0)
    print (outputDf)

    outputDf.to_csv(output, index=0)

mergeData()

   Alpha  Beta  Charlie  Delta
0   0.00  10.0      0.0    0.0
1   0.10   0.0      5.0    0.0
2   0.10   0.0      0.0   10.0
3   0.15   0.0     15.0    0.0
4   0.15   0.0      0.0   20.0
5   0.20  20.0      0.0    0.0
6   0.20   0.0      0.0   50.0
7   0.25   0.0      0.0    0.0
8   0.30  30.0      0.0    0.0
9   0.30   0.0      0.0   10.0

这篇关于将多个.txt文件合并到一个csv中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！