[FIXED] How to pivot and rename columns based on several grouped columns

Issue

I am getting trouble using agg function and renaming results properly. So far I have made the table of the following format.

sheet	equipment	chamber	time	value1	value2
a	E1	C1	1	11	21
a	E1	C1	2	12	22
a	E1	C1	3	13	23
b	E1	C1	1	14	24
b	E1	C1	2	15	25
b	E1	C1	3	16	26

I would like to create a statistical table like this:

sheet	E1_C1_value1_mean	E1_C1_value1_min	E1_C1_value1_max	E1_C1_value2_mean	E1_C1_value2_min	E1_C1_value2_max
a	12	11	13	22	21	23
b	15	14	16	25	24	26

Which I would like to groupBy "sheet", "equipment", "chamber" to aggregate mean, min, max values. I also need to rename column by the rule: equip + chamber + aggregation function. There are multiple "equipment" names and "chamber" names.

Solution

As pivot in spark only accept single column, therefore you have to concat the column which you want to pivot:

df = spark.createDataFrame(
    [
        ('a', 'E1', 'C1', 1, 11, 21),
        ('a', 'E1', 'C1', 2, 12, 22),
        ('a', 'E1', 'C1', 3, 13, 23),
        ('b', 'E1', 'C1', 1, 14, 24),
        ('b', 'E1', 'C1', 2, 15, 25),
        ('b', 'E1', 'C1', 3, 16, 26),
    ],
    schema=['sheet', 'equipment', 'chamber', 'time', 'value1', 'value2']
)

df.printSchema()
df.show(10, False)
+-----+---------+-------+----+------+------+
|sheet|equipment|chamber|time|value1|value2|
+-----+---------+-------+----+------+------+
|a    |E1       |C1     |1   |11    |21    |
|a    |E1       |C1     |2   |12    |22    |
|a    |E1       |C1     |3   |13    |23    |
|b    |E1       |C1     |1   |14    |24    |
|b    |E1       |C1     |2   |15    |25    |
|b    |E1       |C1     |3   |16    |26    |
+-----+---------+-------+----+------+------+

Assume there are lots of columns that you want to do the aggregation, you can use a loop to create and prevent the bulky coding:

aggregation = []
for col in df.columns[-2:]:
    aggregation += [func.min(col).alias(f"{col}_min"), func.max(col).alias(f"{col}_max"), func.avg(col).alias(f"{col}_mean")]


df.withColumn('new_col', func.concat_ws('_', func.col('equipment'), func.col('chamber')))\
    .groupby('sheet')\
    .pivot('new_col')\
    .agg(*aggregation)\
    .orderBy('sheet')\
    .show(100, False)
+-----+----------------+----------------+-----------------+----------------+----------------+-----------------+
|sheet|E1_C1_value1_min|E1_C1_value1_max|E1_C1_value1_mean|E1_C1_value2_min|E1_C1_value2_max|E1_C1_value2_mean|
+-----+----------------+----------------+-----------------+----------------+----------------+-----------------+
|a    |11              |13              |12.0             |21              |23              |22.0             |
|b    |14              |16              |15.0             |24              |26              |25.0             |
+-----+----------------+----------------+-----------------+----------------+----------------+-----------------+

Answered By - Jonathan Lam

Answer Checked By - Mary Flores (PHPFixing Volunteer)

Read More

[FIXED] How do I rename relationships in Neo4j?

Issue

I realized only after importing a ton of nodes that I had created relationships called START, which is a reserved keyword. Querying the DB through the Cypher console hence always complains about the reserved keywords:

SyntaxException: reserved keyword "start n=node(0) match n<-[:START]-r return count(r)"

The only workaround that comes to mind is creating new copy relationships with a different name and then deleting the old ones.

Is there an easy way to rename all of these relationships or some way to escape reserved keywords in Cypher?

Solution

You are right. You cannot rename an already existing relationship. You'd have to run through all relationships, create the new one in parallel (including all properties) and then remove the old one.

You may also want to consider quoting the reserved word START in your cypher queries with backticks and leave the relationships as they are:

start n=node(0) match n<-[:`START`]-r return count(r)

Answered By - James

Answer Checked By - Pedro (PHPFixing Volunteer)

Read More

[FIXED] How to change the name of a column in r from an integer for a graph

Issue

I'm trying to do a bar plot in R the days of the week are the x axis, and I want them to be the labels for each bar but in the dataset they are marked from 1 to 7 starting from Sunday so I did the following:

week_day2$day_of_week <- recode(week_day2$day_of_week, 
   "1"="Sunday",
   "2"="Monday",
   "3"="Tuesday",
   "4"="Wednesday",
   "5"="Thursday",
   "6"="Friday",
   "7"="Saturday")

But then I couldn't make the bar plot in order so I used forcats library

