Calculate Ratios in columns, based on other columns












0














I am facing an thinking & programming problem. See below my question, I have no clue what a proper approach is (played with DPLYR's group_by, but without results). Many thanks in advance for trying helping me out here!



I have a data set like this:



Numbers   Area      Cluster  
1 A 1
0.8 A 1
0.78 A 1
0.7 B 1
0.4 A 2
0 C 1


I want to calculate two new columns:




  1. Show the % of Area's occurring in a specific cluster (Column_Example_1)

  2. Per Cluster, a new index of the column numbers (in a range from 1 - 0) (Column_example_2). The new ratio should be based on the column Numbers #note: in the example it is just an example, it could also done differently, but we I want to make sure that the column Numbers is leading)


The result should be like this:



Numbers   Area      Cluster  Example_1                             Example_2 
1 A 1 60% #5x cluster 1, and 3x Area A) 1
0.8 A 1 60% 0.8
0.78 A 1 60% 0.78
0.7 B 1 20% 0.7
0.4 A 2 100% 1
0 C 1 20% 0









share|improve this question






















  • Can you explain how you arrive at Example_2?
    – erocoar
    Nov 22 at 15:32










  • df %>% group_by(Cluster) %>% mutate(Example_2 = ifelse(row_number()==1, 1, Numbers)) ?
    – CER
    Nov 22 at 15:43










  • Hi @erocoar; yes, in this example it was to show the sequence of a specific cluster, based on the column numbers. (So if numbers is between 1 - 0, I want to have something similar for each cluster (so the highest value of a specific cluster should have a 1, the lowest should have a 0, and the rest between those two numbers, based on their number score of course). Sorry, hope that this explanation make sense for you.
    – R overflow
    Nov 23 at 7:29
















0














I am facing an thinking & programming problem. See below my question, I have no clue what a proper approach is (played with DPLYR's group_by, but without results). Many thanks in advance for trying helping me out here!



I have a data set like this:



Numbers   Area      Cluster  
1 A 1
0.8 A 1
0.78 A 1
0.7 B 1
0.4 A 2
0 C 1


I want to calculate two new columns:




  1. Show the % of Area's occurring in a specific cluster (Column_Example_1)

  2. Per Cluster, a new index of the column numbers (in a range from 1 - 0) (Column_example_2). The new ratio should be based on the column Numbers #note: in the example it is just an example, it could also done differently, but we I want to make sure that the column Numbers is leading)


The result should be like this:



Numbers   Area      Cluster  Example_1                             Example_2 
1 A 1 60% #5x cluster 1, and 3x Area A) 1
0.8 A 1 60% 0.8
0.78 A 1 60% 0.78
0.7 B 1 20% 0.7
0.4 A 2 100% 1
0 C 1 20% 0









share|improve this question






















  • Can you explain how you arrive at Example_2?
    – erocoar
    Nov 22 at 15:32










  • df %>% group_by(Cluster) %>% mutate(Example_2 = ifelse(row_number()==1, 1, Numbers)) ?
    – CER
    Nov 22 at 15:43










  • Hi @erocoar; yes, in this example it was to show the sequence of a specific cluster, based on the column numbers. (So if numbers is between 1 - 0, I want to have something similar for each cluster (so the highest value of a specific cluster should have a 1, the lowest should have a 0, and the rest between those two numbers, based on their number score of course). Sorry, hope that this explanation make sense for you.
    – R overflow
    Nov 23 at 7:29














0












0








0







I am facing an thinking & programming problem. See below my question, I have no clue what a proper approach is (played with DPLYR's group_by, but without results). Many thanks in advance for trying helping me out here!



I have a data set like this:



Numbers   Area      Cluster  
1 A 1
0.8 A 1
0.78 A 1
0.7 B 1
0.4 A 2
0 C 1


I want to calculate two new columns:




  1. Show the % of Area's occurring in a specific cluster (Column_Example_1)

  2. Per Cluster, a new index of the column numbers (in a range from 1 - 0) (Column_example_2). The new ratio should be based on the column Numbers #note: in the example it is just an example, it could also done differently, but we I want to make sure that the column Numbers is leading)


The result should be like this:



Numbers   Area      Cluster  Example_1                             Example_2 
1 A 1 60% #5x cluster 1, and 3x Area A) 1
0.8 A 1 60% 0.8
0.78 A 1 60% 0.78
0.7 B 1 20% 0.7
0.4 A 2 100% 1
0 C 1 20% 0









share|improve this question













I am facing an thinking & programming problem. See below my question, I have no clue what a proper approach is (played with DPLYR's group_by, but without results). Many thanks in advance for trying helping me out here!



I have a data set like this:



Numbers   Area      Cluster  
1 A 1
0.8 A 1
0.78 A 1
0.7 B 1
0.4 A 2
0 C 1


I want to calculate two new columns:




  1. Show the % of Area's occurring in a specific cluster (Column_Example_1)

  2. Per Cluster, a new index of the column numbers (in a range from 1 - 0) (Column_example_2). The new ratio should be based on the column Numbers #note: in the example it is just an example, it could also done differently, but we I want to make sure that the column Numbers is leading)


The result should be like this:



Numbers   Area      Cluster  Example_1                             Example_2 
1 A 1 60% #5x cluster 1, and 3x Area A) 1
0.8 A 1 60% 0.8
0.78 A 1 60% 0.78
0.7 B 1 20% 0.7
0.4 A 2 100% 1
0 C 1 20% 0






r






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 22 at 15:18









R overflow

652211




652211












  • Can you explain how you arrive at Example_2?
    – erocoar
    Nov 22 at 15:32










  • df %>% group_by(Cluster) %>% mutate(Example_2 = ifelse(row_number()==1, 1, Numbers)) ?
    – CER
    Nov 22 at 15:43










  • Hi @erocoar; yes, in this example it was to show the sequence of a specific cluster, based on the column numbers. (So if numbers is between 1 - 0, I want to have something similar for each cluster (so the highest value of a specific cluster should have a 1, the lowest should have a 0, and the rest between those two numbers, based on their number score of course). Sorry, hope that this explanation make sense for you.
    – R overflow
    Nov 23 at 7:29


















  • Can you explain how you arrive at Example_2?
    – erocoar
    Nov 22 at 15:32










  • df %>% group_by(Cluster) %>% mutate(Example_2 = ifelse(row_number()==1, 1, Numbers)) ?
    – CER
    Nov 22 at 15:43










  • Hi @erocoar; yes, in this example it was to show the sequence of a specific cluster, based on the column numbers. (So if numbers is between 1 - 0, I want to have something similar for each cluster (so the highest value of a specific cluster should have a 1, the lowest should have a 0, and the rest between those two numbers, based on their number score of course). Sorry, hope that this explanation make sense for you.
    – R overflow
    Nov 23 at 7:29
















Can you explain how you arrive at Example_2?
– erocoar
Nov 22 at 15:32




Can you explain how you arrive at Example_2?
– erocoar
Nov 22 at 15:32












df %>% group_by(Cluster) %>% mutate(Example_2 = ifelse(row_number()==1, 1, Numbers)) ?
– CER
Nov 22 at 15:43




df %>% group_by(Cluster) %>% mutate(Example_2 = ifelse(row_number()==1, 1, Numbers)) ?
– CER
Nov 22 at 15:43












Hi @erocoar; yes, in this example it was to show the sequence of a specific cluster, based on the column numbers. (So if numbers is between 1 - 0, I want to have something similar for each cluster (so the highest value of a specific cluster should have a 1, the lowest should have a 0, and the rest between those two numbers, based on their number score of course). Sorry, hope that this explanation make sense for you.
– R overflow
Nov 23 at 7:29




Hi @erocoar; yes, in this example it was to show the sequence of a specific cluster, based on the column numbers. (So if numbers is between 1 - 0, I want to have something similar for each cluster (so the highest value of a specific cluster should have a 1, the lowest should have a 0, and the rest between those two numbers, based on their number score of course). Sorry, hope that this explanation make sense for you.
– R overflow
Nov 23 at 7:29












2 Answers
2






active

oldest

votes


















2














Since you want to keep all rows, you can calculate the relative frequencies as follows:



library(tidyverse)
df <- data.frame(numbers = c(1, .8, .78, .7, .4, 0),
area = c("A", "A", "A", "B", "A", "C"),
cluster = c(1, 1, 1, 1, 2, 1))

df %>%
group_by(cluster) %>%
mutate(example_1 = n()) %>%
group_by(area, cluster) %>%
mutate(example_1 = n() / example_1)

# A tibble: 6 x 4
# Groups: area, cluster [4]
numbers area cluster example_1
<dbl> <fct> <dbl> <dbl>
1 1 A 1 0.6
2 0.8 A 1 0.6
3 0.78 A 1 0.6
4 0.7 B 1 0.2
5 0.4 A 2 1
6 0 C 1 0.2





share|improve this answer





















  • Thanks! The column Example_1 totally works! Do you also know how I can make the example_2 column with your dplyr approach? Really appreciated!!
    – R overflow
    Nov 23 at 7:26










  • This will do the trick. Thanks! library(scales) test <- test %>% group_by(cluster) %>% mutate(example_2 = rescale(numbers ))
    – R overflow
    Nov 23 at 9:03



















1














You can also do with data.table:



library(magrittr)
library(data.table)

df <- data.table(Numbers = c(1, .8, .78, .7, .4, 0),
Area = c(rep("A", 3), "B", "A", "C"),
Cluster = c(rep(1, 4), 2, 1))

df[, N := .N, by = c("Cluster")] %>%
.[, Example_1 := .N/N, by = c("Cluster", "Area")] %>%
.[, `:=`(N = NULL, Example_2 = Numbers)]


Output:



> df
Numbers Area Cluster Example_1 Example_2
1: 1.00 A 1 0.6 1.00
2: 0.80 A 1 0.6 0.80
3: 0.78 A 1 0.6 0.78
4: 0.70 B 1 0.2 0.70
5: 0.40 A 2 1.0 0.40
6: 0.00 C 1 0.2 0.00





share|improve this answer





















    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53433959%2fcalculate-ratios-in-columns-based-on-other-columns%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    2














    Since you want to keep all rows, you can calculate the relative frequencies as follows:



    library(tidyverse)
    df <- data.frame(numbers = c(1, .8, .78, .7, .4, 0),
    area = c("A", "A", "A", "B", "A", "C"),
    cluster = c(1, 1, 1, 1, 2, 1))

    df %>%
    group_by(cluster) %>%
    mutate(example_1 = n()) %>%
    group_by(area, cluster) %>%
    mutate(example_1 = n() / example_1)

    # A tibble: 6 x 4
    # Groups: area, cluster [4]
    numbers area cluster example_1
    <dbl> <fct> <dbl> <dbl>
    1 1 A 1 0.6
    2 0.8 A 1 0.6
    3 0.78 A 1 0.6
    4 0.7 B 1 0.2
    5 0.4 A 2 1
    6 0 C 1 0.2





    share|improve this answer





















    • Thanks! The column Example_1 totally works! Do you also know how I can make the example_2 column with your dplyr approach? Really appreciated!!
      – R overflow
      Nov 23 at 7:26










    • This will do the trick. Thanks! library(scales) test <- test %>% group_by(cluster) %>% mutate(example_2 = rescale(numbers ))
      – R overflow
      Nov 23 at 9:03
















    2














    Since you want to keep all rows, you can calculate the relative frequencies as follows:



    library(tidyverse)
    df <- data.frame(numbers = c(1, .8, .78, .7, .4, 0),
    area = c("A", "A", "A", "B", "A", "C"),
    cluster = c(1, 1, 1, 1, 2, 1))

    df %>%
    group_by(cluster) %>%
    mutate(example_1 = n()) %>%
    group_by(area, cluster) %>%
    mutate(example_1 = n() / example_1)

    # A tibble: 6 x 4
    # Groups: area, cluster [4]
    numbers area cluster example_1
    <dbl> <fct> <dbl> <dbl>
    1 1 A 1 0.6
    2 0.8 A 1 0.6
    3 0.78 A 1 0.6
    4 0.7 B 1 0.2
    5 0.4 A 2 1
    6 0 C 1 0.2





    share|improve this answer





















    • Thanks! The column Example_1 totally works! Do you also know how I can make the example_2 column with your dplyr approach? Really appreciated!!
      – R overflow
      Nov 23 at 7:26










    • This will do the trick. Thanks! library(scales) test <- test %>% group_by(cluster) %>% mutate(example_2 = rescale(numbers ))
      – R overflow
      Nov 23 at 9:03














    2












    2








    2






    Since you want to keep all rows, you can calculate the relative frequencies as follows:



    library(tidyverse)
    df <- data.frame(numbers = c(1, .8, .78, .7, .4, 0),
    area = c("A", "A", "A", "B", "A", "C"),
    cluster = c(1, 1, 1, 1, 2, 1))

    df %>%
    group_by(cluster) %>%
    mutate(example_1 = n()) %>%
    group_by(area, cluster) %>%
    mutate(example_1 = n() / example_1)

    # A tibble: 6 x 4
    # Groups: area, cluster [4]
    numbers area cluster example_1
    <dbl> <fct> <dbl> <dbl>
    1 1 A 1 0.6
    2 0.8 A 1 0.6
    3 0.78 A 1 0.6
    4 0.7 B 1 0.2
    5 0.4 A 2 1
    6 0 C 1 0.2





    share|improve this answer












    Since you want to keep all rows, you can calculate the relative frequencies as follows:



    library(tidyverse)
    df <- data.frame(numbers = c(1, .8, .78, .7, .4, 0),
    area = c("A", "A", "A", "B", "A", "C"),
    cluster = c(1, 1, 1, 1, 2, 1))

    df %>%
    group_by(cluster) %>%
    mutate(example_1 = n()) %>%
    group_by(area, cluster) %>%
    mutate(example_1 = n() / example_1)

    # A tibble: 6 x 4
    # Groups: area, cluster [4]
    numbers area cluster example_1
    <dbl> <fct> <dbl> <dbl>
    1 1 A 1 0.6
    2 0.8 A 1 0.6
    3 0.78 A 1 0.6
    4 0.7 B 1 0.2
    5 0.4 A 2 1
    6 0 C 1 0.2






    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered Nov 22 at 15:34









    erocoar

    3,69511233




    3,69511233












    • Thanks! The column Example_1 totally works! Do you also know how I can make the example_2 column with your dplyr approach? Really appreciated!!
      – R overflow
      Nov 23 at 7:26










    • This will do the trick. Thanks! library(scales) test <- test %>% group_by(cluster) %>% mutate(example_2 = rescale(numbers ))
      – R overflow
      Nov 23 at 9:03


















    • Thanks! The column Example_1 totally works! Do you also know how I can make the example_2 column with your dplyr approach? Really appreciated!!
      – R overflow
      Nov 23 at 7:26










    • This will do the trick. Thanks! library(scales) test <- test %>% group_by(cluster) %>% mutate(example_2 = rescale(numbers ))
      – R overflow
      Nov 23 at 9:03
















    Thanks! The column Example_1 totally works! Do you also know how I can make the example_2 column with your dplyr approach? Really appreciated!!
    – R overflow
    Nov 23 at 7:26




    Thanks! The column Example_1 totally works! Do you also know how I can make the example_2 column with your dplyr approach? Really appreciated!!
    – R overflow
    Nov 23 at 7:26












    This will do the trick. Thanks! library(scales) test <- test %>% group_by(cluster) %>% mutate(example_2 = rescale(numbers ))
    – R overflow
    Nov 23 at 9:03




    This will do the trick. Thanks! library(scales) test <- test %>% group_by(cluster) %>% mutate(example_2 = rescale(numbers ))
    – R overflow
    Nov 23 at 9:03













    1














    You can also do with data.table:



    library(magrittr)
    library(data.table)

    df <- data.table(Numbers = c(1, .8, .78, .7, .4, 0),
    Area = c(rep("A", 3), "B", "A", "C"),
    Cluster = c(rep(1, 4), 2, 1))

    df[, N := .N, by = c("Cluster")] %>%
    .[, Example_1 := .N/N, by = c("Cluster", "Area")] %>%
    .[, `:=`(N = NULL, Example_2 = Numbers)]


    Output:



    > df
    Numbers Area Cluster Example_1 Example_2
    1: 1.00 A 1 0.6 1.00
    2: 0.80 A 1 0.6 0.80
    3: 0.78 A 1 0.6 0.78
    4: 0.70 B 1 0.2 0.70
    5: 0.40 A 2 1.0 0.40
    6: 0.00 C 1 0.2 0.00





    share|improve this answer


























      1














      You can also do with data.table:



      library(magrittr)
      library(data.table)

      df <- data.table(Numbers = c(1, .8, .78, .7, .4, 0),
      Area = c(rep("A", 3), "B", "A", "C"),
      Cluster = c(rep(1, 4), 2, 1))

      df[, N := .N, by = c("Cluster")] %>%
      .[, Example_1 := .N/N, by = c("Cluster", "Area")] %>%
      .[, `:=`(N = NULL, Example_2 = Numbers)]


      Output:



      > df
      Numbers Area Cluster Example_1 Example_2
      1: 1.00 A 1 0.6 1.00
      2: 0.80 A 1 0.6 0.80
      3: 0.78 A 1 0.6 0.78
      4: 0.70 B 1 0.2 0.70
      5: 0.40 A 2 1.0 0.40
      6: 0.00 C 1 0.2 0.00





      share|improve this answer
























        1












        1








        1






        You can also do with data.table:



        library(magrittr)
        library(data.table)

        df <- data.table(Numbers = c(1, .8, .78, .7, .4, 0),
        Area = c(rep("A", 3), "B", "A", "C"),
        Cluster = c(rep(1, 4), 2, 1))

        df[, N := .N, by = c("Cluster")] %>%
        .[, Example_1 := .N/N, by = c("Cluster", "Area")] %>%
        .[, `:=`(N = NULL, Example_2 = Numbers)]


        Output:



        > df
        Numbers Area Cluster Example_1 Example_2
        1: 1.00 A 1 0.6 1.00
        2: 0.80 A 1 0.6 0.80
        3: 0.78 A 1 0.6 0.78
        4: 0.70 B 1 0.2 0.70
        5: 0.40 A 2 1.0 0.40
        6: 0.00 C 1 0.2 0.00





        share|improve this answer












        You can also do with data.table:



        library(magrittr)
        library(data.table)

        df <- data.table(Numbers = c(1, .8, .78, .7, .4, 0),
        Area = c(rep("A", 3), "B", "A", "C"),
        Cluster = c(rep(1, 4), 2, 1))

        df[, N := .N, by = c("Cluster")] %>%
        .[, Example_1 := .N/N, by = c("Cluster", "Area")] %>%
        .[, `:=`(N = NULL, Example_2 = Numbers)]


        Output:



        > df
        Numbers Area Cluster Example_1 Example_2
        1: 1.00 A 1 0.6 1.00
        2: 0.80 A 1 0.6 0.80
        3: 0.78 A 1 0.6 0.78
        4: 0.70 B 1 0.2 0.70
        5: 0.40 A 2 1.0 0.40
        6: 0.00 C 1 0.2 0.00






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 22 at 15:46









        JdeMello

        331112




        331112






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53433959%2fcalculate-ratios-in-columns-based-on-other-columns%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Berounka

            Sphinx de Gizeh

            Different font size/position of beamer's navigation symbols template's content depending on regular/plain...