Use for loop to get distinct value each column of a data frame with count of the distinct value-CodePudding

Below is the example if a table :-

Table

A      B    Income
satya India 10000
Amlan Usa   20000
Satya India 10000
Amlan Usa   20000
Sid   India 30000

Results required

Query 1

A <- sqldf( " Select distinct A,sum(Income) from Table ")

A     Total
Satya 20000
Amlan 40000
Sid   30000

Query 2

B <- sqldf( " Select distinct B,Sum(Income) from Table ")

B     Total 
India 50000
Usa   40000

I want use a loop so that I can write one query and get unique value of each filed with sum of income, instead of writing separate query for each field .I want this in R.

CodePudding user response：

You can do something like this:

First of all, you need to loop through the colnames in the for loop and inside sqldf statement, you need to pass that colname using `sprintf' Otherwise, it won't work.

col_names <- colnames(df)
col_names <- col_names[-length(col_names)]

for (i in col_names) {
  A <- sqldf(sprintf( "Select distinct %s, sum(Income) as Total from df group by %s", i, i))
  print(A)
}

The output looks like this:

      A Total
1 Amlan 40000
2 Satya 20000
3   Sid 30000
      B Total
1 India 50000
2    US 40000

Hereafter, you can append the results to a list or create data frame as you wish.

CodePudding user response：

You may try this

df <- data.frame(A = c('satya', 'Amlan', 'Satya', 'Amlan', 'Sid'), 
             B = c('India', 'Usa', 'India', 'Usa', 'India'), 
             Income = c(10000, 20000, 10000, 20000, 30000))

# Converting to upper case since R is case sensitive
df$A <- toupper(df$A)
df$B <- toupper(df$B)

Cols_Group <- names(df)[!names(df) %in% 'Income']

library(data.table)
setDT(df)

# Creating different objects for each column
for(i in Cols_Group) {
  assign(i, df[, .(Total = sum(Income)), i])
}

A

       A Total
1: SATYA 20000
2: AMLAN 40000
3:   SID 30000

B

       B Total
1: INDIA 50000
2:   USA 40000

If you need it in single object you may use the below code

Result <- data.table()

for(i in Cols_Group) {
  Result <- rbindlist(list(Result, df[, .(Total = sum(Income)), i]), use.names = FALSE)
}

Result

       A Total
1: SATYA 20000
2: AMLAN 40000
3:   SID 30000
4: INDIA 50000
5:   USA 40000