Convert Rows to Columns using RxJS / TypeScript / JavaScript

Question

I have the following table data:

date	value
01/01/2000	1
01/02/2000	2
01/01/2001	2
01/01/2002	1.5
01/02/2002	1.6

[{date: "01/01/2000", value: "1"},{date: "01/02/2000", value: "2"},{date: "01/01/2001", value: "2"},{date: "01/01/2002", value: "1.5"},{date: "01/02/2002", value: "1.6"}]

I would like to convert it to columns:

Year	Jan	Feb
2000	1	2
2001		2
2002	1.5	1.6

[{Year: "2000", Jan: "1", Feb: "2"},
{Year: "2001", Jan: "", Feb: "2"},
{Year: "2002", Jan: "1.5", Feb: "1.6"}]

How can I change it using RxJS / TypeScript / JavaScipt in Angular?

Thanks.

I think maybe you transposed Jan and Feb in your output for 2001 (jan = 2, feb = '')? — Randy Casburn
– Randy Casburn, Commented Feb 22, 2021 at 17:59

Owen Kelvin · Accepted Answer · 2021-02-22 18:58:58Z

1

See below approach using reduce

const initial = [{date: "01/01/2000", value: "1"},{date: "01/02/2000", value: "2"},{date: "01/01/2001", value: "2"},{date: "01/01/2002", value: "1.5"},{date: "01/02/2002", value: "1.6"}]

const allMonths = initial.reduce((prev, next) => {
  const date = next.date.substr(6,4) + '/' + next.date.substr(3,2)
  const month =  new Date(date).toLocaleString('default', { month: 'short' });
  return {...prev, [month]: ''}
}, {})

const temp = initial.reduce(
  (prev, next) => {
    const date = next.date.substr(6,4) + '/' + next.date.substr(3,2)
    const month =  new Date(date).toLocaleString('default', { month: 'short' });
    
    const Year = new Date(date).getFullYear()
    let prevYearVal = prev[Year]
    if(!prevYearVal) { prevYearVal = {Year,...allMonths} ;}
    return {...prev, [Year]: {...prevYearVal,Year, [month]: next.value}}
    
    return prev
  },
  {}
)
const final = Object.values(temp)
console.log(final)

answered Feb 22, 2021 at 18:58

Owen Kelvin

15.2k11 gold badges51 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

developer Over a year ago

Thanks @Owen, exactly what I was looking for.

Randy Casburn Over a year ago

@developer - is it? This is not cross browser. It does not work on Safari on MacOS or iOS. Thought you should know.

Randy Casburn · Accepted Answer · 2021-02-22 22:08:17Z

1

This requires a number of steps to complete the transformation.

Convert string date into usable JavaScript date object
- because the date is mm/dd/YYYY, rather then dd/mm/YYYY it must be transposed
- then a new Date is constructed using the decomposed string
Then it is just a matter of constructing the new object
The output from that is each date with corresponding values that must be combined
- The second iterator (.reduce()) combines the objects if the Year properly match

const input = [{date: "01/01/2000", value: "1"},{date: "01/02/2000", value: "2"},{date: "01/01/2001", value: "2"},{date: "01/01/2002", value: "1.5"},{date: "01/02/2002", value: "1.6"}];

const result = input.map(i => {
  const [day, month, year] = i.date.split('/');
  const [_, monthName] = new Date(year, month - 1, day).toDateString().split(' ');
  return {
    Year: year,
    [monthName]: i.value
  }
}).reduce((acc, d, idx) => {
  if (idx == 0) { 
    acc.push(d);
  } else if (acc[acc.length - 1].Year == d.Year) {
    acc[acc.length - 1] = Object.assign(acc[acc.length-1], d);
  } else {
    acc.push(d);
  }
  return acc;
}, []);

console.log(result);

edited Feb 22, 2021 at 22:08

answered Feb 22, 2021 at 18:57

Randy Casburn

14.2k1 gold badge20 silver badges32 bronze badges

3 Comments

Scott Sauyet Over a year ago

The OP says it works well, and I like the implementation, but I notice a miss in that the expected second entry is {Year: '2001', Jan: 2, Feb: ''}. Your version doesn't include the blank Feb node.

Randy Casburn Over a year ago

@ScottSauyet I realized that from the beginning, but I am reluctant to "make up" data that is not in the source. It is just a bad practice. If the OP wants to take the risk that is the OP's prerogative. "A man with a watch knows what time it is. A man with two watches is never sure." -- Segal's Law about single source of truth.

Scott Sauyet Over a year ago

Certainly there is a balancing act between that notion and the idea that we should return consistent data structures, not one with year/jan/feb and another with year/jan. Obviously if this is to be displayed in a table, at some point that missing data has to be created, even if it's just a ... | '' somewhere in the display code. Whether this is the right place to do that is a good question. But you knew it and made a reasonable choice, which is fine. It just wasn't clear that you'd made an explicit decision.

Scott Sauyet · Accepted Answer · 2021-02-23 14:30:01Z

Here is a version that works similarly to answers from Owen and Randy, but it separates the month-name processing into its own helper function. Written independently, the local variable names are all different, but they do the same work. It is also structured as a single function call rather than a set of steps:

const monthName = ((months) => (m) => months [m - 1])(
  '01|02|03|04|05|06|07|08|09|10|11|12' .split ('|') .map (
    m => new Date (`2021/${m}`).toLocaleString('default', {month: 'short'})
  )
)

const transform = (xs) => {
  const base = Object .fromEntries (
    [...new Set(input .map (
      ({date}) => date .slice (3, 5)
    ))]
    .map (month => [monthName (month), ""])
  )
  return Object .values (xs .reduce ((years, {date, value}) => {
    const Year = date .slice (6, 10), 
          Month = date.slice (3, 5)
    years [Year] = years [Year] || {Year, ...base}
    years [Year] [monthName(Month)] = value
    return years
  }, {}))
}

const input = [{date: "01/01/2000", value: "1"}, {date: "01/02/2000", value: "2"}, {date: "01/01/2001", value: "2"}, {date: "01/01/2002", value: "1.5"}, {date: "01/02/2002", value: "1.6"}]

console .log (transform (input))

.as-console-wrapper {max-height: 100% !important; top: 0}

One advantage is that it doesn't call the Date constructor for every object, simply calling it once for every calendar month. And if you don't want the locale string version but a fixed set of month names the helper can be even simpler:

const monthName = ((months) => (m) => months [m - 1]) (
  'Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec' .split ('|')
)

I tend to prefer functions written purely as expressions and not statement, so an alternate version might look like this:

const transform = (
  xs, 
  base = Object .fromEntries (
    [...new Set (xs .map (
      ({date}) => date .slice (3, 5)
    ))]
    .map (month => [monthName (month), ""])
  )
) => Object .values (
  xs .reduce ((a, {date, value}, _, __, 
    Year = date .slice (6, 10), Month = date.slice (3, 5)
  ) => ({
    ...a, 
    [Year]: {Year, ...(a [Year] || base), [monthName (Month)]: value}
  }), {})
)

It works the same way, and is somewhat less efficient, but I find it cleaner.

Collectives™ on Stack Overflow

Convert Rows to Columns using RxJS / TypeScript / JavaScript

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related