1

I'm trying to merge multiple .txt files in a directory specifically merging on one of the common column X found in dataframes.

Import multiple csv files into pandas and concatenate into one DataFrame

Python pandas - merge csv files in directory into one

Import multiple nested csv files and concatenate into one DataFrame

Python for merging multiple files from a directory into one single file

Best strategy for merging a lot of data frames using pandas

import pandas as pd

  df1 = pd.DataFrame({'X': ['X0', 'X1', 'X2', 'X3'],
       ...:                     'B': ['B0', 'B1', 'B2', 'B3'],
       ...:                     'C': ['C0', 'C1', 'C2', 'C3'],
       ...:                     'D': ['D0', 'D1', 'D2', 'D3']})


df2 = pd.DataFrame({'X': ['X0', 'X1', 'X2', 'X3'],
   ...:                     'G': ['G0', 'G1', 'G2', 'G3'],
   ...:                     'H': ['H0', 'H1', 'H2', 'H3'],
   ...:                     'J': ['J0', 'J1', 'J2', 'J3']})

by following previous post solutions I built the following code

filepath = "D:\\test"

data=[]
for file in glob.iglob(filepath + '/*.txt', recursive=True):
    
    print(file)
    
    df=pd.read_csv(file, header=0, skiprows=0, skipfooter=0, na_values=(""," ","NA"))
    data=data.append(df)
    
data_merge = pd.concat(data, keys = ('X'))

but I got

AttributeError: 'NoneType' object has no attribute 'append'

How can I join two datafame by common column ?

the expected output

enter image description here

enter image description here

Thanks.

Community
  • 1
  • 1
Alexander
  • 4,527
  • 5
  • 51
  • 98

2 Answers2

2

Replace the line

data=data.append(df)

with

data.append(df.set_index('X'))

This will set the X column as the index and add them to the data array. Then you will need to change the merge line to

data_merge = pd.concat(data, axis=1).reset_index()

This will lead to 

print(data_merge.mark_down())

|    | X   | B   | C   | D   | G   | H   | J   |
|---:|:----|:----|:----|:----|:----|:----|:----|
|  0 | X0  | B0  | C0  | D0  | G0  | H0  | J0  |
|  1 | X1  | B1  | C1  | D1  | G1  | H1  | J1  |
|  2 | X2  | B2  | C2  | D2  | G2  | H2  | J2  |
|  3 | X3  | B3  | C3  | D3  | G3  | H3  | J3  |
divingTobi
  • 2,044
  • 10
  • 25
1

Replace this line:

data=data.append(df)

with this:

data.append(df)

Unlike using append with pandas (i.e. df = df.append(dict1)), when using the append method on a list, you don't need to redefine it

OD1995
  • 1,647
  • 4
  • 22
  • 52