I want to make a query that returns me if for a cell of a string type column in a table, said cell has letters.
Suppose the table is named data and the column is named values .
Ex:
That should return me:
I am trying to do this query in Athena (Presto) and it does not work for me, I found that regular expressions can be applied but I do not understand them: https://prestodb.io/docs/current/functions/regexp.html
This question is more theoretical. I am studying to certify in AWS, I am reading the aws certified solutions architect study guide for the second time and it is not clear to me what is the use that can be given to an ENI, I understand that they are used to connect a VPC with other services. Could someone give me an explanation "for dummies" and some examples of use? Thank you very much.
Based on the following String:
fecha = '4 de julio de 1995 (edad 23\xa0años), Siracusa, Nueva York, Estados Unidos'
With a Split I separate the words:
split = fecha.split(',')
split[1], split[2], split[3]
Output: (' Siracusa', ' Nueva York', ' Estados Unidos')
How can I eliminate what is between parentheses in this string and only have the date.
split[0]
Output: '4 de julio de 1995 (edad 23\xa0años)'
Final Result: July 4, 1995
I have strings like the following:
€14.5M, €50M, $14.8M, $$70.5M ,100M
How can I go through those strings and get the number in them, being integers or floats without losing the . I get to this but lose the point so 13.5 becomes 135
dato = '€14.5M'
simple_dream_team.Wage.apply(lambda x: ''.join([n for n in x if n.isdigit()]))
return = 145
I could do a replace of symbols like €, $ or the M to '' but from what I'm seeing anything is coming to me, that's why I don't apply it.
I was practicing with polar and came to this, Is it possible to put an image as the background of the graph (only of the graph)? In the example that I put and by which I was guided by the link , the image is put but as the background of the entire plane.
(If you run the code, an image will be downloaded, which is the one I use in the background, if you don't want that, change where it says url_image = '' and put the address of one you have)
import matplotlib
import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
from math import pi
import seaborn as sns
import matplotlib.image as mpimg
import requests
%matplotlib inline
df = pd.DataFrame({
'Cero': ['Uno'],
'Uno': [20],
'Dos': [30],
'Tres': [40],
'Cuatro': [50],
'Cinco': [60],
'Seis': [70],
'Siete': [80],
'Ocho': [90],
'Nueve': [100]
})
url_imagen = "https://store-images.s-
microsoft.com/image/apps.6102.13510798887356280.9398b0dd-2ecf-4973-9380-
576d1d374a25.92d12017-af9a-41ae-b97a-8213710cdb49?
mode=scale&q=90&h=1080&w=1920"
nombre_local_imagen = "go.jpg"
imagen = requests.get(url_imagen).content
with open(nombre_local_imagen, 'wb') as handler:
handler.write(imagen)
def detalle(row, title):
color = '#80cbc4'
img = mpimg.imread('go.jpg')
plt.figure(figsize=(15,8))
categories=list(df)[1:]
N = len(categories)
angles = [n / float(N) * 2 * pi for n in range(N)]
angles += angles[:1]
ax = plt.subplot(111, projection='polar')
ax.set_theta_offset(pi / 2)
ax.set_theta_direction(-1)
plt.xticks(angles[:-1], categories, color= "black", size=10)
ax.set_rlabel_position(0)
plt.yticks([25,50,75,100], ["25","50","75","100"], color="grey", size=8)
plt.ylim(0,100)
values=df.loc[row].drop('Cero').values.flatten().tolist()
values += values[:1]
ax.plot(angles, values, color= "black", linewidth=1, linestyle='solid')
ax.fill(angles, values, color= color, alpha=0.9)
axes_coords = [0, 0, 1, 1]
ax_image = plt.gcf().add_axes(axes_coords)
ax_image.imshow(img, alpha=.5)
ax_image.axis('off')
plt.title(title, size=50, color= "black", y=1.1)
plt.title(title, size=50, color= "black", y=1.1)
my_palette = plt.cm.get_cmap("Set2", len(df.index))
detalle(row= row, title=df['Cero'][row])
If in the line ax_image = plt.gcf().add_axes(axes_coords) I add zorder= -99 it looks like this:
ax_image = plt.gcf().add_axes(axes_coords, zorder= -99)
I need the opposite, that the image is only visible inside the graph.
How can I filter the DF and show of all the varieties the one with the highest score for each one and other conditions?
For example with this line I can see one by one changing the variety:
dataDos[(dataDos.variedad == 'malbec') & (dataDos.puntaje==
dataDos.puntaje[dataDos.variedad == 'malbec'].max())]
This would show the most expensive Malbec in all of Mexico City:
How can I apply it to the entire DF and show me the one with the highest score by variety (they would be the ones that are painted blue)
UPDATE
I reached the desired goal by doing the following, but I'm leaving the question open because there has to be a simpler solution:
variedades = data.variedad.unique().tolist()
dfPuntajes = pd.DataFrame(columns = data.columns)
for x in variedades:
filtroUno = data[data.variedad == x]
filtroDos = filtroUno[filtroUno.puntaje == filtroUno.puntaje.max()]
filtroTres = filtroDos[filtroDos.precio_en_pesos == filtroDos.precio_en_pesos.max()]
filtroCuatro = filtroTres[filtroTres.creacion == filtroTres.creacion.min()]
dfPuntajes = dfPuntajes.append(filtroCuatro, ignore_index=True)
dfPuntajes['id'] = dfPuntajes.index
dfPuntajes.sort_values('puntaje', ascending=[0]).head(10)
I need to fix an error that appears when replacing cell values by cells within a loop in pandas:
Using the following information:
import pandas as pd
data = pd.read_csv('iEC7R76C.txt', sep=",")
data.head(2)
I want to complete the year based on the title and I get it like this:
import re
cadena = data.title[0]
cadena
# 'Nicosia 2013 VulkÃ\xa0 Bianco (Etna)'
cadena = re.sub("\D", "", cadena)
cadena
# 2013
Now if I match the year in position 0 to that string, it changes it but I get this alert that I don't know what it means and although I already google enough I don't understand it:
data.anio[0] = cadena
data.head(2)
With something more or less like this I can go through all the data and complete it but the error keeps coming up:
for i in range(0, len(data)):
cadena = data.title[i]
cadena = re.sub("\D", "", cadena)
if cadena != '':
if 1900 <= int(cadena) <= 2020:
data['anio'][i] = cadena
I put that empty condition because sometimes the title does not have the year and if that year is between 1900 and 2020 because there are titles that have for example "Name of wine 2001 batch 2" and when I get the numbers within that string I returns 20012
Enter the link that shows me the link error but I do not understand.
I've been dragging queries from this problem:
Using this code:
import random
import pandas as pd
from datetime import datetime
inicio = datetime(2019, 1, 1)
final = datetime(2019, 3, 21)
datos = []
for i in range (0, 10000):
fechaRandom = inicio + (final - inicio) * random.random()
datos.append(fechaRandom.strftime('%Y-%m-%d'))
df = pd.DataFrame(datos)
df.rename(columns={0: "Fecha"}, inplace=True)
procesos = []
for a in range (1, 11):
procesos.append('Proceso' + str(a))
total = 0
proceso = []
for i in range (0, 10):
for j in range ( 0, 1000):
proceso.append(procesos[total])
total += 1
datosProceso = pd.DataFrame(proceso)
datosProceso.rename(index=str, columns={0: "Proceso"}, inplace=True)
result = pd.merge(datosProceso.reset_index(),
df.reset_index(),
left_index=True,
right_index=True)
result = result.drop(columns={'index_x', 'index_y'})
I get the following:
A Df with 10,000 random data in two columns, processes and dates (With 10,000 random dates grouped into 10 random processes of 1000 records each)
What I need now is to be able to remove the duplicates, and leave the process plus the date and in a column the total number of times that date was repeated for that process. In summary it is this:
(result
.groupby(["Proceso", "Fecha"])
["Fecha"].count())
The problem I have is that I need everything to be in a new Dataframe that has this format:
I have the following code:
import random
import pandas as pd
from datetime import datetime
inicio = datetime(2017, 1, 30)
final = datetime(2019, 3, 21)
datos = []
for i in range (0, 10000):
datos.append(inicio + (final - inicio) * random.random())
df = pd.DataFrame(datos)
df.rename(columns={0: "Fecha"}, inplace=True)
procesos = []
for a in range (1, 11):
procesos.append('Proceso' + str(a))
total = 0
proceso = []
for i in range (0, 10):
for j in range ( 0, 1000):
proceso.append(procesos[total])
total += 1
datosProceso = pd.DataFrame(proceso)
datosProceso.rename(index=str, columns={0: "Proceso"}, inplace=True)
This creates two df's with 10,000 random data, one has 10,000 dates and the other has 10,000 process data separated into 10 random processes i.e. 1000 process data1, 1000 process data2 etc.
Now how could I join the two dataframes into one that has two columns, processes and dates, I tried adding ids, with concat but it throws it down, join gives me an error, etc.
Without using the two DFs that are assembled quickly, I do it this way but it takes 2 to 3 minutes because it goes line by line in the position, supplanting the value in addition to the fact that SettingWithCopyWarning throws:
import random
import pandas as pd
from datetime import datetime
inicio = datetime(2017, 1, 30)
final = datetime(2019, 3, 21)
datos = []
for i in range (0, 10000):
datos.append(inicio + (final - inicio) * random.random())
df = pd.DataFrame(datos)
df.rename(columns={0: "Fecha"}, inplace=True)
df['proceso'] = ''
procesos = []
for a in range (1, 11):
procesos.append('Proceso' + str(a))
total = 0
posicion = 0
for i in range (0, 10):
for j in range ( 0, 1000):
df['proceso'][posicion] = procesos[total]
print(posicion)
posicion += 1
total += 1
I'm having trouble displaying legends on a scatter.
import pandas as pd
import numpy as np
import scipy.stats
import matplotlib
import matplotlib.pyplot as pp
import re
import mailbox
import csv
from IPython import display
from ipywidgets import interact, widgets
%matplotlib inline
datos = pd.read_csv('https://raw.githubusercontent.com/theengineeringworld/statistics-using-python/master/gapminder.xls')
datosDos = datos.dropna()
datosDos.reset_index(drop=True, inplace=True)
I am using the following function:
def plotyearDinamico(anio):
data = datosDos[datosDos.year == anio]
area = 10e-6 * datosDos.population
color = data.region.map({'Africa': 'skyblue', 'Europe' : 'gold',
'America' : 'palegreen', 'Asia' : 'coral'})
data.plot.scatter('babies_per_woman', 'age5_surviving', s = area, c =
color, linewidth = 1, edgecolors = 'k', figsize=(9,6))
pp.axis(ymin = 50, ymax = 105, xmin = 0, xmax = 8)
pp.xlabel('babies_per_woman')
pp.ylabel('age5_surviving')
Then calling that function I see the following graph:
interact(plotyearDinamico, anio=widgets.IntSlider(min=1950, max=2015,
step=1, value= 1950))
I know it's a legend() but I can't find how to put it on this chart.
Source: Example of what I need
How can I do to show the names of the continents since I only have them differentiated by colors? Something more or less like this:
import matplotlib.pyplot as plt
from numpy.random import rand
fig, ax = plt.subplots()
for color in ['red', 'green', 'blue']:
n = 750
x, y = rand(2, n)
scale = 200.0 * rand(n)
ax.scatter(x, y, c=color, s=scale, label=color,
alpha=0.3, edgecolors='none')
ax.legend()
ax.grid(True)
plt.show()
If I add pp.legend(data.region) to my graph, it shows the following (The area and color match but it only brings one)
I am reading a file and counting the words to show how many times each word is repeated.
readme = sc.textFile("README.md")
wordCounts = readme.flatMap(lambda line: line.split()).map(lambda word: (word,
1)).reduceByKey(lambda a, b: a+b)
wordCounts.collect()
Returns the following:
[('#', 1),
('Apache', 1),
('Spark', 14),
('is', 6),
('It', 2), ('provides', 1),
( 'high-level', 1),
('APIs', 1),
('in', 5), ('Scala,', 1),
('Java,', 1),
('an', 3) ,
('optimized', 1), ('engine', 1),
('supports', 2),
('computation', 1),
('analysis.',1),
('set', 2),
('of', 5),
('tools', 1),
('SQL', 2),
('MLlib', 1),
('machine', 1), ('learning,', 1),
( 'graphX', 1),
('graph', 1), ('processing,', 1),
('Documentation', 1),..... (There is more data but I cut it here)]
As you can see, the word that is repeated the most is Spark with 14 registers, now:
How can I show only that word and the total number of records?
Watching several Pandas tutorials I found this:
When reading csv files I sometimes get the following error:
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe1 in position 17: invalid continuation byte
In all the videos they give the option to open the file with Sublime and then save it as UTF-8.
Now, on the other hand, I found the following:
I open the file, go through it and apply the detect function of chardet so that it finds what type of encoding it has.
import chardet
with open('data/atp-tour/data.csv', 'rb') as f:
result = chardet.detect(f.read())
result['encoding']
Output: 'Windows-1252'
Once this is done, I can encode the file and make it work normally:
datos = pd.read_csv('data/atp-tour/data.csv', encoding=result['encoding'])
What is the best method for encoding? Fix it quick with sublime or find the type of encoding and apply it?
On the other hand, when applying the Chardet encoding, it sometimes gives a memory error that stops exiting when adding the parameter low_memory = False
DtypeWarning: Columns (4,5,7,16) have mixed types. Specify dtype option on import or set low_memory=False.
What does this mean? Since I investigated and on the same line sometimes it comes out and sometimes not when executing it
I am using the information from the following file:
To read it and display it I use the following:
import pandas as pd
import chardet
import numpy as np
### Si quieren usar el codigo de ejemplo, la siguiente linea
### tarda bastante en hacerse porque busca la codificación que usa el
### archivo, ( si usan el archivo reducido es instantáneo)
### {'encoding': 'Windows-1252', 'confidence': 0.73, 'language': ''}
with open('Direccion donde esta el CSV', 'rb') as f:
result = chardet.detect(f.read())
datos = pd.read_csv('Direccion donde esta el archivo',
encoding=result['encoding'], low_memory=False)
df = pd.DataFrame(datos)
limpiarNaN = df.replace(np.nan, '0')
archivoConvertidoInt = limpiarNaN.describe(include = [np.number])
archivoConvertidoInt = limpiarNaN.replace("NR", "0")
archivoConvertidoInt['Wsets'] = archivoConvertidoInt.Wsets.astype(float)
archivoConvertidoInt['WRank'] = archivoConvertidoInt.WRank.astype(float)
archivoConvertidoInt.set_index("Location", inplace = True)
(archivoConvertidoInt.loc[['Adelaide' , 'St. Petersburg'], 'Series' :
'Round']
.groupby(["Location", "Series", "Court", "Surface", "Round"])
["Series",].count())
This returns me the following:
As you can see, it shows me information about the locations of Adelaide and St. Petersburg.
Now:
1) How can I show the same information but not only from Adelaide and St. Petersburg but from all the locations between them inclusive, if I use : to separate it gives me a syntax error.
and 2) Adding a sum at the end I can know how much is the total number of series, how can I add it as a new row?
How to get something like this:
I am reading a csv and plotting in Python using the following code:
import pandas as pd
from matplotlib import pyplot as plt
data = pd.read_csv('Direccion del archivo Csv', header = 0, sep=';')
g = data.groupby(["SUBDETERMINANTE"])["POSITIVAS", "POSIBLES"].sum()
1) How to order g groupby from highest to lowest based on the positive field so that when making the graph they are in that order?
g.plot(kind='barh', legend='Reverse')
plt.title(u'ANALISIS DE SUBDETERMINANTES', size = 12.0)
plt.xlabel(u'Suma', size = 12.0)
plt.ylabel('SubDeterminantes', size = 12.0)
fig_size = plt.rcParams["figure.figsize"]
print ("Current size:", fig_size)
fig_size[0] = 10.0
fig_size[1] = 15.0
plt.savefig('detalle')
Show the graph with the data from highest to lowest according to the total number of positives:
2) At the end of the graph I download it in PNG, how can I make it show the entire name?
SOLVED THANKS TO ABULAFIA'S RESPONSE
import pandas as pd
from matplotlib import pyplot as plt
data = pd.read_csv('Direccion donde esta el archivo csv', header = 0, sep=';')
g = data.groupby(["SUBDETERMINANTE"])["POSITIVAS",
"POSIBLES"].sum().sort_values(by="POSITIVAS")
g.plot(kind='barh', legend='Reverse')
plt.title(u'ANALISIS DE SUBDETERMINANTES', size = 12.0)
plt.xlabel(u'Suma', size = 12.0)
plt.ylabel('SubDeterminantes', size = 12.0)
fig_size = plt.rcParams["figure.figsize"]
print ("Current size:", fig_size)
fig_size[0] = 10.0
fig_size[1] = 15.0
plt.savefig('detalle', bbox_inches='tight')
Documentation sort_values for dataframe in Pandas:
I am reading an API that brings me the horoscopes and from time to time a horoscope is missing.
Doing the following I save only the horoscopes that come to me in an array:
$datosJson = [];
foreach ($datos_diario['horoscopo'] as $key => $value) {
array_push($datosJson, $key);
}
var_dump($datosJson);
Which returns the following:
array(9) {
[0]=>
string(7) "acuario"
[1]=>
string(5) "aries"
[2]=>
string(6) "cancer"
[3]=>
string(11) "capricornio"
[4]=>
string(9) "escorpion"
[5]=>
string(7) "geminis"
[6]=>
string(3) "leo"
[7]=>
string(5) "libra"
[8]=>
string(6) "piscis"
}
The problem I have is that when going through that information with a for, Aquarius is in position 0, so the horoscope is not displayed in order.
Creating another array where the complete information is, how can I tell $dataJson: look, position 0 has to be Aries, as it is in $horoscope and that they are ordered, it doesn't matter if I get 2, 3, 11 from the api, 12 horoscopes.
$horoscopos = ['aries', 'tauro', 'geminis', 'cancer', 'leo', 'virgo',
'libra', 'escorpion', 'sagitario', 'capricornio', 'acuario', 'piscis'];
Try the following: Perform a foreach on horoscope and with the following logic: Does the value that brings horoscope exist in dataJson? If it exists I don't do anything, if I don't delete it from the horoscope and in this way the horoscope keeps the data that comes to me and sorts, the problem is that when doing the unset the positions are eliminated and if I don't get Taurus for example, Aries I am left in position 0 and geminis in position 2 and when doing the for it gives an error because it does not find position 1:
foreach ($horoscopos as $key => $value) {
$indice = array_search($value ,$datosJson,false);
if ($indice == ""){
unset($horoscopos[$key]);
}
}
Bring back:
array(8) {
[0]=>
string(5) "aries"
[2]=>
string(7) "geminis"
[3]=>
string(6) "cancer"
[4]=>
string(3) "leo"
[6]=>
string(5) "libra"
[7]=>
string(9) "escorpion"
[9]=>
string(11) "capricornio"
[11]=>
string(6) "piscis"
}
Based on the latter, can the positions be ordered in ascending order, changing the values it has so that it stays like this? That would fix everything.
array(8) {
[0]=>
string(5) "aries"
[1]=>
string(7) "geminis"
[2]=>
string(6) "cancer"
[3]=>
string(3) "leo"
[4]=>
string(5) "libra"
[5]=>
string(9) "escorpion"
[6]=>
string(11) "capricornio"
[7]=>
string(6) "piscis"
}
SOLVED THANKS TO PHPMyguel's ANSWER
Is it possible to add a very marked css class such as a black background to the days that cannot be selected in the calendar (min="current day")? As you can see in the image, the days that cannot be selected have a very gray color. Clearly, users with visual impairment have access to that page.
<input type="date" name="fechaInicio" id="fechaInicio"
min="2019-01-15" max="2020-01-01" class="readonly">
<script>
$(".readonly").keydown(function(e){
e.preventDefault();
});
</script>
I have the following doubt, although I already did it by taking the data and processing it with php, I wanted to know if I can bring the information from the base in the following way:
Based on this query I get a list of errors with their respective impacts (total of those errors):
SELECT mtg.idTemaGestion, mtg.detTemaGestion,
count(mtg.idTemaGestion) as total
from mac_gestion mg
inner join mac360_mac_gestion mmg on mg.idMacGestion = mmg.idMacGestion_FK
inner join mac360 m on m.idMac360 = mmg.idMac360_FK
inner join mac_tipogestion tg on tg.idTipoGestion = mg.idTipoGestion_FK
inner join datos_nomina dn on dn.idDatosNomina = m.idDatosNominaRecibe_FK
inner join site si on si.idSite = dn.idSite_FK
inner join mac_temagestion mtg on mtg.idTemaGestion = mg.idTemaGestion_FK
where mg.error = 'si'
and si.idSIte in (2)
and tg.idTipoGestion in (1)
group by mtg.idTemaGestion
order by total desc;
After this, what I need is to put together something like a pareto where it only shows me the errors that cover 80%.
Now, is it possible to return that information by query directly and not have to process the data later? With stored procedures? Can you declare variables within the query and validate from there? They guide me to know what I have to look for in order to answer this question that I have, thank you very much.
SOLUTION BASED ON THE ANSWER:
SELECT *, round((c.acumulado / total), 2) as pareto
from (
SELECT titulo, cantidad,
(select sum(cantidad) from errores) as total,
@acum:= @acum + cantidad as acumulado
from errores
JOIN (SELECT @acum:= 0) r
order by cantidad desc
) c
WHERE C.ACUMULADO/Total <= 0.80
I created a virtual host and when entering the URL sometimes it opens the Laravel page and other times it throws me that DateTime error, how can I solve it? Searching I found this:
I do the php artisan key:generate but nothing happens, which follows the post about changing the date format $datetime = DateTime::createFromFormat('mdY', '06-23-2014'); I don't understand where I should do it, I repeat I haven't programmed anything yet, sometimes the Laravel page loads and other times I get the error that I mentioned.
I have the following doubt, I have an if that if it is a process it shows some things but if it is any other it shows other things. At a glance like this:
<?php
if($proceso == 'Tp'){
echo "Todo esto que ve TP";
}
else{
echo "Todas estas cosas que ven los otro";
}
?>
How can I add a process that sees what 'Tp' sees and what the others see, that Tp sees what Tp sees, the others see their things and that new specific process sees what Everyone sees and what the others see?
Is it with an if or do I have to use a Switch?
Case 1 ve esto
Case 2 ve esto
Default Case 1 Case 2
I didn't find it, but can I set a case to see what other Cases see and for default to ring what case 1 and case 2 have?
If it looks bad, excuse me, I'm from a cell phone and I don't see if what is code fits well.
If I update person with an empty one that does not insert anything in Audit, but if I update person with a data, name, etc., that if I do a INSERT. I have a table of reservations with schedules, when someone makes a reservation I update the person field associated with the schedule, and that triggers the triggerone that leaves the record of who registered, when, etc., but in turn I have an event that every day at 00 :00:00 updateall the people fields are empty so that the next day it can be solved again, that is the problem, the updatereserve one is ok, when the event is executed triggerit takes the 24 empty updates and inserts 24 empty data in the audit table
This is the code triggerI currently have
DELIMITER $$
CREATE TRIGGER Pc_Auditoria
AFTER UPDATE ON pcTotal
FOR EACH ROW
INSERT INTO auditoria (pc, personaAnterior, horario, persona, fecha_mod)
VALUES (NEW.pc, OLD.persona, NEW.horario, NEW.persona, NOW());
DELIMITER ;
When I make a reservation: The record inserted ok in the audit: How is the audit when the event is executed: