Come creare un file XML da Excel usando Python

Tempo di lettura stimato: 3 minuti

Stai lavorando a un progetto di analisi dei dati in cui hai bisogno di fornire i tuoi dati a una posizione che sia in grado di elaborare un file XML?

La capacità di ottenere i vostri dati in un formato strutturato come XML ha molti vantaggi:

(A) È possibile trasferire i dati a un servizio web per l'elaborazione.

(B) Più formati diversi dei vostri dati grezzi possono essere standardizzati, permettendo una rapida conversione ed elaborazione.

(C) I file XML possono essere letti da più programmi diversi, a patto di consegnarli nel formato corretto.

(D) Il destinatario dei dati può facilmente leggere il file XML e memorizzarlo nel suo database.

La capacità di usare questo metodo per leggere e trasferire i dati è un modo molto potente per aiutare un analista di dati ad elaborare grandi insiemi di dati.

Infatti, se state usando applicazioni basate sul cloud per analizzare le informazioni che state conservando, questo vi permetterà di consegnare rapidamente i dati.

Di quali pacchetti ho bisogno in Python?

Il primo passo è importare quanto segue:

import pandas as pd
from lxml import etree as et

Poi vogliamo leggere i dati di origine

In questo caso, stiamo leggendo un file excel

raw_data = pd.read_excel(r'Link to where your data is stored including the full file name')

Ora vogliamo iniziare a costruire la struttura XML

Il PRIMO PASSO è definire la radice

root = et.Element('root')

La radice è il genitore di tutti gli elementi di dati (tag) contenuti nel file XML ed è necessaria come parte della struttura

Il SECONDO PASSO è definire i nomi dei tag che memorizzeranno ogni riga dei dati sorgente

for row in raw_data.iterrows(): ==> This is a loop that takes runs through each record and populates for each tag.
    root_tags = et.SubElement(root, 'ExportData') #=== > Root name
# These are the tag names for each row (SECTION 1)
    Column_heading_1 = et.SubElement(root_tags, 'Name')
    Column_heading_2 = et.SubElement(root_tags, 'Area')
    Column_heading_3 = et.SubElement(root_tags, 'NoPurchases')
    Column_heading_4 = et.SubElement(root_tags, 'Active')

###These are the values that will be populated for each row above
# The values inside the [] are the raw file column headings.(SECTION 2)
    Column_heading_1.text = str(row[1]['Name'])
    Column_heading_2.text = str(row[1]['Area'])
    Column_heading_3.text = str(row[1]['No Purchases'])
    Column_heading_4.text = str(row[1]['Active'])

Il file grezzo si presenta così:

NomeAreaNessun acquistoAttivo
JohnDublino2Y
MariaGalway3N
JoeLimerick4N
JimmyKilkenny55Y
JenniferBelfast6N
SusanWaterford3Y
JakeSughero1Y
BobbyDundalk11N
SarahSligo9N
CianEnnis8Y
Dati del file grezzo che saranno importati nel file XML

Il TERZO PASSO è creare il file XML e popolarlo con i dati del file di origine

# This Section outputs the data to an xml file
# Unless you tell it otherwise it saves it to the same folder as the script.
tree = et.ElementTree(root) ==> The variable tree is to hold all the values of "root"
et.indent(tree, space="\t", level=0) ===> This just formats in a way that the XML is readable
tree.write('output.xml', encoding="utf-8") ==> The data is saved to an XML file

L'output XML dovrebbe assomigliare al seguente

