CODEWITHSUNDEEP
rtidyr
James Crumpler
James Crumpler

Reputation: 204

pivot_wider a single row

This morning was running through my usual routine; coffee, NYT, and R based problems/learning. Have been working on a blog post, and utilizing the tidycensus package, and developed an interesting problem.

How to pivot_wider a single row from a table? or How to create proportions based on the condition of a single row?

I am trying to create a proportion of white and black population data based on Total population. The initial table looks like this...

Ohio_population_acs5 %>% 
  group_by(ID) %>%
  summarise(Total = sum(estimate))

# A tibble: 3 x 2
  ID                            Total
  <chr>                       <dbl>
1 Black or African American 1336133
2 Total                     8204988
3 White                     6283529

Using this table, my initial thought was to pivot_wider() the table, then pivot_longer() for a result that looks like this...

Ohio_population_acs5 %>% 
  group_by(ID) %>%
  summarise(Total = sum(estimate)) %>% 
  pivot_wider(names_from = ID, values_from = Total) %>% 
  pivot_longer(cols = c(`Black or African American`,`White`), names_to = "ID", values_to = "value")

# A tibble: 2 x 3
    Total ID                          value
    <dbl> <chr>                       <dbl>
1 8204988 Black or African American 1336133
2 8204988 White                     6283529

Then create a proportion ratio between the value column and the Total...

Ohio_population_acs5 %>% 
  group_by(ID) %>%
  summarise(Total = sum(estimate)) %>% 
  pivot_wider(names_from = ID, values_from = Total) %>% 
  pivot_longer(cols = c(`Black or African American`,`White`), names_to = "ID", values_to = "value") %>%
  group_by(ID) %>% 
  mutate(Prop_pop = value/Total)

# A tibble: 2 x 4
# Groups:   ID [2]
    Total ID                          value Prop_pop
    <dbl> <chr>                       <dbl>    <dbl>
1 8204988 Black or African American 1336133    0.163
2 8204988 White                     6283529    0.766

However, it would be far more convenient to select the row to pivot_wider() rather than the full column, then fill the values. Maybe there is an easier way to go about this process, but seems like there might be a function or process to combine the pivot_wider- pivot_longer into a single operation, especially to extract a single row value.

Hope this is an easier question than others. Thank you for the help with this, and looking forward to the responses. Please let me know if any clarification is needed (or reproducible example).

Upvotes: 1

Views: 845

Answers (1)

IceCreamToucan
IceCreamToucan

Reputation: 28675

You can get to that output without any pivoting

library(dplyr)

df %>% 
  rename_at('Total', ~'value') %>% 
  transmute(Total = value[ID == 'Total'],
            ID,
            value,
            Prop = value/Total) %>% 
  filter(ID != 'Total')

#     Total                        ID   value      Prop
# 1 8204988 Black or African American 1336133 0.1628440
# 2 8204988                     White 6283529 0.7658182

Upvotes: 3

Related Questions