1
$\begingroup$

I have one excel file with a sheet "OK".

enter image description here

I need to import the file, i tried:

s = Import["sales.xls", {"Sheets", "OK"}]

header1 = s[[1]];

data = s[[3 ;;]];

sDataset = Thread[header -> #] & /@ data // Map[Association]

But didn't work because the column headers are in the row 1 and 2 after the column 1. I need:

1.) SALES, GM, %GM and EBITDA pear YEAR per MONTH.

Ideas? Thanks!

Improve this question
$\endgroup$
2
$\begingroup$

For those like me who seem incapable of learning about Association and what it can do for me, here is a brute-force method of rearranging your data:

data = Import["sales.xls", {"Sheets", "OK"}];

(* Remove headers *)
data = data[[3 ;;]];

(* Attach year and split into separate rows *)
data = Flatten[Table[{Flatten[{2014, data[[i, 1 ;; 6]]}],
Flatten[{2015, data[[i, 1]], data[[i, 7 ;; 11]]}],
Flatten[{2016, data[[i, 1]], data[[i, 12 ;; 16]]}]},
   {i, Length[data]}], 1]

{{2014, "January", 1., 13., 25., 37., 49.},
 {2015, "January", 61., 73., 85., 97., 109.},
 {2016, "January", 121., 133., 145., 157., 169.},
 {2014, "February", 2., 14., 26., 38., 50.},
 {2015, "February", 62., 74., 86., 98., 110.},
 {2016, "February", 122., 134., 146., 158., 170.},
 {2014, "March", 3., 15., 27., 39., 51.},
 {2015, "March", 63., 75., 87., 99., 111.},
.
.
.
 {2016, "December", 132., 144., 156., 168., 180.}}
Improve this answer
$\endgroup$
  • $\begingroup$ Thanks. How I can compare for example, ebitda from 2014, 2015 y 2016? $\endgroup$ – chan go May 15 '17 at 2:20
  • 2
    $\begingroup$ That's a new question. $\endgroup$ – JimB May 15 '17 at 2:21
  • $\begingroup$ Given the analysis you want do to, it would be better to map the data to TimeSeries. For example, each dimension EBIT etc -> {date, value} and then create a TimeSeries. Use the form from Jim Baldwins answer and use Part[[All, {1, 2}]] to create the dates and then Transpose that result with the value columns. $\endgroup$ – FredrikD Jul 14 '17 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.