<root>
	<ExportData>
		<Name>John</Name>
		<Area>Dublin</Area>
		<NoPurchases>2</NoPurchases>
		<Active>Y</Active>
	</ExportData>
	<ExportData>
		<Name>Mary</Name>
		<Area>Galway</Area>
		<NoPurchases>3</NoPurchases>
		<Active>N</Active>
	</ExportData>
	<ExportData>
		<Name>Joe</Name>
		<Area>Limerick</Area>
		<NoPurchases>4</NoPurchases>
		<Active>N</Active>
	</ExportData>
	<ExportData>
		<Name>Jimmy</Name>
		<Area>Kilkenny</Area>
		<NoPurchases>55</NoPurchases>
		<Active>Y</Active>
	</ExportData>
	<ExportData>
		<Name>Jennifer</Name>
		<Area>Belfast</Area>
		<NoPurchases>6</NoPurchases>
		<Active>N</Active>
	</ExportData>
	<ExportData>
		<Name>Susan</Name>
		<Area>Waterford</Area>
		<NoPurchases>3</NoPurchases>
		<Active>Y</Active>
	</ExportData>
	<ExportData>
		<Name>Jake</Name>
		<Area>Cork</Area>
		<NoPurchases>1</NoPurchases>
		<Active>Y</Active>
	</ExportData>
	<ExportData>
		<Name>Bobby</Name>
		<Area>Dundalk</Area>
		<NoPurchases>11</NoPurchases>
		<Active>N</Active>
	</ExportData>
	<ExportData>
		<Name>Sarah</Name>
		<Area>Sligo</Area>
		<NoPurchases>9</NoPurchases>
		<Active>N</Active>
	</ExportData>
	<ExportData>
		<Name>Cian</Name>
		<Area>Ennis</Area>
		<NoPurchases>8</NoPurchases>
		<Active>Y</Active>
	</ExportData>
</root>

Si possono aggiungere altri dati XML

  1. Aggiungere altre righe - Tutto quello che dovete fare è aggiungere al file sorgente e salvare. Quando rilancerete la logica, essa leggerà le informazioni extra.
  2. Aggiungere altre colonne - Tutto quello che devi fare è andare al secondo passo sopra, aggiungere un nome di tag alla SEZIONE 1. Separatamente dovrai aggiungere un'ulteriore colonna con dati al file di origine, e poi aggiungere quel nome di colonna anche alla SEZIONE 2

Come aggiungere formule a Excel usando Python

Tempo di lettura stimato: 3 minuti

Potresti lavorare sull'automazione di alcune esportazioni in Excel usando Python per confrontare i file o semplicemente aggiungendo formule a un file Excel prima di aprirlo.

Qui spieghiamo l'aggiunta di formule all'output di Excel usando Numpy o aggiungendo i calcoli a celle specifiche nell'output.

Aggiungere formule a celle specifiche

Prima di tutto, guardiamo il normale foglio di calcolo con alcuni calcoli, questi hanno le formule digitate. L'obiettivo finale è che il codice Python faccia questo per noi, un passo in meno.

Come si può vedere, le celle hanno le formule al loro interno, ma questo sarebbe un processo molto dispendioso in termini di tempo se si dovesse fare più volte, in più fogli di calcolo.

Per aggirare questo problema possiamo scrivere la logica Python come segue:

  1. Crea tre liste e tre dataframes come segue.
datasetA_list = np.array([1,2,3,4,5,6,7,8,9,10])

datasetB_list = np.array([9,8,65,43,3,21,3,2,1,7])

dataset_list = ('sum','average','median','standard deviation','count','correlation')


datasetA = pd.DataFrame(datasetA_list,columns=['ValueA'])
datasetB = pd.DataFrame(datasetB_list,columns=['ValueB'])
dataset_list_calcs = pd.DataFrame(dataset_list, columns=['Calcs'])

2. Quindi create un percorso dove memorizzerete i dati come segue:

path = 'output.xlsx'

3. In questo passo successivo create la cartella di lavoro e la posizione in cui verranno memorizzati i dati. Questo caricherà le intestazioni create nel passo 1 in una particolare posizione del foglio di lavoro.

workbook = pd.ExcelWriter(path, engine='openpyxl')
workbook.book = load_workbook(path)
workbook.sheets = dict((ws.title,ws) for ws in workbook.book.worksheets)

datasetA.to_excel(workbook,sheet_name="Sheet1", startrow=1,index=False, header=True,)
datasetB.to_excel(workbook,sheet_name="Sheet1", startrow=1, startcol=2,index=False, header=True)
dataset_list_calcs.to_excel(workbook,sheet_name="Sheet1", startrow=1, startcol=4,index=False, header=True)

