data pivoting

Community Help
1

I am struggling with data wrangling issue. I want to pivot data with year becoming column names and totals as values, in order to calculate the difference between the months. And then unpivot. I tried tidy.js pivotWider but got a lot of undefined per year.
I know how to approach this with R, but not sure if I am missing something.

initial data

[
  { year: 2020, month: "jan", total: 10 },
  { year: 2020, month: "feb", total: 20 },
  { year: 2019, month: "jan", total: 10 },
  { year: 2019, month: "feb", total: 7 },
  { year: 2018, month: "jan", total: 10 },
  { year: 2018, month: "feb", total: 5 }
]

how I would want it to look for pivoting

[
  { month: "jan", "2020": 10, "2019": 30, "2018": 20, diff: 10 },
  { month: "feb", "2020": 10, "2019": 30, "2018": 20, diff: 10 },
]

Unpivot again

[
  { month: "jan", year: 2020, diff: 10},
  { month: "feb", year: 2020, diff: 10},
  { month: "jan", year: 2019, diff: 10},
  { month: "feb", year: 2019, diff: 10},
]
2

Hi! Your example data have a few fields that vary from one snippet to the next (e.g. diff), and I’m not too clear about where these are coming from, so I haven’t tried to address that part. As for the pivots, I wonder if you’re just looking for a simple d3.group operation?

Here’s a very quick notebook:

With the pivot transformation of your original data resulting in something like this:

… it may be that this approach isn’t ‘flat’ enough?

Also, have you checked out some of the notebooks for R users? This one has examples of some simple data mappings that may be interesting:

… Let me know if this helps (or more importantly, if it doesn’t) and we can see how better to refine the approach :slight_smile:

3

Hi! Thanks so much for your reply! It is actually not 100% what I had in mind. Apologies for a poor explanation. I just wanted to make data wider in order to calculate difference between months of different years. So “diff” actually needs to be calculated. Anyway, here is a better explanation : pivoting test / Maria Fedorova | Observable. But I actually made it work (below “pivot wider works” title). What made the difference is that I just renamed columns to be months and not “month year”, and it worked as I expected it to work. It still gives me a lot of undefined when I use “month year”. I wonder why is this the case? Maybe I am missing something obvious.
Screenshot 2022-05-04 at 19.28.12

1 Like
4

Sorry, I meant to reply here, but instead replied to myself.

1 Like
5

Oh I understand why of course, because categories don’t repeat. So obvious. Anyway, all works, thanks so much!

1 Like