Iterate csv rows

[Old1]

Hello,

i have the csv with the above structure

Stores Financial 2010 2011
store1 income
store2 expenses
store1 income
store2 expenses

Above its year is a numeric values and goes on for 10 stores and 10 years.

I want to print the store with the best profit.
So basically, for each store to sum the incomes and the expenses, find the total difference and then print the store name with the best profit.

I can do a group_by(stores) and use x [ , purrr::map_lgl(x, is.numeric)] and then summurise_all() but i dont know how to do for each store.

any help?

[nirgrahamuk]

Is your starting structure really as you say ?
it would make more sense (but still need to be transformed) if it was more like
Stores Financial 2010 2011
store1 income
store1 expenses
store2 income
store2 expenses

because you have store1 income twice and no expenses and store2 expenses twice and no income...

[nirgrahamuk]

assuming the structure is as I have it you could try

library(tidyverse)
#example data but use your own in place of this df
(df<- tribble(~"Stores",~"Financial",~"2010",~"2011",
"store1","income","654","456",
"store1","expenses","321","234",
"store2","income","999","789",
"store2","expenses","789","565"))

(dfl<-pivot_longer(df,
                   cols = starts_with("2"),
                  names_to = "year",
                  values_to="amount") %>% mutate(amount=as.numeric(amount)))
(df2 <- pivot_wider(dfl,
                    id_cols=c(Stores,year),
                    names_from = Financial,
                    values_from = amount) %>% mutate(
                      profit = income-expenses
                    ))

# the best profit store from every year 
group_by(df2,
         year) %>% 
  top_n(n=1,wt=profit)

# the best year store for  each store 
group_by(df2,
         Stores) %>% 
  top_n(n=1,wt=profit)

[Old1]

@nirgrahamuk

group_by(df2, year) %>% top_n(n=1,wt=profit)

That will do the trick! thank you very much!