4. Caricate le formule nelle celle accanto alle relative intestazioni. Questo dovrebbe allineare queste formule accanto all'intestazione pertinente creata nel passo 1.

###Creazione di calcoli per datasetA

sheet = workbook.sheets['Sheet1']
sheet['E2'] = 'CalcsA'
sheet['F3'] = '=SUM(A3:A12)'
sheet['F4'] = '=AVERAGE(A3:A12)'
sheet['F5'] = '=MEDIAN(A3:A12)'
sheet['F6'] = '=STDEV(A3:A12)'
sheet['F7'] = '=COUNT(A3:A12)'
sheet['F8'] = '=CORREL(A3:A12,C3:C12)'

###Creazione di calcoli per datasetB

sheet = workbook.sheets['Sheet1']
sheet['H2'] = 'CalcsB'
sheet['H3'] = '=SUM(C3:C12)'
sheet['H4'] = '=AVERAGE(C3:C12)'
sheet['H5'] = '=MEDIAN(C3:C12)'
sheet['H6'] = '=STDEV(C3:C12)'
sheet['H7'] = '=COUNT(C3:C12)'
sheet['H8'] = '=CORREL(A3:A12,C3:C12)'

Usa Numpy per creare i calcoli

a. Create i calcoli che popolerete nel foglio di calcolo, usando Numpy

a = np.sum(datasetA_list)
b = np.average(datasetA_list)
c = np.median(datasetA_list)
d = np.std(datasetA_list,ddof=1) ## Setting DDOF = 0 will give a differnt figure, this corrects to match the output.
f = np.count_nonzero(datasetA_list)
g = np.corrcoef(datasetA_list,datasetB_list)

b. Creare le intestazioni e assegnarle a particolari celle

sheet['E14'] = 'Numpy Calculations'
sheet['E15'] = 'Sum'
sheet['E16'] = 'Average'
sheet['E17'] = 'Median'
sheet['E18'] = 'Standard Deviation'
sheet['E19'] = 'Count'
sheet['E20'] = 'Correlation'

c. Assegnare le variabili del passo a a un insieme di celle

sheet['F15'] = a
sheet['F16'] = b
sheet['F17'] = c
sheet['F18'] = d
sheet['F19'] = f
sheet['F20'] = str(g)

d. Salvare la cartella di lavoro e chiuderla - Questo passo è importante, e va sempre incluso.

workbook.save()
workbook.close()

E l'output finale appare come...

Come contare il numero di righe e colonne in un file CSV

Quindi state lavorando a diversi progetti di analisi dei dati, e come parte di alcuni di essi, state portando dati da un file CSV.

Un'area che potreste voler guardare è How to Compare Column Headers in CSV to a List in Python, ma questo potrebbe essere accoppiato con le uscite di questo post.

Come parte del processo, se state manipolando questi dati, dovete assicurarvi che tutti siano stati caricati senza problemi.

Con questo in mente, cercheremo di aiutarvi con un possibile compito di automazione per garantire che:

(A) Tutte le righe e le colonne sono totalizzate al caricamento di un file CSV.

(B) Come parte del processo, se lo stesso set di dati viene esportato, il totale sull'esportazione può essere contato.

(C) Questo assicura che tutte le righe e le colonne della tabella richieste siano sempre disponibili.

CodicePython che vi aiuterà con questo

Quindi, nel codice qui sotto, ci sono diverse cose da guardare.

Diamo un'occhiata al file CSV che leggeremo:

In totale ci sono dieci righe con dati. La riga superiore non è inclusa nel conteggio perché è considerata una riga di intestazione. Ci sono anche sette colonne.

Questo primo bit legge solo i dati, e salta automaticamente la riga di intestazione.

import pandas as pd

df = pd.read_csv("csv_import.csv") #===> reads in all the rows, but skips the first one as it is a header.


Output with first line used:
Number of Rows: 10
Number of Columns: 7

Poi crea due variabili che contano il numero di righe e colonne e le stampa.

Notate che ha usato il df.axes per dire a python di non guardare le singole celle.

total_rows=len(df.axes[0]) #===> Axes of 0 is for a row
total_cols=len(df.axes[1]) #===> Axes of 1 is for a column
print("Number of Rows: "+str(total_rows))
print("Number of Columns: "+str(total_cols))

