Summarizing Data from Excel Sheet

[Michael Stewart]

Hello All:

Good Morning

I am trying to summarize data from excel sheet.
I was hoping if anyone in the forum could help me please.
The reason for the request is - I need to get this done on a repetetive manner for multiple datasets and I was hoping to automate it.

I have included the data in the excel sheet(name -request) under sheet data and corresponding as stata dataset(stata 12 format)
I have included the desired summary format in the excel sheet(name -request) under sheet Results.

Can anyone help please ?

Thank you
Happy Halloween
Mike

[Hemanshu Kumar]

Michael Stewart a couple things:

• if reading in data from Excel into Stata is not a challenge for you, then we would really appreciate it if you were not to post attachments (whether Excel or Stata format), but instead post a data extract using dataex. This is the advice in the Statalist FAQ , which it would be useful for you to review.
• It would be useful to have a richer description of your problem. You say you have multiple datasets -- what changes across those datasets, and what remains the same?

[Mike Lacy]

You've posed your question so that it can't be addressed without opening an attachment. Very few people here are willing to open an attachment downloaded from the list (such files can have active and potentially dangerous content). You'll be much more likely to get an answer if you can describe your problem in detail, but in such a way that it does not require someone to download an attachment from you.

It sounds like your problem will entail obtaining a list of Excel file names, and for each item in that list, loading that file into Stata, and obtaining some summary information (frequency distributions? descriptive statistics? something else.) If this is correct, knowing what parts of this task are a problem for you would also make it easier to help you. If, for example, you know how to load and summarize your data for a single Excel file, then your problem is only about how to automate this for multiple files.

[Michael Stewart]

Hello:

Dear Mike lacy, Hemashu Kumar and all other members of the statalist community - Please accept my sincere apologies.
Thanks for taking time to reply to my request.
I am reposting the request as per the rules of the statalist group.

PROBLEM:
I work in an office and one of my responsibility is to summarize the schedule of the office staff
At our office:
1) staff works in the office and in the field.
2) The staff work at different place on particular days of the week
3) The duration of shifts (AM shift and PM shift) also varies.
4) The shifts are not same on each week and do not follow specific pattern

I have to summarize where each person works at the end of the month and was hoping if it could be done with stata.

The dataset generated by dataex is as follows:
variables include:
1) Date of the month
2) Week of the month
3) Location: wether office-AM(morning shift), office-PM(evening shift), Field-AM(morning shift) and Field-PM(evening shift)

The dataex code is as follows:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str11 location str7(dec_5 dec_6 dec_7 dec_8 dec_9 dec_12 dec_13 dec_14 dec_15 dec_16)
"Day"         "Mon"     "Tues"    "Wed"     "Thurs"   "Fri"     "Mon"     "Tues"    "Wed"     "Thurs"   "Fri"    
"Office-1-AM" "Mike"    "Melissa" "Frank"   "Melissa" "Melissa" "Mike"    "Melissa" "Frank"   "Melissa" "Melissa"
"Office-1-AM" "Dough"   "Wong"    "Jim"     "Wong"    "Wong"    "Dough"   "Wong"    "Jim"     "Wong"    "Wong"   
"Office-1-AM" "Carrie"  "Peter"   "jack"    "Peter"   ""        "Carrie"  "Peter"   "jack"    "Peter"   ""       
"Fleid-1-AM"  "Melissa" ""        "Mike"    "Mike"    "Frank"   "Melissa" ""        "Mike"    "Mike"    "Frank"  
"Fleid-1-AM"  "Wong"    "Dough"   "Dough"   "Dough"   "Jim"     "Wong"    "Dough"   "Dough"   "Dough"   "Jim"    
"Fleid-1-AM"  "Peter"   "Carrie"  ""        "Carrie"  "jack"    "Peter"   "Carrie"  ""        "Carrie"  "jack"   
"Office-1-PM" "Frank"   "Frank"   "Melissa" "Frank"   "Melissa" "Frank"   "Frank"   "Melissa" "Frank"   "Melissa"
"Office-1-PM" "Jim"     ""        "Wong"    "Jim"     "Wong"    "Jim"     ""        "Wong"    "Jim"     "Wong"   
"Office-1-PM" "jack"    "jack"    "Peter"   "jack"    "Peter"   "jack"    "jack"    "Peter"   "jack"    "Peter"  
"Office-1-PM" "Jill"    "Jill"    ""        "Jill"    ""        "Jill"    "Jill"    ""        "Jill"    ""       
"Fleid-1-PM"  "Mike"    "Mike"    "Frank"   "Mike"    "Frank"   "Mike"    "Mike"    "Frank"   "Mike"    "Frank"  
"Fleid-1-PM"  "Dough"   ""        "Jim"     "Dough"   "Jim"     "Dough"   ""        "Jim"     "Dough"   "Jim"    
"Fleid-1-PM"  "Carrie"  "Carrie"  "jack"    "Carrie"  "jack"    "Carrie"  "Carrie"  "jack"    "Carrie"  "jack"   
end

The aspired result format is as follows:
(It is a summary where each person worked in week-1, week-2 and combined-(week-1 and week-2))

