Estoy concatenando todos los archivos de un directorio en uno solo; sin embargo, algunos archivos tienen un número diferente de entradas: ¿cómo coloco un NaN cuando no hay un valor para esa clave en el archivo?

Por ejemplo:

File1.cs

 NUM, NAME, ORG, DATA
 1,AAA,10,123.4
 1,AAB,20,176.5
 1,AAC,30,133.5

Archivo2. cs

 NUM, NAME, ORG, DATA
 1,AAA,10,111.4
 1,AAC,30,122.5
 2,BBA,12,156.7

Salida deseada

 NUM, NAME, ORG, File1, File2 ....
 1, AAA, 10, 123.4, 111.4
 1, AAB, 20, 176.5, NaN
 1, AAC, 30, 133.5, 122.5
 2, BBA, 12, NaN,   156.7
 .....

Esto es lo que he intentado:

import pandas as pd
import glob

writer = pd.ExcelWriter('analysis.xlsx', engine='xlsxwriter')
data = []
df1 = pd.read_csv("file1.cs", sep = ',', header = 'infer')    

for infile in glob.glob("*.cs"):
    df = pd.read_csv(infile, sep = ',', header = 'infer')
    name = infile[13:-7]
    df['filename'] = name
    data.append(df)
result = pd.merge(df1, data.to_frame(), on= 'NAME')
result.to_excel(writer, sheet_name=sheetname)
writer.save()

También probé pd.concat(data, axis=1, ignore_index=False) pero esto no agrega el NaN porque simplemente concatena los archivos según el nombre de la columna.

5
EA00 22 feb. 2018 a las 20:33

3 respuestas

La mejor respuesta

Utilice merge con el parámetro how igual a 'exterior':

df1.merge(df2, on=['NUM','NAME','ORG'], how='outer')

Salida:

   NUM NAME  ORG  DATA_x  DATA_y
0    1  AAA   10   123.4   111.4
1    1  AAB   20   176.5     NaN
2    1  AAC   30   133.5   122.5
3    2  BBA   12     NaN   156.7

Para obtener su salida exacta use:

df1.rename(columns={'DATA':'FILE'})\
   .merge(df2.rename(columns={'DATA':'FILE'}), 
         on=['NUM','NAME','ORG'],
         how='outer', 
         suffixes=('1','2'))

Salida:

   NUM NAME  ORG  FILE1  FILE2
0    1  AAA   10  123.4  111.4
1    1  AAB   20  176.5    NaN
2    1  AAC   30  133.5  122.5
3    2  BBA   12    NaN  156.7
3
Scott Boston 22 feb. 2018 a las 17:53

Intente algo similar a esto:

df1 = pd.read_csv("file1.cs", sep = ',', header = 'infer')
df2 = pd.read_csv("file2.cs", sep = ',', header = 'infer')
merged_df = df1.merge(df2, on=['NUM','NAME','ORG'], how='outer')

Puedes leer df2 en tu bucle como quieras, pero esta sería la sintaxis general para obtener lo que quieres.

1
sacuL 22 feb. 2018 a las 17:46

Esta es una forma. Use el argumento suffixes para nombrar sus columnas.

pd.merge(df1, df2, how='outer', on=['NUM', 'NAME', 'ORG'], suffixes=('_FILE1', '_FILE2'))

#    NUM NAME  ORG  DATA_FILE1  DATA_FILE2
# 0    1  AAA   10       123.4       111.4
# 1    1  AAB   20       176.5         NaN
# 2    1  AAC   30       133.5       122.5
# 3    2  BBA   12         NaN       156.7
1
jpp 22 feb. 2018 a las 17:48