library(tidyverse)
library(forcats)

Graph_major_days <- week_day2 %>%
  mutate(name = fct_relevel(day_of_week, 
            "Sunday", "Monday", "Tuesday", 
            "Wednesday", "Thursday", "Friday", 
            "Saturday")) %>%
  ggplot( aes(x=name, y=n)) +
    geom_bar(stat="identity")

I want to know if there is an easier/readable way to do this kind of thing

Solution

If I understood you correctly, you should keep the numbers in the dataset and just change the labels of the plot to the weekdays instead. Here's a complete example:

library(ggplot2)
set.seed(0)

df <- data.frame(day_of_week = sample(1:7, 7), n = rnorm(7))
days = c("Sun", "Mon", "Tue", "Thu", "Wed", "Fri", "Sat")

ggplot(df, aes(x = day_of_week, y = n)) + 
  geom_bar(stat="identity") +
  scale_x_continuous(breaks = 1:7, labels = days)

As you can see, even though the days are not ordered in the dataset they appear ordered in the plot. If you need to you can also add the days of the week to the dataset, just don't remove the numeric values.

Answered By - Molx

Answer Checked By - Marie Seifert (PHPFixing Admin)

Read More

[FIXED] How do I customize the .exe filename for Microsoft cmdline compiler, cl?

Issue

The only two options that I was able to find from googling are /OUT and /Fe, but neither works for me:

Using /Fe shows no error but no output file found in the current dir either:

C:\hands_on\C>cl /Fe:test.exe main.c
Microsoft (R) C/C++ Optimizing Compiler Version 15.00.30729.01 for x64
Copyright (C) Microsoft Corporation.  All rights reserved.

main.c
Microsoft (R) Incremental Linker Version 9.00.30729.01
Copyright (C) Microsoft Corporation.  All rights reserved.

/out::test.exe
main.obj

C:\hands_on\C>ls
main.c  main.obj

Using /OUT gives errors:

C:\hands_on\C>cl /OUT:test.exe main.c
Microsoft (R) C/C++ Optimizing Compiler Version 15.00.30729.01 for x64
Copyright (C) Microsoft Corporation.  All rights reserved.

cl : Command line warning D9025 : overriding '/Ot' with '/Os'
cl : Command line warning D9025 : overriding '/Os' with '/Ot'
cl : Command line warning D9002 : ignoring unknown option '/OU'
cl : Command line warning D9002 : ignoring unknown option '/OT'
cl : Command line warning D9002 : ignoring unknown option '/O:'
cl : Command line warning D9002 : ignoring unknown option '/Oe'
cl : Command line warning D9002 : ignoring unknown option '/O.'
cl : Command line warning D9002 : ignoring unknown option '/Oe'
cl : Command line warning D9002 : ignoring unknown option '/Oe'
main.c
Microsoft (R) Incremental Linker Version 9.00.30729.01
Copyright (C) Microsoft Corporation.  All rights reserved.

/out:main.exe
main.obj

The version of the compiler:

C:\hands_on\C>cl
Microsoft (R) C/C++ Optimizing Compiler Version 15.00.30729.01 for x64
Copyright (C) Microsoft Corporation.  All rights reserved.

usage: cl [ option... ] filename... [ /link linkoption... ]

Solution

The syntax is:

cl /Fetest.exe main.c

with no space or punctuation, or:

cl /Fe: test.exe main.c

with a colon and space.

You have a colon but no space:

cl /Fe:test.exe main.c

Source

Answered By - ChrisF

Answer Checked By - Clifford M. (PHPFixing Volunteer)

Read More

[FIXED] How can I rename a database column in a Ruby on Rails migration?

Issue

I wrongly named a column hased_password instead of hashed_password.

How do I update the database schema, using migration to rename this column?

Read More

[FIXED] Renaming a file on log out

Issue

I'm trying to rename a file with PHP but for some reason it doesnt work, do I have to activate some special permissions under PHP?

heres my code for the php file

<?php
  if(!rename('file.php','filer.php'))
  {
    echo "Couldn't rename file!";
  }
  else 
  {
    echo "file renamed succesfully!";
  }
?>

I'm trying to rename a file on my /var/www directory when they sign out of a login area, so that way they cant access back hitting back button. Do I have an error on my code? Or is there another way to prevent this?

Solution

The Default permissions on this folder /var/www/ are: chmod 755 /var/www/

"read, write, and execute by owner" and "read and execute by the group and everyone else" (-rwxr-xr-x)

and the files inside the folder /var/www/file are: chmod 644 /var/www/file

I don't know if you have changed the Permissions or simply execute this command and it we'll work.

chmod 777 /var/www/file.php

"read, write, and execute by owner, group and everyone else"

Answered By - Alaeddine

Read More