How do I group my columns with groupby()?

Question

I am trying to group 2 columns given a third in RStudio. The actual table I have is this. What I would like to have is this. As you can see, the county names Attock, Bahawal, etc... repeat themselves 21 times for each of the services (tap, pump, etc...).

What I would like to have is the county names repeated only once in the top row, one column with the 21 services and the 2nd column with the respective population.

Anyone know how to do this, please? I've been trying different codes and functions but it doesn't seem to work.

df <- data.frame(District_name = c(rep('Attock', 4), rep('Bahawal', 4)), 
                 Services = rep(c('Tap', 'Pump', 'Well', 'Spring'), 2), 
                 RHH_Access = c(46, 53, 62, 58, 98, 102, 58, 63))

District_name	Services	RHH_Access
Attock	Tap	46
Attock	Pump	53
Attock	Well	62
Attock	Srping	58
Bahawal	Tap	98
Bahawal	Pump	102
Bahawal	Well	58
Bahawal	Srping	63

I have 36 districts and 21 services. I made some progress using the code from @moodymudskipper but still not there: Here is what I get now:

Services	Attock	Bahawal	Bhakkar
Tap	c(46, 78)	c(98, 152)	c(90, 132)
Pump	53	102	53
Well	c(62, 68)	c(58, 36)	c(65, 10)
Spring	58	63	25

This happens for tap and well because I have these services twice in the data.

Hi @gal, could you please share your table using `dput(df)`? So we can help you better. — Quinten, Apr 12 '22 at 14:56
I believe you want `pivot_wider()` from the tidyr package. You might want to take a look at this Q&A too : https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — moodymudskipper, Apr 12 '22 at 15:03
Hi @Quinten, I'm sorry I didn't really understand your question. I tried looking online to do it but didn't get it. Sorry, I am very new to R and don't know what I'm doing 99% of the time — gal, Apr 12 '22 at 15:17
@moodymudskipper thank you! It worked to a certain extent (my county names are only repeated once and so are the 21 services). However, as some services were duplicated, it gives me a vector inside the cell with 2 numbers and not just one number. I'll try to fix it but thank you - this was huge help! — gal, Apr 12 '22 at 15:19
You can use the command `dput(your table name)` in the console and cope and paste that in your question above. — Quinten, Apr 12 '22 at 15:21
@Quinten thank you for your explanation! I edited it, hope it makes more sense to you now. — gal, Apr 12 '22 at 15:41

score 0 · Answer 1 · answered Apr 12 '22 at 21:01

library(tidyverse)
df <- data.frame(District_name = c(rep('Attock', 4), rep('Bahawal', 4)), 
                 Services = rep(c('Tap', 'Pump', 'Well', 'Spring'), 2), 
                 RHH_Access = c(46, 53, 62, 58, 98, 102, 58, 63))

dfWide <- df %>% 
  pivot_wider(id_cols = Services, 
              names_from = District_name, 
              values_from = RHH_Access)

How do I group my columns with groupby()?

1 Answers1