0

According to solution in Gather multiple sets of columns I am trying to gather multiple sets of columns. All columns which I would like to gather have year in the end of the sentece. column.name.2010, column.name.2011, column.name.2012, column.name2.2010, column.name2.2011, column.name2.2012 etc.

I tried

db %>%
  gather(key, value, -c(1:6)) %>%
  extract(key, c("question", "Year"), "([^\d{4}]+)") %>%
  spread(question, value)

I would like to accomplish this:

Id Index.number Country.Name World.Rank.by.Assets Year  column.name. column.name2.
1    a            3              6                 2010    xxxx          xxy
2    b            4              6                 2011    xxyx          xyy
3    c            5              8                 2012    xxxs          sada

Head of db

     Id Index.number Country.Name World.Rank.by.Assets Total.Assets.th.EUR.2010 Total.Assets.th.EUR.2011
1 10931        10931            9                    6                     n.a.            1,965,283,000
2 10696        10696            9                    9                     n.a.            1,879,536,000
3 11948        11948            9                   13                     n.a.            1,723,608,000
4 11150        11150            9                   19                     n.a.            1,181,372,000
5 10708        10708            9                   NA                     n.a.            1,138,395,000
  Total.Assets.th.EUR.2012 Total.Assets.th.EUR.2013 Total.Assets.th.EUR.2014 Total.Assets.th.EUR.2015
1            1,907,200,000            1,810,522,000            2,077,759,000            1,994,193,000
2            1,783,220,000            1,688,264,000            1,762,714,000            1,698,859,000
3            1,617,429,000            1,518,811,000            1,589,044,000            1,529,294,000
4            1,250,889,000            1,214,193,000            1,308,138,000            1,334,391,000
5            1,147,521,000            1,123,520,000            1,223,298,000            1,166,535,000
  Total.Assets.th.EUR.2016 Deposits...Short.term.funding.th.EUR.2010
1            2,076,959,000                                      n.a.
2            1,722,849,000                                      n.a.
3            1,524,232,000                                      n.a.
4            1,382,241,000                                      n.a.
5            1,235,240,000                                      n.a.
  Deposits...Short.term.funding.th.EUR.2011 Deposits...Short.term.funding.th.EUR.2012
1                               947,516,000                               942,644,000
2                               917,933,000                               928,045,000
3                               808,028,000                               805,504,000
4                               452,417,000                               540,628,000
5                               604,912,000                               661,297,000
  Deposits...Short.term.funding.th.EUR.2013 Deposits...Short.term.funding.th.EUR.2015
1                               942,387,000                             1,032,100,000
2                               911,884,000                               891,035,000
3                               789,353,000                               796,777,000
4                               523,624,000                               673,520,000
5                               692,997,000                               707,185,000
  Deposits...Short.term.funding.th.EUR.2014 Deposits...Short.term.funding.th.EUR.2016
1                             1,031,624,000                             1,111,246,000
2                               890,072,000                               903,317,000
3                               773,094,000                               759,184,000
4                               587,177,000                               687,328,000
5                               740,298,000                               761,140,000
  Loan.loss.res...Gross.loans...2010 Loan.loss.res...Gross.loans...2011 Loan.loss.res...Gross.loans...2012
1                               n.a.                               4,04                               4,05
2                               n.a.                               3,55                               3,16
3                               n.a.                               4,53                               3,77
4                               n.a.                               4,21                               4,26
5                               n.a.                               1,96                               2,03
Community
  • 1
  • 1
piotr
  • 142
  • 1
  • 13
  • What do you intend to match with `"([^\d{4}]+)"`? – Wiktor Stribiżew May 08 '17 at 19:12
  • @WiktorStribiżew I tried to match non-year part of column name and group by it. – piotr May 08 '17 at 19:14
  • With a negated character class, `[^...]`, you only match a single char, you negated a set or range of chars, but not a sequence. – Wiktor Stribiżew May 08 '17 at 19:15
  • @WiktorStribiżew So how should I match a sequence? Putting a whole statemant into bracets returns an error `Error: '\d' is an unrecognized escape in character string starting ""([^(\d"` ? – piotr May 08 '17 at 19:21
  • If you use PCRE (`perl=TRUE`) you would be able to skip part of a string with SKIP-FAIL (see [demo](https://regex101.com/r/v4DZgN/1)). I can't help more here, I cannot repro the issue with the code provided in the question. The easiest way to do it is to *split* with `\d{4}`. – Wiktor Stribiżew May 08 '17 at 19:37
  • @WiktorStribiżew thanks :) I would try to do this your way :) – piotr May 08 '17 at 19:53

0 Answers0