847863_es-ES

liliconcom

Apilar tablas una al lado de la otra desde Excel y agregar columnas de encabezado

He estado luchando durante 2 días para extraer datos de Excel a JMP en el formato correcto.

Tengo cientos de hojas de Excel, pero los datos están desorganizados. Hay cientos de tablas juntas en una hoja de Excel.

Quiero apilar los datos en las diferentes tablas, pero también crear nuevas columnas con los datos de encabezado asociados. Solo he podido lograrlo en una tabla, pero no puedo replicarlo en todas las tablas ni en las hojas de cálculo de diferentes documentos de Excel. Las hojas suelen tener nombres de pestaña diferentes, pero si pudiera hacerlo funcionar en un solo documento de Excel, sería genial.

Organización del contenidoVentanasRe: Apilar tablas una al lado de la otra desde Excel y agregar columnas de encabezado

¿Podrías proporcionar un ejemplo de archivo de Excel? Si tienes varios tipos de archivos, tener más ejemplos facilitará la búsqueda de respuestas.

Re: Apilar tablas una al lado de la otra desde Excel y agregar columnas de encabezado

Hola Jarmo,

Claro, he adjuntado datos ficticios. Puede haber hasta 200 tablas en paralelo, y he añadido diferentes hojas de cálculo dentro del mismo archivo de Excel solo para mostrar el cambio de nombre de las pestañas, pero en realidad estas hojas suelen estar en hojas de Excel separadas dentro de una carpeta. Creo que los resaltados en amarillo indican dónde se necesitarían nuevos encabezados, y el resaltado en naranja simplemente se debe a que en el conjunto de datos real hay varios cientos de filas vacías antes de que comiencen los datos.

Idealmente, me gustaría traer todos los parámetros (parámetros 1 a 19, hay más cuando te desplazas hacia abajo en Excel) bajo un encabezado de parámetros y los valores bajo un encabezado de valor, y agregar columnas para mostrar que estos datos pertenecen a los valores del encabezado 1/2/3 (y nuevos 4) agregando columnas como en la publicación original.

Finalmente, también intentaré guardar de manera similar los dos conjuntos de datos (columnas de encabezado VCL y VL) con algún identificador del encabezado 1/2/3 (y 4).

Déjame saber si tienes alguna idea. ¡Te lo agradecería mucho!

Gracias

Ametralladora

Re: Apilar tablas una al lado de la otra desde Excel y agregar columnas de encabezado

No será fácil, ya que, siendo sinceros, estos datos tienen un formato horrible. Ir a la fuente y corregirlos sería la mejor opción, pero rara vez es una opción.

Yo empezaría extrayendo los datos a JMP lo más crudos posible, algo como esto

A partir de esto, empezaría a crear un script, ya que estos datos presentan demasiados problemas para gestionarlos sin usar JSL (probablemente se pueda llegar bastante lejos sin JSL, pero con JSL será mucho más fácil). Corregir la primera línea de encabezados es probablemente lo primero que debería hacer, ya que así podrá separar más fácilmente las tablas y sus datos.

Re: Apilar tablas una al lado de la otra desde Excel y agregar columnas de encabezado

Modifiqué el archivo de Excel que me proporcionaste para simplificarlo un poco, pero la idea sería la misma si fuera más compleja. Solo manejaré una hoja de cálculo, pero es fácil recorrer las hojas.

Datos iniciales

Importar a JMP (por alguna razón, JMP quiere eliminar la primera columna vacía aunque no le he dicho que lo haga, pero está bien)

Nuestros datos de partida

En este punto, optaré inmediatamente por JSL, pero se podría realizar una limpieza fácilmente con las funciones interactivas de JMP. Tenga en cuenta que esta solución no aborda diferentes tipos de problemas en los datos (encabezados faltantes, parámetros duplicados), ya que no sé qué hacer con ellos.

Ver más...

Names Default To Here(1);

