import xlrd
import xlwt
book = xlrd.open_workbook("02_Excel_data.xlsx")
sheet_names = book.sheet_names()
print(sheet_names)
sheet = book.sheet_by_index(0)
sheet_1 = book.sheet_by_index(1)
sheet_2 = book.sheet_by_index(2)
sheet_3 = book.sheet_by_index(3)
print(sheet.name)
print(sheet_1.name)
print(sheet_2.name)
print(sheet_3.name)
book.add_sheet('Olimpiadas')
book.save("02_Excel_data.xlsx")
import openpyxl
from openpyxl.chart import BarChart, Reference
wb = openpyxl.load_workbook('data.xlsx')
hoja_olimpiadas = wb.create_sheet('olimpiadas')
data = openpyxl.chart.Reference(hoja_olimpiadas, min_col=5, min_row=2, max_col=5, max_row=7)
categories = openpyxl.chart.Reference(hoja_olimpiadas, min_col=1, min_row=2, max_row=7)
chart = BarChart()
chart.add_data(data)
chart.set_categories(categories)
chart.title = "Medallas por paises"
chart.style = 10
chart.x_axis.title = 'Paises'
chart.y_axis.title = 'Medallas'
hoja_olimpiadas.add_chart(chart, "E2")
wb.save('data.xlsx')
``
import openpyxl
wb = openpyxl.Workbook()
hoja = wb.active
# rango de celdas que queremos modificar/estilizar
rango_celdas = openpyxl.charts.Reference(hoja, min_col=1, min_row=1, max_col=3, max_row=5)
# tipo de grafico que queremos
grafico = openpyxl.charts.BarChart()
# asignamos el rango de celdas y el titulo al grafico
grafico.add_data(rango_celdas, titles_from_data=True)
# asignamos el grafico a la hoja
hoja.add_chart(grafico, "A10")
wb.save("grafico_de_barras.xlsx")
import pandas as pd
titulos = pd.read_csv("05_05_imdb_titulos.csv")
titulos.head()
len(titulos)
elenco = pd.read_csv("05_05_imdb_elenco.csv")
elenco.head()
len(elenco)
titulos.sort_values(by='year').head()
titulos[titulos.title.str.contains('Dracula')].count()
titulos.title.value_counts().head(10)
titulos[titulos.title.str.contains('Romeo and Juliet')]
titulos[titulos.title.str.contains('Exorcist')].sort_values(by='year')
titulos[titulos.year == 1950].count()
titulos[(titulos.year >= 1950) & (t
import pandas as pd
medals = pd.read_csv('medals.csv')
def medals_sum(medals):
medals_sum = medals.groupby('Country').aggregate({'Bronze': sum, 'Silver': sum, 'Gold': sum})
medals_sum.columns = ['Bronze medals', 'Silver medals', 'Gold medals']
return medals_sum
medals_sum(medals)
import pandas as pd
df_imdb_titles = pd.read_csv("05_05_imdb_titulos.csv")
df_imdb_titles.head(5)
df_imdb_cast = pd.read_csv("05_05_imdb_elenco.csv")
df_imdb_cast.head(5)
len(df_imdb_titles)
len(df_imdb_cast)
df_imdb_titles.sort_values('year').head(5)
df_imdb_titles[df_imdb_titles['title'].str.contains('Dracula')]
df_imdb_titles['title'].value_counts().head(10)
df_imdb_titles[df_imdb_titles['title'] == 'Romeo and Juliet']
df_imdb_titles[df_imdb_titles['title'].str.contains('Exorcist')].sort_values('year')
len(df_im
chart = openpyxl.chart.BarChart()
chart.title = "Medallas"
chart.y_axis.title = "Pais"
chart.x_axis.title = "Medallas"
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
chart.style = 10
hoja_olimpiadas.add_chart(chart, "E8")
df["total"] = df["Gold"] + df["Silver"] + df["Bronze"]
df
chart = openpyxl.chart.BarChart() #TODO: quitar este comentario y completar la línea
chart.title = "Medallas obtenidas por país"
chart.style = 5 #TODO: quitar este comentario y completar la línea
chart.add_data(data)
chart.set_categories(categories)
chart.shape = 4 #TODO: quitar este comentario y completar la línea
hoja_olimpiadas.add_chart(chart, "D2") #TODO: quitar este comentario y completar la línea
from openpyxl import load_workbook
wb = load_workbook('cursos_udemy.xlsx')
hoja = wb['cursos']
# Escribimos un nuevo bold para el estilo
bold = Font(bold=True)
# Ahora creamos una variable para el estilo, que será un objeto de tipo Alignment
estilo = Alignment(horizontal='center', vertical='center')
#Iteramos por la primera fila
for col in range(1, hoja.max_column + 1):
hoja.cell(1, col).font = bold
hoja.cell(1, col).alignment = estilo
wb.save('cursos_udemy.xlsx')
# import pandas library
import pandas as pd
# Read excel file (dataset)
df = pd.read_excel('02_Excel_data.xlsx')
# To list sheets in excel file
# print(df.sheet_names)
# To get a sheet from the excel file
# print(df.parse('Sheet1'))
# To add a sheet to the excel file
df.add_sheet('olimpiadas')
# To list sheets in excel file
# print(df.sheet_names)
# To save excel file with changes
df.save('02_Excel_data.xlsx')
import pandas as pd
xls = pd.ExcelFile("02_Excel_data.xlsx")
xls.sheet_names
df = pd.DataFrame({'Data': [11, 12, 13, 14]})
writer = pd.ExcelWriter('02_Excel_data.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Olimpiadas')
writer.save()
import pandas as pd
def open_excel_file(file_path):
dataframe = pd.read_excel(file_path)
return dataframe
def list_excel_sheets(dataframe):
sheets = dataframe.sheet_names
return sheets
def create_new_sheet(dataframe, sheet_name):
dataframe.create_sheet(sheet_name)
return dataframe
def save_excel_file(dataframe, file_path):
dataframe.save(file_path)
return
file_path = "./02_Excel_data.xlsx"
dataframe = open_excel_file(file_path)
sheets = list_excel_sheets(dataframe)
print(sheets)
dataframe = create_new_sheet(dataframe, "Olimpiadas")
sheets = list_excel_sheets(dataframe)
print(sheets)
save_excel_file(dataframe, file_path)
bar_chart = openpyxl.chart.BarChart()
bar_chart.add_data(data=data)
bar_chart.set_categories(categories)
bar_chart.title = "Olimpiadas"
bar_chart.x_axis.title = "Pais"
bar_chart.y_axis.title = "Medallas"
hoja_olimpiadas.add_chart(bar_chart, "E2")
Por ejemplo, si un país ha conseguido 100 medallas de oro, 50 de plata y 70 de bronce, el valor de esta columna será 220
import pandas as pd
df_titles = pd.read_csv('05_05_imdb_titulos.csv')
df_titles.head()
df_elenco = pd.read_csv('05_05_imdb_elenco.csv')
df_elenco.head()
df_titles.shape[0]
df_elenco.shape[0]
df_titles.sort_values('year').head()
df_titles[df_titles['title'].str.contains('Dracula')]
df_titles[df_titles['title'].str.contains('Dracula')].shape[0]
df_elenco['title'].value_counts().head(10)
df_titles[df_titles['title'] == 'Romeo and Juliet']
df_titles[df_titles['title'].str.contains('Exorcist')].sort_values('year')
df_titles[
import openpyxl
wb = openpyxl.load_workbook('02_Excel_data.xlsx')
print(wb.sheetnames)
ws = wb.create_sheet('olimpiadas')
print(wb.sheetnames)
wb.save('02_Excel_data_new.xlsx')
def sum_medals(df):
df["Sum_medals"] = df.sum(axis=1)
return df
chart = openpyxl.chart.BarChart()
chart.add_data(data=data)
chart.set_categories(categories)
chart.title = "Medallas"
chart.x_axis.title = "Paises"
chart.y_axis.title = "Número de medallas"
sheet.add_chart(chart, "F3")
import numpy as np
np.random.randint(0, 10, (3, 3))
np.eye(6)
np.random.random((3, 3, 3))
np.random.rand(3, 3)
np.random.random_sample((3, 3))
a = np.random.randint(0,10,(3,3))
b = np.random.randint(0,10,(3,3))
np.array_equal(a, b)
import pandas as pd
import numpy as np
import os
#Cargar como dataframe de pandas el csv 05_05_imdb_titulos.csv y mostrar sus 5 primeros registros
ds_titulos = pd.read_csv('C:\\Users\\curso\\Documents\\curso_python\\05_05_imdb_titulos.csv', encoding='latin-1')
ds_titulos.head(5)
#Cargar como dataframe de pandas el csv 05_05_imdb_elenco.csv y mostrar sus 5 primeros registros
ds_elenco = pd.read_csv('C:\\Users\\curso\\Documents\\curso_python\\05_05_imdb_elenco.csv', encoding='latin-1')
ds_elenco.head(5)
#Mostrar el número de registros del dataframe de titulos
len(ds_titulos.index) #23.130
chart = BarChart()
chart.add_data(data=data)
chart.set_categories(categories)
chart.title = 'Medallas por pais'
chart.y_axis.title = 'Medallas'
chart.x_axis.title = 'Paises'
chart.set_legend(None)
chart.style = 10
hoja_olimpiadas.add_chart(chart, 'M2')
chart = openpyxl.chart.BarChart()
chart.add_data(data)
chart.set_categories(categories)
chart.title = "Medallas olímpicas"
chart.x_axis.title = 'Paises'
chart.y_axis.title = 'Medallas'
hoja_olimpiadas.add_chart(chart, "G2")
libro.save('02_Excel_data.xlsx')
import numpy as np
import pandas as pd
df = pd.DataFrame()
df['dolares'] = np.random.randint(1, 1000, size = 100)
df['euros'] = np.random.randint(1, 1000, size = 100)
df['euros'] = (df['dolares'] * 1.14362).round(2)
df = df[['euros', 'dolares']]
import numpy as np
dolares = np.random.randint(low=1, high=1001, size=100)
dolares
# in Python 2.x
from __future__ import print_function
import xlrd
from xlrd import open_workbook, cellname
wb = open_workbook('02_Excel_data.xlsx')
print('Sheets: ', wb.sheet_names())
sheet_0 = wb.sheet_by_index(0)
print(sheet_0.name)
sheet_0 = wb.sheet_by_name('Ventas')
print(sheet_0.name)
print(sheet_0.cell(0,0).value)
print(sheet_0.nrows)
print(sheet_0.ncols)
sheet_1 = wb.sheet_by_index(1)
print(sheet_1.nrows)
print(sheet_1.ncols)
sheet_2 = wb.sheet_by_index(2)
print(sheet_2.nrows)
print(sheet_2.ncols)
def add(a, b):
return a + b
add(1, 2)
import openpyxl
import os
from os.path import join, dirname, abspath
def get_current_path():
return dirname(abspath(__file__))
# obtener una ruta absoluta para el fichero excel
current_path = get_current_path() + '/ejercicio.xlsx'
# abrir el libro de excel
wb = openpyxl.load_workbook(current_path)
#listando las hojas de excel
print(wb.get_sheet_names())
# creando una hoja que se llame olimpiadas entre ventas y otros
hoja_olimpiadas = wb.create_sheet("olimpiadas")
# vuelve a listar las hojas que hay
print(wb.get_sheet_names())
# creando una lista que contenga los siguientes datos olímpicos (nombre del país y medallas de oro, plata y bronce):
import numpy as np
dolares = np.random.randint(1, 1000+1, 100)
euros = dolares * 0.874413
euros = np.round(euros, 2)
euros
chart = openpyxl.chart.BarChart()
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
chart.style = openpyxl.chart.CHART_STYLE.colors
hoja_olimpiadas.add_chart(chart, 'E1')
import xlrd
import xlwt
from xlutils.copy import copy
rbook = xlrd.open_workbook( '02_Excel_data.xlsx' , formatting_info=True)
rbook.nsheets
rbook.sheet_names()
book = copy(rbook)
book.add_sheet('olimpiadas')
book.save('02_Excel_data_olimpiadas.xlsx')
def medals_sum(row):
row['Total'] = row['Gold_sum'] + row['Silver_sum'] + row['Bronze_sum']
return row
all_medals = all_medals.apply(medals_sum, axis=1)
import numpy as np
arr = np.arange(10, 101)
arr[::-1]
arr = np.arange(9).reshape((3, 3))
np.eye(6)
arr = np.random.randint(0, 100, (3, 3, 3))
np.random.seed(42)
a = np.random.randint(0, 100, (3, 3, 3))
b = np.random.randint(0, 100, (3, 3, 3))
a == b
def sumatorio(dataframe,columna):
return dataframe[columna].sum()
import openpyxl
# Open the workbook
wb = openpyxl.load_workbook('Olimpiadas.xlsx')
# Get the worksheet
olimpiadas = wb.get_sheet_by_name('Olimpiadas')
# Get the first row from the spreadsheet
row = olimpiadas[1]
print(row)
# Iterate over the cells in the first row
for cell in row:
print(cell)
# Get the value of a specific cell
pais = olimpiadas['A2'].value
print(pais)
# Get the value of a specific cell
medallas = olimpiadas['B2'].value + olimpiadas['C2'].value + olimpiadas['D2'].value
print(medallas)
# Get the value of a specific cell
medallas = olimpiadas.cell(row=2, column=2).value
print(medallas)
# Get the value of a specific cell
medallas = o
datos_auto = open("cars-sample35.txt")
mpg = []
cylinders = []
displacement = []
horsepower = []
weight = []
acceleration = []
model = []
origin = []
car_name = []
for line in datos_auto:
line = line.strip()
auto = line.split(',')
mpg.append(auto[0])
cylinders.append(auto[1])
displacement.append(auto[2])
horsepower.append(auto[3])
weight.append(auto[4])
acceleration.append(auto[5])
model.append(auto[6])
origin.append(auto[7])
car_name.append(auto[8])
print(mpg)
print(cylinders)
import pandas as pd
df_titulos = pd.read_csv('05_05_imdb_titulos.csv')
df_titulos.head()
df_elenco = pd.read_csv('05_05_imdb_elenco.csv')
df_elenco.head()
chart = openpyxl.chart.BarChart()
chart.add_data(data=data)
chart.set_categories(categories)
hoja_olimpiadas.add_chart(chart, "A8")
import pandas as pd
def creaHoja():
# Abre el fichero Excel
data = pd.ExcelFile('C:/Users/jlescobar/Downloads/02_Excel_data.xlsx')
# Lista las hojas que hay
print(data.sheet_names)
# Crea una nueva hoja que se llame olimpiadas entre ventas y otros
data.parse('ventas')
data.parse('otros')
data.parse('olimpiadas')
# Vuelve a listar las hojas que hay
print(data.sheet_names)
# Guarda los cambios y comprueba en el excel si se han efectuado
data.to_excel('C:/Users/jlescobar/Downloads/02_Excel_data.xlsx')
creaHoja()
import numpy as np
usd = np.random.randint(1, 1000, 100)
eur = np.array([])
for i in range(len(usd)):
eur = np.append(eur, round(usd[i] * 0.874413, 2))
print(eur)
def excel_ejercicio(fichero):
# todo
return None
excel_ejercicio('02_Excel_data.xlsx')
import pandas as pd
df = pd.read_excel('02_Excel_data.xlsx')
print(df)
chart = openpyxl.chart.BarChart()
chart.add_data(data)
chart.set_categories(categories)
df['total'] = df['gold'] + df['silver'] + df['bronze']
from openpyxl import *
wb = Workbook()
ws = wb.active
ws['A1'] = "Pais"
ws['B1'] = "Oros"
ws['C1'] = "Platas"
ws['D1'] = "Bronces"
medals = [
["USA", 46, 12, 5],
["China", 38, 20, 7],
["UK", 29, 7, 7],
["Russia", 22, 10, 9],
["South Korea", 13, 3, 2],
["Germany", 11, 7, 4]
]
for i in range(len(medals)):
for j in range(len(medals[i])):
ws.cell(row=i+2, column=j+1, value=medals[i][j])
wb.save("olimpiadas.xlsx")
import requests
response = requests.get("http://api.open-notify.org/astros.json")
data = response.json()
print("El tipo de dato de la respuesta es: "+str(type(data)))
print("\n")
print("La respuesta es: "+str(data))
print("\n")
print("El número de personas que se encuentran actualmente en el espacio es: "+str(data["number"]))
print("\n")
print("Los nombres de las personas y la nave en la que se encuentran son: ")
for i in data["people"]:
print("Nombre: "+ i["name"]+", Nave: "+i["craft"])
import openpyxl
data = openpyxl.chart.Reference(hoja_olimpiadas, min_col=5, min_row=2, max_col=5, max_row=7)
categories = openpyxl.chart.Reference(hoja_olimpiadas, min_col=1, min_row=2, max_row=7)
df = pd.DataFrame([['USA', 46, 12, 5], ['China', 38, 20, 7], ['UK', 29, 7, 7], ['Russia', 22, 10, 9], ['South Korea', 13, 3, 2], ['Germany', 11, 7, 4]], columns=['Pais', 'Oros', 'Platas', 'Bronces'])
df
import openpyxl
# Abrir el fichero Excel
excel_file = openpyxl.load_workbook('02_Excel_data.xlsx')
# mirar que hojas existen
print(excel_file.sheetnames)
# crear una hoja nueva
excel_file.create_sheet('Olimpiadas')
# Mirar que hojas hay ahora
print(excel_file.sheetnames)
# guardar el fichero
excel_file.save('02_Excel_data.xlsx')
import pandas as pd
#Obtener los datos de un excel
#Abrir el fichero
#df = pd.read_excel('/Users/albertomartinez/Desktop/02_Excel_data.xlsx')
df = pd.read_excel('02_Excel_data.xlsx')
#Listar las hojas que hay
print(df.sheet_names)
#Crea una nueva hoja que se llame olimpiadas entre ventas y otros
writer = pd.ExcelWriter('02_Excel_data.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Olimpiadas')
workbook = writer.book
worksheet = writer.sheets['Olimpiadas']
writer.save()
#Vuelve a listar las hojas que hay
print(df.sheet_names)
def totalMedallas(row):
return row['Medals_gold'] + row['Medals_silver'] + row['Medals_bronze']
df.apply(totalMedallas, axis=1)
bar_chart = openpyxl.chart.BarChart()
bar_chart.add_data(data)
bar_chart.set_categories(categories)
bar_chart.title = "Medallas obtenidas en los juegos olimpicos"
bar_chart.x_axis.title = "Pais"
bar_chart.y_axis.title = "Medallas"
hoja_olimpiadas.add_chart(bar_chart, "G2")
def add(a, b):
return a + b
add(1, 2)
from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference
wb = load_workbook("02_Excel_data.xlsx")
# hoja_olimpiadas = wb["olimpiadas"]
hoja_olimpiadas = wb.create_sheet("olimpiadas")
hoja_olimpiadas.sheet_properties.tabColor = '1072BA'
wb.save("02_Excel_data.xlsx")
wb.create_sheet("olimpiadas")
wb.save("02_Excel_data.xlsx")
"""
#lista_paises = ["USA", "China", "UK", "Russia", "South Korea", "Germany"]
lista_paises = [
["USA", 46, 12, 5],
["China", 38, 20, 7],
["UK", 29, 7, 7],
["Russia", 22, 10, 9],
["South Korea", 13, 3, 2],
def function_name(parameters):
return # some value
# calling the function
chart = openpyxl.chart.BarChart()
chart.type = "col"
chart.style = 10
chart.title = "Medallas Olimpicas"
chart.y_axis.title = "Medallas"
chart.x_axis.title = "Paises"
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
hoja_olimpiadas.add_chart(chart, "A10")
chart = BarChart()
chart.add_data(data)
chart.set_categories(categories)
hoja_olimpiadas.add_chart(chart, "I2")
chart = openpyxl.chart.BarChart()
chart.type = "col"
chart.style = 10
chart.title = "Medallas por País"
chart.y_axis.title = "Medallas"
chart.x_axis.title = "País"
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
chart.shape = 4
hoja_olimpiadas.add_chart(chart, "A10")
import pandas as pd
winter = pd.read_csv('data/winter.csv')
import pandas as pd
# read excel file
df = pd.read_excel('02_Excel_data.xlsx', sheet_name=None)
# print sheet names
print(df.keys())
# create new sheet
df.update({'Olimpiadas': df['Ventas']})
# create chart
chart = openpyxl.chart.BarChart()
chart.add_data(data)
chart.set_categories(categories)
chart.title = "Medallas Olímpicas"
chart.y_axis.title = "Número de Medallas"
# insert chart into sheet
sheet.add_chart(chart, "I1")
data = openpyxl.chart.Reference(hoja_olimpiadas, min_col=5, min_row=2, max_col=5, max_row=7)
categories = openpyxl.chart.Reference(hoja_olimpiadas, min_col=1, min_row=2, max_row=7)
from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference
wb = load_workbook("02_Excel_data.xlsx")
ws = wb["Olimpiadas"]
wb.create_sheet(title="Olimpiadas2", index=1)
wb.remove(wb["Otro"])
for sheet in wb:
print(sheet.title)
lista = [["USA", 46, 12, 5],
["China", 38, 20, 7],
["UK", 29, 7, 7],
["Russia", 22, 10, 9],
["South Korea", 13, 3, 2],
["Germany", 11, 7, 4]]
for i in lista:
ws.append(i)
ws.insert_rows(1)
for row in ws.iter_rows(min_row=2, max_row=7, min_col=1, max_col=4):
for cell in row:
print(cell.value)
from openpyxl import Workbook
from openpyxl.styles import Font
wb = Workbook()
ws = wb.active
ws.title = "uno"
ws['A1'] = 'hello world'
ws['A1'].font = Font(bold=True)
wb.save('hello world.xlsx')
#python
def add(a, b):
return a + b
add(1, 2)
import xlrd
book = xlrd.open_workbook("02_Excel_data.xlsx")
print("The number of worksheets is {0}".format(book.nsheets))
print("Worksheet name(s): {0}".format(book.sheet_names()))
sh = book.sheet_by_index(0)
print("{0} {1} {2}".format(sh.name, sh.nrows, sh.ncols))
print("Cell D30 is {0}".format(sh.cell_value(rowx=29, colx=3)))
for rx in range(sh.nrows):
print(sh.row(rx))
chart = openpyxl.chart.BarChart()
chart.type = "col"
chart.style = 10
chart.shape = 4
chart.title = "Medallas obtenidas"
chart.y_axis.title = 'Medallas'
chart.x_axis.title = 'Países'
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
chart.legend = None
hoja_olimpiadas.add_chart(chart, "A9")
df['Total_Medals'] = df.Bronze_Total + df.Gold_Total + df.Silver_Total
df.head()
import openpyxl as xl
from openpyxl.utils import get_column_letter
#load the workbook
workbook = xl.load_workbook("Olimpíadas.xlsx")
#get the sheet reference
sheet = workbook["Hoja1"]
#Cargar la lista de datos
datos = [["USA",46,12,5],["China",38,20,7],["UK",29,7,7],["Russia",22,10,9],["South Korea",13,3,2],["Germany",11,7,4]]
#Move data one row down
sheet.insert_rows(3)
#Write the headers
sheet.cell(1,1).value = "País"
sheet.cell(1,2).value = "Oros"
sheet.cell(1,3).value = "Platas"
sheet.cell(1,4).value = "Bronces"
#Write the values
i = 0
while i < len(datos):
j = 0
import numpy as np
s = np.arange(10, 101)
s = s[::-1]
s = np.arange(9).reshape(3,3)
s = np.eye(6)
s = np.random.rand(3,3,3)
a = np.random.rand(3,3)
b = np.random.rand(3,3)
np.array_equal(a,b)
def add(a, b):
return a + b
add(1, 2)
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
for i in range(1, 101):
sheet.append([i])
chart = openpyxl.chart.BarChart()
chart.add_data(data)
chart.set_categories(categories)
sheet.add_chart(chart, 'A1')
wb.save('bar_chart.xlsx')
import openpyxl
from openpyxl import load_workbook
file_path = "02_Excel_data.xlsx"
workbook = load_workbook(file_path)
print(workbook.sheetnames)
sheet_3 = workbook['Sheet3']
sheet_3['A1'] = "Hola"
workbook.create_sheet(title="Olimpiadas", index=1)
print(workbook.sheetnames)
workbook.save("02_Excel_data.xlsx")
lista = [["USA", 46, 12, 5], ["China", 38, 20, 7], ["UK", 29, 7, 7], ["Russia", 22, 10, 9], ["South Korea", 13, 3, 2], ["Germany", 11, 7, 4]]
hoja_olimpiadas = workbook['Olimpiadas']
for i in range(0, len(lista)):
hoja_olimpiadas.append(lista[i])
print(list(
import openpyxl
wb = openpyxl.load_workbook('02_Excel_data.xlsx')
print(wb.sheetnames)
hoja_olimpiadas = wb.create_sheet(title='olimpiadas')
hoja_olimpiadas.title = "olimpiadas"
print(wb.sheetnames)
#Añadir datos a la hoja olimpiadas
paises = [('USA', 46, 12, 5), ('China', 38, 20, 7),
('UK', 29, 7, 7), ('Russia', 22, 10, 9),
('South Korea', 13, 3, 2), ('Germany', 11, 7, 4)]
for pais in paises:
hoja_olimpiadas.append(pais)
# Listar por filas y columnas el contenido
for row in hoja_olimpiadas.values:
print(row)
# Mover todo el rango de datos una fila hacia abajo
!pip install xlrd
import xlrd
# Open the workbook and select the first worksheet
wb = xlrd.open_workbook('02_Excel_data.xlsx')
sh = wb.sheet_by_index(0)
print('--------------------------')
print('Sheet:', sh.name, sh.nrows, sh.ncols)
print('--------------------------')
print('Sheets:')
for name in wb.sheet_names():
print(name)
wb.create_sheet("olimpiadas")
print('--------------------------')
print('Sheets:')
for name in wb.sheet_names():
print(name)
wb.save('02_Excel_data.xlsx')
def columna_a_lista (nombre_archivo, nombre_columna):
with open(nombre_archivo, 'r') as f:
datos_columna = []
lineas = f.readlines()
for lineas in lineas:
datos_columna.append(lineas.split(',')[nombre_columna])
return datos_columna
del(datos_columna[0])
datos_columna = list(map(float, datos_columna))
print(datos_columna)
def funcion_convierta_a_float(lista):
# convierta a float
return lista
list(map(funcion_convierta_a_float, lista))
import pandas as pd
df = pd.read_excel('02_Excel_data.xlsx')
print(df)
#Nombre de las hojas
df.sheet_names
#Hoja que queremos
df_ventas = df.parse('ventas')
#Crear un nuevo DataFrame
df_ventas.head(6)
new_data = df_ventas.head(6)
new_data
#Escribir un nuevo fichero
new_data.to_excel('nuevo_excel.xlsx')
# Ajustar el tamaño del gráfico
chart.layout = openpyxl.chart.Layout(
x=0.25,
y=0.25,
width=5,
height=2.5)
import openpyxl
wb = openpyxl.load_workbook('example.xlsx')
ws = wb.active
ws['A1'] = 'Tallest Mountains'
ws['B1'] = 'Height (m)'
from openpyxl.styles import Font
fontObj1 = Font(name='Times New Roman', bold=True)
ws['A1'].font = fontObj1
ws['B1'].font = fontObj1
for c in range(1,8):
ws.cell(row=c,column=2).value = c
wb.save('styled.xlsx')
chart = openpyxl.chart.BarChart()
chart.add_data(data)
chart.set_categories(categories)
hoja_olimpiadas.add_chart(chart, "A10")
import numpy as np
dolares = np.random.randint(1,1001,100)
euros = np.zeros(100)
for i in range(len(dolares)):
euros[i]=dolares[i]*0.874413
print(euros.round(2))
from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference
wb = load_workbook(filename = 'sample_chart.xlsx')
ws = wb.active
data = Reference(ws, min_row=2, min_col=2, max_row=6, max_col=6)
cats = Reference(ws, min_row=1, min_col=2, max_row=1, max_col=6)
chart = BarChart()
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(cats)
chart.title = "My Chart"
chart.y_axis.title = 'Test number'
chart.x_axis.title = 'Sample length (mm)'
# Add chart to the worksheet.
ws.add_chart(chart, 'D1')
wb.save('sample_chart.xlsx')
def sumatorio(a, b, c, d):
medallas_totales = a + b + c + d
return medallas_totales
sumatorio(10, 10, 10, 10)
# Solution 1
# Solution 2
# Solution 3
def sumatorio():
return sum(medals.loc[medals["Countries"] == country, "Medals"])
import pandas as pd
def list_sheets(path):
return pd.ExcelFile(path).sheet_names
def new_sheet(path, new_sheet, before):
writer = pd.ExcelWriter(path, engine='openpyxl')
writer.book = load_workbook(path)
writer.sheets = dict((ws.title, ws) for ws in writer.book.worksheets)
writer.book.create_sheet(new_sheet, before= before)
writer.save()
path = '02_Excel_data.xlsx'
print(list_sheets(path))
new_sheet(path, 'olimpiadas', 'ventas')
print(list_sheets(path))
import numpy as np
dollars = np.random.randint(1, 1001, 100)
euros = np.zeros(len(dollars))
euros = np.round(dollars * 1.14362, 2)
print(euros)
chart = openpyxl.chart.BarChart()
chart.type = "col"
chart.style = 10
chart.title = "Medallas por pais"
chart.y_axis.title = "Medallas"
chart.x_axis.title = "Paises"
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(categories)
chart.shape = 4
hoja_olimpiadas.add_chart(chart, "F2")
import pandas as pd
df = pd.read_excel('./data/02_Excel_data.xlsx')
df.head()
import xlrd
import xlwt
workbook = xlrd.open_workbook("02_Excel_data.xlsx")
worksheets = workbook.sheet_names()
print(worksheets)
workbook_w = xlwt.Workbook()
worksheet_w = workbook_w.add_sheet('olimpiadas')
worksheets_new = workbook_w.sheet_names()
print(worksheets_new)
workbook_w.save("02_Excel_data.xlsx")
medals.groupby('NOC').agg({'Total': 'sum'}).reset_index()
chart = BarChart()
chart.add_data(data)
chart.set_categories(categories)
chart.title = "Olimpiadas 2016"
chart.x_axis.title = "País"
chart.y_axis.title = "Total medallas"
hoja_olimpiadas.add_chart(chart, "H5")
import openpyxl
from openpyxl.chart import BarChart, Series, Reference
wb = openpyxl.Workbook()
ws = wb.active
# Datos para el gráfico
data = [
[1, 2, 3, 4, 5],
[2, 4, 6, 8, 10],
[3, 6, 9, 12, 15],
]
# inserta los datos en la hoja
for row in data:
ws.append(row)
chart = BarChart()
chart.type = "col"
chart.style = 15
chart.title = "Horizontal Bar Chart"
chart.y_axis.title = 'Test number'
chart.x_axis.title = 'Sample length (mm)'
cats = Reference(ws, min_col=1, min_row=1, max_row=3)
data = Reference(ws, min_col=2, min_row=1, max_col=5, max_row=3)
chart.add_data(data, titles
import numpy as np
dolares = np.random.randint(1,1000,100)
print(dolares)
euros = np.empty_like(dolares)
euros = dolares *0.874413
print(euros)
print(np.round(euros, 2))
import numpy as np
dollars = np.random.randint(1,1000, 100)
euros = np.zeros_like(dollars)
euros = round(dollars * 0.874413, 2)
euros
df['Total'] = df.Gold + df.Silver + df.Bronze
df.head()
def addColumna(df, a, b):
df["medallas"] = df[a] + df[b]
return df
a = 'oro'
b = 'plata'
addColumna(df, a, b)
import xlrd
def abrir_excel():
# Abrimos el fichero
libro = xlrd.open_workbook('02_Excel_data.xlsx')
# Listamos las hojas que hay en el fichero
print(libro.sheet_names())
# Creamos una nueva pestaña en la posición (antes) de "Otros"
libro.add_sheet("Olimpiadas", before="Otros")
# Listamos las hojas que hay en el fichero, para comprobar que se ha creado correctamente
print(libro.sheet_names())
# Guardamos los cambios
libro.save("02_Excel_data_edit.xlsx")
abrir_excel()
chart = openpyxl.chart.BarChart()
chart.type = "col"
chart.style = 10
chart.title = "Medallas Olímpicas"
chart.x_axis.title = 'Paises'
chart.y_axis.title = 'Medallas'
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
chart.shape = 4
hoja_olimpiadas.add_chart(chart, "A10")
df['Total'] = df['Gold'] + df['Silver'] + df['Bronze']
chart = openpyxl.chart.BarChart()
chart.add_data(data)
chart.set_categories(categories)
chart.title = "Medallas por pais"
chart.x_axis.title = "Paises"
chart.y_axis.title = "Medallas"
import numpy as np
dolar = np.random.randint(1,1001,100)
euros = np.empty(100)
euros = (dolar/1.14362)
round(euros,2)
import statistics
print("\nMuestra el número de observaciones de cada grupo")
print(len(g1), len(g2), len(g3))
print("\nMuestra el valor mínimo y el valor máximo de cada grupo")
print(min(g1), max(g1))
print(min(g2), max(g2))
print(min(g3), max(g3))
print("\nMuestra la media, mediana y desviación estándar de cada grupo")
print(statistics.mean(g1), statistics.median(g1), statistics.stdev(g1))
print(statistics.mean(g2), statistics.median(g2), statistics.stdev(g2))
print(statistics.mean(g3), statistics.median(g3), statistics.stdev(g3))
chart = openpyxl.chart.BarChart()
chart.add_data(data)
chart.set_categories(categories)
hoja_olimpiadas.add_chart(chart, "E2")
import numpy as np
dolares = np.random.randint(1, 1000, 100)
euros = np.array([])
for x in dolares:
euros = np.append(euros, round(x * 0.874413, 2))
euros
chart = openpyxl.chart.BarChart()
chart.type = "col"
chart.shape = 4
chart.style = 10
chart.title = "Medallas por país"
chart.x_axis.title = "País"
chart.y_axis.title = "Medallas"
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
chart.shape = 4
