CODEWITHSUNDEEP
pythonrsasreshapetranspose
LonelySoul
LonelySoul

Reputation: 1222

Transposing / Reshaping based on variable but slightly different

I have a table which I want to Transpose based on a variables. Although I know how to Transpose based on one variable in similar fashion , doing it in this style is not that I am aware of. Is it possible to do it in SAS / Python / R.

My Input Table

Input

The output which I want looks like This

Output

Upvotes: 0

Views: 510

Answers (3)

Joe
Joe

Reputation: 63424

You can do this with proc report easily. Starting with this code, then modify it for your needs style-wise.

proc report data=have nowd;
  columns application month_year,(total_number_applications tota_applications_one total_applications_two);
  define application/group;
  define month_year/across;
run;

Upvotes: 1

Chris J
Chris J

Reputation: 7769

PROC TABULATE is the best way of doing this easily...

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a000146759.htm

Something along the lines of...

proc tabulate data=mydata ;
  class APPLICATION MONTH_YEAR ;
  var TOTAL_: ;
  table APPLICATION, MONTH_YEAR ;
run ;

Upvotes: 1

user20650
user20650

Reputation: 25854

This is a R solution

If your data looks like this

dat <- data.frame(App = c(2,3), time = c("Sept", "Jan"),
                  Total  = c(2,1777),
                  total_one = c(1, 1521),
                  total_two = c(1, 256))

dat
#     App time Total total_one total_two
#  1   2 Sept     2         1         1
#  2   3  Jan  1777      1521       256

You can reshape it to wide format

reshape(dat, idvar = "App" , timevar="time" , direction="wide")


#    App Total.Sept total_one.Sept total_two.Sept Total.Jan total_one.Jan total_two.Jan
# 1   2          2              1              1        NA            NA            NA
# 2   3         NA             NA             NA      1777          1521           256

See ?reshape for the details

Upvotes: 2

Related Questions