E mettere tutto insieme

import pandas as pd

df = pd.read_csv("csv_import.csv") #===> reads in all the rows, but skips the first one as it is a header.

total_rows=len(df.axes[0]) #===> Axes of 0 is for a row
total_cols=len(df.axes[1]) #===> Axes of 0 is for a column
print("Number of Rows: "+str(total_rows))
print("Number of Columns: "+str(total_cols))

Output:
Number of Rows: 10
Number of Columns: 7

In sintesi, questo sarebbe molto utile se state cercando di ridurre la quantità di sforzo manuale nel controllare la popolazione di un file.

Come risultato aiuterebbe a:

(A) Gli script che elaborano i dati non rimuovono righe o colonne inutilmente.

(B) Le esecuzioni in batch che conoscono la dimensione di un set di dati prima dell'elaborazione possono assicurarsi di avere i dati di cui hanno bisogno.

(C) Registri di controllo - i database possono memorizzare questi dati per mostrare che ciò che è stato elaborato è corretto.

(D) Quando un'esecuzione automatica deve essere messa in pausa, questo può aiutare a identificare il problema e a risolverlo rapidamente.

(E) Infine, se si ricevono dati concordati da una terza parte, può essere utilizzato per avvertirli che sono state ricevute troppe o troppo poche informazioni.

Ecco un altro post che dovreste leggere!

Come cambiare le intestazioni di un file CSV

come copiare/incollare speciale un intervallo di celle con xlwings

State usando Microsoft Excel insieme a Python per i vostri progetti di analisi dei dati , ma avete bisogno di automatizzare alcuni compiti?

In questo post del blog vi mostreremo come rimuovere le formule in una cella e sostituirle con i loro valori restituiti.

Questo si ottiene utilizzando xlwings, una libreria molto potente che può essere utilizzata con Python.

Quindi quello che vogliamo fare è rimuovere le formule in un foglio excel, normalmente questo si ottiene tramite "copia e incolla di valori speciali" in excel.

Qui sotto c'è uno screenshot di prima:

Per rimuovere le formule usiamo il seguente codice:

Questo codice fondamentalmente carica il file (input) e cerca l'intervallo F2:F5.

Poi, usando la funzionalità xlwings, rende i vecchi valori dell'intervallo di file uguali ai nuovi valori dell'intervallo.

La differenza è che guarda il valore restituito da vlookup alla cella e non la formula.

da openpyxl importare load_workbook
importare xlwings come xlfile

filepath_input = r'your file path here' (il percorso del tuo file)
filepath_output = r'il percorso del tuo file qui'

input_workbook = load_workbook(filepath_input)
output_workbook = load_workbook(filepath_output)

ws = input_workbook['Sheet1']

### Rimozione delle formule nel foglio di calcolo

oldlist = xlfile.Book(filepath_input)
newlist = xlfile.Book(filepath_output)

my_values = oldlist.sheets['Sheet1'].range('F2:F5').options(ndim=2).value

my_values1 = newlist.sheets['Sheet1'].range('F2:F5').options(ndim=2).value

newlist.sheets['Sheet1'].range('F2:F5').value = my_values1

L'output è un nuovo file, con le formule rimosse!

Ed ecco che ci sono altre opzioni però.

Teoricamente non devi creare un nuovo foglio come ho fatto sopra, questo è stato fatto per mostrare il prima e il dopo, altrimenti il file di input viene sovrascritto, e se questo è ciò di cui hai bisogno allora il tuo problema è risolto!

Nel distribuire questa soluzione, ci sono anche altre opzioni là fuori, ho trovato questa la più semplice da implementare.

Openpyxl può essere utilizzato ed è stato il suggerimento più comune, ma ho trovato la sua implementazione non così semplice.

Tutorial del Pitone: Come creare grafici in Excel

Tempo di lettura stimato: 2 minuti

Andando oltre con un grafico di python excel.

