SQL met R / dplyr zoals het hoort in R

Doe leuk met data

Gegevens uit één tabel halen

Alle velden SQL sqldf("Select * from Tone") Antwoord is de hele tabel. * staat voor alle kolommen dplyr select(Tone, klant, artikel, nettoprijs, brutoprijs)
Velden naar keuze SQL sqldf("Select klant, artikel from Tone") Antwoord is twee van de vier kolommen dplyr select(Tone, klant, artikel) of alle kolommen behalve 1 select(Tone, -nettoprijs) of een aantal kolommen op rij select(Tone, artikel:brutoprijs)
Velden samenvoegen met sql kan dat niet in R dplyr tabelnaam$nieuwveldnaam<-paste(tabelnaam$veld, "-", Tabelnaam$veld) "-" mag je ook weglaten maar dan zitten de velden strak tegen elkaar.
Uitbreiden van zoek criteria SQL SQL is één zin die steeds langer kan worden. dplyr In dplyr worden stukjes code aan elkaar gekoppeld met %>%
Aan bepaalde waarden voldoen: where / filter == is precies, = is soms ongeveer, dus altijd == gebruiken SQL sqldf("Select klant, artikel from Tone where klant == 'Hoogvliet' ") Hoogvliet is een string dus tussen ' ' (en niet " " want die gebruik je al) Antwoord is alle rijen waar de klant Hoogvliet is dplyr filter(Tone, klant == "Hoogvliet") Alles aan elkaar gebruik je %>% Omdat je met de tabel begint, hoef je die bij de volgende stukjes code niet meer te noemen. Tone %>% select(klant, artikel) %>% filter(klant == "Hoogvliet") Antwoord is alle rijen waar de klant Hoogvliet is
SQL sqldf("select klant, artikel from Tone where brutoprijs > 7") Antwoord de twee rijen met de duurste artikelen dplyr Bij dplyr moet je eerst filteren op brutoprijs want dan zit de kolom er nog in, en dan de kolommen selecteren. Of beide kolommen selecteren. Tone %>% filter(brutoprijs > 7) %>% select(klant, artikel) Antwoord de twee rijen met de duurste artikelen
Waarde lijkt op: like SQL sqldf("Select klant, artikel from Tone where klant like 'H%'") Antwoord alle rijen van Hoogvliet en Hema dplyr Tone %>% select(klant, artikel) %>% filter(klant %like% "H") Antwoord alle rijen van Hoogvliet en Hema
Waarde tussen twee waarden in: between SQL sqldf("select klant, artikel, brutoprijs from Tone where brutoprijs between 2 and 5") dplyr Tone %>% select(klant, artikel, brutoprijs) %>% filter(between(brutoprijs,2,5))
Meerdere where/filter: and, or, not AND SQL sqldf("select klant, artikel from Tone where klant like 'H%' and brutoprijs < 3") dplyr Tone %>% filter(klant %like% "H" & brutoprijs < 3) %>% select(klant, artikel)
OR SQL sqldf("select klant, artikel, brutoprijs from Tone where brutoprijs > 6 or artikel == 'speelgoed'") dplyr Tone %>% select(klant, artikel, brutoprijs) %>% filter(brutoprijs > 6 | artikel == "speelgoed")
NOT SQL sqldf("select klant, artikel, brutoprijs from Tone where NOT klant == 'VenD'") dplyr Tone %>% select(klant, artikel, brutoprijs) %>% filter(klant != "VenD")	Alle klanten behalve VenD
Ongelijk aan != SQL sqldf("select klant, artikel, brutoprijs from Tone where klant == 'Hema' AND artikel !='speelgoed'") dplyr Tone %>% select(klant, artikel, brutoprijs) %>% filter(klant == "Hema" & artikel !="speelgoed")
Op volgorde zetten: order by / arrange Oplopend van A-Z = Asc van ascending en kan je weglaten, dat is de standaard Aflopend van Z-A = Desc van descending en kan je niet weglaten. Eerst wordt er op de eerste kolom op volgorde gezet, dan op de tweede, enz. dus bijvoorbeeld: Ax, Ay, Az, Bk, Bl, Bm SQL sqldf("select klant, artikel from Tone order by klant, artikel") sqldf("select klant, artikel from Tone order by klant, artikel desc") dplyr Tone %>% select(klant, artikel) %>% arrange(klant, artikel) Tone %>% select(klant, artikel) %>% arrange(desc(klant), desc(artikel))
Aantal, optellen, min, max, gemiddeld met group by Group by gebruik je om groepen te maken waar je weer wat mee kan doen. Bijvoorbeeld het aantal rijen in elke de groep tellen. Het resultaat wordt een nieuwe kolom die je een naam geeft, bv aantal SQL sqldf("select klant, count(klant) as aantal from Tone group by klant") Overige mogelijkheden zijn: sum(veldnaam) als opgeteld min(veldnaam) as minimum max(veldnaam) as maximun avg(veldnaam) as gemiddeld dplyr Tone %>% select(klant) %>% arrange(klant) %>% group_by(klant) %>% summarise(aantal = n()) de functie n (aantal tellen) heeft altijd lege () Overige mogelijkheden zijn: summarise(opgeteld=sum(brutoprijs)) summarise(gemiddeld=mean(brutoprijs)) summarise(ander_gemiddeld=median(brutoprijs)) summarise(minimum=min(brutoprijs)) summarise(maximum=max(brutoprijs))
Rekenen met velden Als je meerder velden hebt met nummerieke waarden dan kan je daarmee rekenen. SQL sqldf("select klant, artikel, brutoprijs, nettoprijs, brutoprijs-nettoprijs as verschil from Tone") dplyr Tone %>% select(klant, artikel, brutoprijs,nettoprijs) %>% mutate(verschil=brutoprijs-nettoprijs)
SQL sqldf("select klant, artikel, brutoprijs, brutoprijs+1 as prijsverhoging from Tone") dplyr Tone %>% select(klant, artikel, brutoprijs) %>% mutate(prijsverhoging=brutoprijs+1)
SQL sqldf("select klant, artikel, brutoprijs, brutoprijs/100*21 as BTW from Tone") dplyr Tone %>% select(klant, artikel, brutoprijs) %>% mutate(BTW=(brutoprijs/100)*21)