* Example generated by -dataex-. To install: ssc install dataex
clear
input str11 location str9(mike dough carrie melissa wong peter frank jim jack jill)
"Week_1"      "Week_1"    "Week_1"    "Week_1"    "Week_1"    "Week_1"    "Week_1"    "Week_1"    "Week_1"    "Week_1"    "Week_1"   
"Office-1-AM" "1"         "1"         "1"         "3"         "3"         "2"         "1"         "1"         "1"         "0"        
"Field-1-AM"  "2"         "3"         "2"         "1"         "1"         "1"         "1"         "1"         "1"         "0"        
"Office-1-PM" "0"         "0"         "0"         "2"         "2"         "2"         "3"         "2"         "3"         "3"        
"Field-1-PM"  "3"         "2"         "3"         "0"         "0"         "0"         "3"         "2"         "2"         "0"        
"Week_2"      "Week_2"    "Week_2"    "Week_2"    "Week_2"    "Week_2"    "Week_2"    "Week_2"    "Week_2"    "Week_2"    "Week_2"   
"Office-1-AM" "1"         "1"         "1"         "3"         "3"         "2"         "1"         "1"         "1"         "0"        
"Field-1-AM"  "2"         "3"         "2"         "1"         "1"         "1"         "1"         "1"         "1"         "0"        
"Office-1-PM" "0"         "0"         "0"         "2"         "2"         "2"         "3"         "2"         "3"         "3"        
"Field-1-PM"  "3"         "2"         "3"         "0"         "0"         "0"         "3"         "2"         "2"         "0"        
"COMBINED "   "COMBINED " "COMBINED " "COMBINED " "COMBINED " "COMBINED " "COMBINED " "COMBINED " "COMBINED " "COMBINED " "COMBINED "
"Office-1-AM" "2"         "2"         "2"         "6"         "6"         "4"         "2"         "2"         "2"         "0"        
"Field-1-AM"  "4"         "6"         "4"         "2"         "2"         "2"         "2"         "2"         "2"         "0"        
"Office-1-PM" "0"         "0"         "0"         "4"         "4"         "4"         "6"         "4"         "6"         "6"        
"Field-1-PM"  "6"         "4"         "6"         "0"         "0"         "0"         "6"         "4"         "4"         "0"        
end

Can you please help,
Sincerely,
Mike

[Hemanshu Kumar]

Here is some code that may do the trick:

local locvar location
unab allvars: *
local days: list allvars - locvar

forval i = 1/10 {
    local var: word `i' of `days'
    local week_`var' = string(1+floor(`i'/6)) in l
}

drop if location == "Day"
gen int n = _n

rename (`days') date_=

reshape long date_, i(n location) j(day) string
drop n
rename date_ person
replace person = strtoname(lower(person))

encode day, gen(day_id)

gen week = .
foreach day of local days {
    replace week = `week_`day'' if day == "`day'"
}

collapse (count) day_id , by(week person location)
rename day_id num_days
drop if missing(person)

forval i = 1/3 {
    preserve
        if `i' < 3 {
                keep if week == `i'
                drop week
        }
        else collapse (sum) num_days, by(person location)
            
        reshape wide num_days, i(location) j(person) string
        foreach var of varlist num_days* {
            replace `var' = 0 if missing(`var')
        }
        rename num_days* *

        set obs `=_N+1'

        foreach var of varlist * {
            tostring `var', replace
            if `i' < 3 replace `var' = "Week_`i'" in l
                else replace `var' = "COMBINED" in l
        }

        gen n = _n
        replace n = 0 in l
        sort n
        drop n
        tempfile set`i'
        save `set`i''
    restore
}

clear
append using `set1' `set2' `set3'

This produces:

. list, noobs sep(0)

  +---------------------------------------------------------------------------------------------------------------------------+
  |    location     carrie      dough      frank       jack       jill        jim    melissa       mike      peter       wong |
  |---------------------------------------------------------------------------------------------------------------------------|
  |      Week_1     Week_1     Week_1     Week_1     Week_1     Week_1     Week_1     Week_1     Week_1     Week_1     Week_1 |
  |  Fleid-1-AM          2          3          1          1          0          1          1          2          1          1 |
  |  Fleid-1-PM          3          2          2          2          0          2          0          3          0          0 |
  | Office-1-AM          1          1          1          1          0          1          3          1          2          3 |
  | Office-1-PM          0          0          3          3          3          2          2          0          2          2 |
  |      Week_2     Week_2     Week_2     Week_2     Week_2     Week_2     Week_2     Week_2     Week_2     Week_2     Week_2 |
  |  Fleid-1-AM          2          3          1          1          0          1          1          2          1          1 |
  |  Fleid-1-PM          3          2          2          2          0          2          0          3          0          0 |
  | Office-1-AM          1          1          1          1          0          1          3          1          2          3 |
  | Office-1-PM          0          0          3          3          3          2          2          0          2          2 |
  |    COMBINED   COMBINED   COMBINED   COMBINED   COMBINED   COMBINED   COMBINED   COMBINED   COMBINED   COMBINED   COMBINED |
  |  Fleid-1-AM          4          6          2          2          0          2          2          4          2          2 |
  |  Fleid-1-PM          6          4          4          4          0          4          0          6          0          0 |
  | Office-1-AM          2          2          2          2          0          2          6          2          4          6 |
  | Office-1-PM          0          0          6          6          6          4          4          0          4          4 |
  +---------------------------------------------------------------------------------------------------------------------------+

[Michael Stewart]

Dear Hemanshu Kumar:
The code works perfectly
I cant thank you enough - the code is extremely helpful for me.
Truly appreciate it.
I hope you have a good weekend
Sincerely,
Mike