dt = Open(
	"$DOWNLOADS/Book1.xlsx",
	Worksheets("Sheet1"),
	Use for all sheets(0),
	Concatenate Worksheets(0),
	Create Concatenation Column(0),
	Worksheet Settings(
		1,
		Has Column Headers(0),
		Number of Rows in Headers(1),
		Headers Start on Row(1),
		Data Starts on Row(1),
		Data Starts on Column(1),
		Data Ends on Row(0),
		Data Ends on Column(0),
		Replicated Spanned Rows(0),
		Replicated Spanned Headers(0),
		Suppress Hidden Rows(0),
		Suppress Hidden Columns(0),
		Suppress Empty Columns(0),
		Treat as Hierarchy(0),
		Multiple Series Stack(0),
		Import Cell Colors(0),
		Limit Column Detect(1),
		Column Separator String("-"),
		Column Numeric Format(Index(0), Column Name(Column 1), Datatype("Character"))
	)
);


// turn everything after this into functions/expressions which you can easily run on different sheets

// Take only useful data: header which belongs to parameters (first two rows)
// Parameter rows
header_rows = [1, 2];
param_rows = dt << Get Rows Where(Starts With(:Column 1, "Parameter"));
dt_headers = dt << subset(Rows(header_rows), Selected Columns(0), Output table("Data Header"));
dt_params = dt << subset(Rows(param_rows), Selected Columns(0), Output table("Data Parametere"));
Close(dt, no save);

// Drop empty columns
For Each({colname}, dt_params << Get Column Names("String"),
	If(Col Number(Column(dt_params, colname)) == 0,
		dt_params << Delete Column(colname)
	);
);

dt_result = dt_params << Stack(
	columns(dt_params << Get Column Names("String")),
	Stack By Row(0),
	Drop All Other Columns(1),
	Number of Series(2),
	Output Table("Result")
);
// Close(dt_params, no save);

// Cleanup some columns
dt_result << Delete Columns(1, 3);
Column(dt_result, 1) << Set Name("Name");
Column(dt_result, 2) << Set Name("Value");

// Add grouping for header joining
new_col = dt_result << New Column("Group", Numeric, Ordinal, Formula(
	Col Cumulative Sum(1, :Name)
));
dt_result << run formulas;
new_col << Delete Formula;


headers = dt_headers[1, 0];
header_count = N Items(Associative Array(headers));
dt_headerstack = Eval(EvalExpr(dt_headers << Stack(
	columns(dt_headers << Get Column Names("String")),
	Stack By Row(0),
	Number of Series(Expr(header_count)),
	Output Table("header stack")
)));
// Close(dt_headers, no save);
// drop unnecessary columns
dt_headerstack << Delete Columns(1::N Cols(dt_headerstack)::2);

// Move header columns and remove extra header rows
dt_headerstack << Move Up;
rows_to_delete = dt_headerstack << Get Rows Where(Starts With(Column(dt_headerstack, 1)[], "Header"));
dt_headerstack << Delete Rows(rows_to_delete);

// Add grouping for update
new_col = dt_headerstack << New Column("Group", Numeric, Ordinal, Formula(
	Row()
));
dt_headerstack << run formulas;
new_col << Delete Formula;

dt_result << Update(
	With(dt_headerstack),
	Match Columns(:Group = :Group)
);
Close(dt_headerstack, no save);
Close(dt_headers, no save); // close earlier
Close(dt_params, no save); // close earlier
Close(dt, no save); // close earlier



/*
sheetList = Get Excel Worksheets("$DOWNLOADS/Book1.xlsx"); // for sheet list to loop over
*/

Archivo de Excel y archivo de resultados JMP adjuntos

Re: Apilar tablas una al lado de la otra desde Excel y agregar columnas de encabezado

¡Excelente, gracias! Funciona muy bien para el archivo de ejemplo. Voy a empezar a limpiarlo un poco y ver qué tal me va, pero parece una buena manera de avanzar. ¡Gracias!