Abbiamo creato qui alcuni contenuti video utilizzando Python con Excel che illustra i diversi modi in cui è possibile sfruttare Python per

  • Pulizia dei dati
  • Trova i personaggi indesiderati
  • E vedere se il file è vuoto prima dell'importazione!

Cosa dà in uscita questo video

Qui stiamo cercando di introdurre i grafici in Excel, e come usare Python per lavorare facilmente con i vostri dati ed esportare in un foglio excel.

Di seguito è riportato l'output finale dei nostri due grafici, a scopo illustrativo, estratto dal sito web del governo irlandese al 1° maggio 2020, che importa gli intervalli di celle ad essi associati.

Un diagramma a barre di casse covide bullt in Python un grafico a linee di casse covide costruite in Python

Come abbiamo fatto

Nel video qui sotto sulla creazione di un grafico di python excel, abbiamo affrontato la questione come segue:

 

  • Creati quattro frame di dati separati, sono le quattro regioni che alimenteranno la creazione dei grafici.
  • A parte questo, abbiamo unito i quattro frame di dati in uno solo, da usare con il grafico a barre.

E per finire

Se ti piace ciò che questo video ti ha spiegato, clicca per vedere il nostro canale YouTube per video più informativi.

Analisi dei dati Irlanda

 

come convalidare i valori delle celle in excel

Tempo di lettura stimato: 2 minuti

Convalidare le celle in Excel rapidamente - come farlo facilmente!
Lavorate con fogli di calcolo di grandi dimensioni e cercate di fare rapidamente l'esercizio di convalida dei dati per risparmiare tempo?

L'obiettivo sarebbe quello di eseguire il vostro codice e testarlo in base ad alcune regole predefinite che voi o il vostro analista dei dati avreste scritto per assicurarvi che riporti i controlli previsti.

Se si guarda qui sotto, questo è l'output finale di questo video, evidenziando due celle che sono oltre il budget in base al budget aziendale predefinito.

esempio di validazione dei dati

La struttura di questo codice può essere suddivisa nelle seguenti fasi:

  • Leggere nel file excel, vedere un esempio precedente qui Come importare dati in excel
  •  Eseguire la prima funzione, controlla se il valore della cella del foglio di calcolo è superiore o inferiore al budget.
  •  Eseguire la seconda funzione che prende il valore della prima funzione e applica il colore rosso alla cella in caso di superamento del budget.

 

Infine

È possibile espandere questo codice per incorporare più funzionalità, come ad esempio:

  • Cambiare il colore delle celle, per avere più colori restituiti.
  •  Aggiornare le due funzioni per includere più regole di business.
  •  Si potrebbe controllare se il file è vuoto prima di elaborarlo come mostrato qui Come controllare se un file è vuoto

Si prega di abbonarsi al nostro canale YouTube, il pulsante è sul lato destro della pagina se si desidera vedere di più come questi.

Analisi dei dati Irlanda

Come importare dati in Excel

Tempo di lettura stimato: 4 minuti

Questa importazione non vi costerà nulla, tranne l'esecuzione di un po' di codice!
La necessità di avere produttivamente una soluzione all in one per gestire i vostri dati come il vostro codice è diventata più critica man mano che i volumi di dati diventano più grandi. Voi, come analisti di dati, avete quindi bisogno di inviare i vostri dati in un file excel? In precedenza avremmo pubblicato un video Come rimuovere i caratteri indesiderati, e qui ci basiamo su quel tema, collegandoci con Excel.

Due tecniche usate qui per raggiungere questo obiettivo sono XLSX writer spiegato, e Openpyxl spiegato.

Gli elementi che copriamo sono:

  • Carica i dati da un frame di dati e li popola in un file excel.
  •  Rinominare un foglio.
  • Creare un nuovo foglio e dargli un nome.
  • Guardiamo le proprietà, ovvero cambiare il colore di una scheda in giallo.
  • Potrebbe essere necessario mettere del testo in una cella del foglio, per fungere da intestazione o per mostrare di avere una cifra totale.
  • E l'ultima funzionalità trattata in questo video è come copiare i dati da un foglio all'altro.

 

