CODEWITHSUNDEEP
rtidyr
Floris508
Floris508

Reputation: 21

Converting multiple rows with pivot_longer

My real dataset is larger, but I tried to shorten it here. #What I have:

Income_100m_total_df <- data.frame(Name = c("Amstelpark", 
                                        "Amstelpark",
                                        "Amstelpark",
                                        "Amstelpark",
                                        "Amstelpark",
                                        "Amstelpark",
                                        "Amstelpark",
                                        "Amstelpark",
                                        "Amstelpark"),
            Income_type_households = c("Mean_low_income_households",
                             "Mean_low_income_households", 
                             "Mean_low_income_households",
                             "Mean_middle_income_households",
                             "Mean_middle_income_households",
                             "Mean_middle_income_households",
                             "Mean_high_income_households",
                             "Mean_high_income_households",
                             "Mean_high_incom_households"),
             Income_households = c(42.333, 42.333, 42.333, 32, 32, 32, 
                                   25.667, 25.667, 25.667),
             Income_type_persons = c("Mean_low_income_persons",
                                     "Mean_middle_income_persons", 
                                     "Mean_high_income_persons",
                                     "Mean_low_income_persons",
                                     "Mean_middle_income_persons",
                                     "Mean_high_income_persons",
                                     "Mean_low_income_persons",
                                     "Mean_middle_income_persons",
                                     "Mean_high_income_persons"),
            Income_households = c(33, 32, 35, 33, 32, 35, 33, 32, 35))

What I want

Income_100m_total_df_2 <- data.frame(Name = c("Amstelpark", 
                                        "Amstelpark",
                                        "Amstelpark"),
            Income_type_households = c("Mean_low_income_households",
                                       "Mean_middle_income_households",
                                       "Mean_high_incom_households"),
            Income_households = c(42.333, 32, 25.667),
            Income_type_persons = c("Mean_low_income_persons",
                                    "Mean_middle_income_persons", 
                                    "Mean_high_income_persons"),
             Income_households = c(33, 32, 35))

I tried to use pivot_longer to achieve this

pivot_longer(c(Mean_low_income_household,
             Mean_middle_income_household,
             Mean_high_income_household),
           names_to = "Income_type_households", 
           values_to = "Income_households") %>% 
pivot_longer(c(Mean_low_income_persons, 
             Mean_middle_income_persons, 
             Mean_high_income_persons), 
           names_to = "Income_type_persons", values_to = "Income_persons")

However, it only seems to apply to persons and not household, how do I achieve this?

Upvotes: 1

Views: 106

Answers (2)

Floris508
Floris508

Reputation: 21

I found a workaround of my code that gave me what I wanted:

inc_house <- Income_100m_total_df %>% 
group_by(Income_type_households) %>% 
  summarize(mean = mean(Income_households))

inc_person <- Income_100m_total_df %>% 
group_by(Income_type_persons) %>% 
  summarize(mean = mean(Income_households.1))

inc_both <- bind_cols(inc_house, inc_person)

Upvotes: 0

Andre Wildberg
Andre Wildberg

Reputation: 19088

Apart from the typo in [9,2] ("Mean_high_incom_households"), this should work:

cbind( unique(Income_100m_total_df[,1:3]), unique(Income_100m_total_df[,4:5]) )

#        Name        Income_type_households Income_households
#1 Amstelpark    Mean_low_income_households            42.333
#4 Amstelpark Mean_middle_income_households            32.000
#7 Amstelpark   Mean_high_income_households            25.667
#         Income_type_persons Income_households.1
#1    Mean_low_income_persons                  33
#4 Mean_middle_income_persons                  32
#7   Mean_high_income_persons                  35

Upvotes: 1

Related Questions