Gegevens uit meerdere tabellen halen

Inner join SQL sqldf("select Tone.klant, Tone.artikel, Tone.brutoprijs, Ttwo.Korting, Ttwo.Percentage from Tone inner join Ttwo where Tone.klant = Ttwo.Klantnaam") dplyr Eerst gelijke kolomnamen in beide tabellen Dan tabellen samenvoegen Dan velden selecteren, sorteren, group by en filter enz colnames(Ttwo) <- c("klant", "Korting", "Percentage") Result1<-inner_join(Tone, Ttwo, by = "klant") Result1 %>% select(klant, artikel, brutoprijs, Korting, Percentage)
Overige joins left, righ, outer of full Omdat bij de voorbeeld-tabellen alles matcht, zie je geen verschil. Daarvoor moet je andere tabellen nemen. SQL Je kan de kolommen selecteren die je wilt Result2<-sqldf("select Tone.klant, Tone.artikel, Ttwo.Korting from Tone left join Ttwo where Tone.klant = Ttwo.Klantnaam") Result3<-sqldf("select Tone.klant, Tone.artikel Ttwo.Korting from Tone right join Ttwo where Tone.klant = Ttwo.Klantnaam") Result4<-sqldf("select Tone.klant, Tone.artikel, Ttwo.Korting from Tone outer join Ttwo where Tone.klant = Ttwo.Klantnaam") dplyr Result2<-left_join(Tone, Ttwo, by = "klant") Result2 %>% select(klant, artikel, brutoprijs, Korting, Percentage) Result3<-right_join(Tone, Ttwo, by = "klant") Result3 %>% select(klant, artikel, brutoprijs, Korting, Percentage) Result4<-full_join(Tone, Ttwo, by = "klant") Result4 %>% select(klant, artikel, brutoprijs, Korting, Percentage)
inner join die aan een waarde voldoet SQL sqldf("select Tone.klant, Tone.artikel, Tone.brutoprijs, Ttwo.Korting from Tone inner join Ttwo where Tone.klant = Ttwo.Klantnaam and korting = 'nee'") dplyr colnames(Ttwo) <- c("klant", "Korting", "Percentage") Result1<-inner_join(Tone, Ttwo, by = "klant") Result1 %>% select(klant, artikel, brutoprijs, Korting) %>% filter(Korting=="nee")
Grafiek van sql SQL/dplyr Je kan het resultaat van een sql of dplyr statement in een variabele zetten en die plotten. VoorGrafiek<- sql of dplyr statement ggplot(data=VoorGrafiek, aes(x=klant, y=aantal)) + geom_bar(stat="identity")
Welke rijen hebben geen match Met is.na(kolomnaam) kan je lege waarden vinden. Met !is.na(kolomnaam) vind je niet lege waarden. Bijvoorbeeld welke meetkastjes staat uit, leveren geen meetgegevens meer. ToneM heeft de ID's van de meetkastjes. TtwoM heeft de meetgegevens. De tabellen inlezen in R ToneM<- read.csv2("C://R/VB/ToneM.csv", sep = ";", header = T) TtwoM<- read.csv2("C://R/VB/TtwoM.csv", sep = ";", header = T) SQL GeenWaarden<-sqldf("select ToneM.ID, TtwoM.ID, TtwoM.temp from ToneM left join TtwoM where ToneM.ID == TtwoM.ID and TtwoM.temp IS NULL") Resultaat is leeg omdat hij reageert als inner join dplyr Check of de kolomnamen hetzelfde zijn tabellen koppelen TabellenSamen<-left_join(ToneM, TtwoM, by = "ID") GeenWaarden<-TabellenSamen %>% select(ID, temperatuur) %>% group_by(ID) %>% filter(is.na(temperatuur))