Ci sono diversi vantaggi nel mettere tutto il lavoro in anticipo a Python:

  1. I vantaggi della pulizia dei dati o del formato consentono di risparmiare tempo.
  2. Dopo aver ricevuto il documento, è possibile rivederlo rapidamente senza correggere gli errori nei dati.
  3. Se si distribuisce l'output a più persone, si ottiene rapidamente ciò che vogliono, senza intervento manuale dopo che la logica è stata completata.

Ho certamente beneficiato di questa pulizia dei dati e l'importazione nell'esercizio di excel, dato che i due sono combinati ora, rende il processo più efficiente.

Ricordatevi di iscrivervi al nostro canale se vi piace il lavoro che stiamo facendo, grazie!

Analisi dei dati Irlanda

Il codice usato in questo video è qui:

########## - Previous video - How to check for invlaid characters in your dataset ###########



###########################################################################################################

#######################This video - How to import data into Excel #########################################

#In Python 3, leading zeros(at the start of a sequence of numbers) are not allowed on numbers
#Numbers need to be put between "" to be looped through

import pandas as pd
#Create a dataset locally
data = {'Number':  ["00&00$000", '111$11111','2222€2222','3333333*33','444444444£','555/55555','666666@666666'],
       'Error' : ['0','0','0','0','0','0','0']}

#Create a dataframe and tell it where to get its values from
df = pd.DataFrame (data, columns = ['Number','Error'])

#Function to loop through the dataset and see if any of the list values below are found and replace them
def run(*l):
    #This line extracts all the special characters into a new column
    #Using regular expressions it finds values that should not appear
    df['Error'] = df['Number'].str.extract('(\D+|^(0)$)')   
    #This line removes anything with a null value
    df.dropna(subset=['Error'], inplace=True)
    #This line reads in the list and assigns it a value i, to each element of the list.
    #Each i value assigned also assigns an index number to the list value.
    #The index value is then used to check whether the value associated with it is in the df['Number'] column 
    #and then replaces if found
    for i in l:
        df['Fix']= df['Number'].str.replace(i[0],"").str.replace(i[1],"").str.replace(i[2],"").str.replace(i[3],"") \
        .str.replace(i[4],"").str.replace(i[5],"").str.replace(i[6],"")
        print("Error list to check against")
        print(i[0])
        print(i[1])
        print(i[2])
        print(i[3])
        print(i[4])
        print(i[5])
        print(i[6])
    display(df)

#This is the list of errors you want to check for
l = ['$','*','€','&','£','/','@']     
        
run(l)

import xlsxwriter
#### This is the start of the code to import the data to excel

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter(r"C://dataanalyticsireland/Python Code/youtube/codeused/files/exportxlsx.xlsx")


################ XlsxWriter can only create new files. It cannot read or modify existing files.#################

# Position the dataframes in the worksheet, and names the sheet Analysis
df.to_excel(writer, sheet_name='Analysis')  # Default position, cell A1.

# Close the Pandas Excel writer and output the Excel file.
writer.save()
writer.close()

### Modify the exportxlsx.xlsx file created above through openpyxl ###


#Load xlsx file, and rename tab "Analysis" to "NewAnalysis"
from openpyxl import load_workbook
importwb = load_workbook("C://dataanalyticsireland/Python Code/youtube/codeused/files/exportxlsx.xlsx")
existing_sheet = importwb['Analysis']
#existing_sheet.title = 'NewAnalysis'

#Create new sheet on existing excel sheet, 1 signifies that it is to be created after the first sheet
#The first sheet been value 0
newsheet = importwb.create_sheet("Newsheet", 1) # insert after the first sheet as we know it already exists.



#Setting the tab colour of the newsheet to yellow
newsheet.sheet_properties.tabColor ='FFFF00'

#Putting text into a particular cell
newsheet['B3'] = "This is some text"

#Tell the code to go to the sheet we want to copy
#sourcesheet=importwb['NewAnalysis']
#Tell the code to copy the data to the newsheetcopy. It creates a new sheet and copies the data in.
#newsheetcopy=importwb.copy_worksheet(sourcesheet)



importwb.save("C://dataanalyticsireland/Python Code/youtube/codeused/files/exportxlsx.xlsx")
importwb.close()