Fun problem : Data Transpose

Common patterns for data reshaping / transformation.

Tip(s):

[1] Python Solution.

[2] SQL Solution.

Question #1: Transform Table A into Table B 

Table A

ID Year Value
2521 2013 5
2521 2014 6
2521 2015 5
2523 2014 15
2523 2015 12
2525 2014 2
2525 2015 3
2527 2015 3

Table B

ID 2013 2014 2015
2521 5 6 5
2523 NULL 15 12
2525 NULL 2 3
2527 NULL NULL 3

 

 

Question #2: Transform Table A into Table B 

Table A

Date

LanguageTag

App

AcceptanceRate

8/10/2015

en-US

Excel

0.96

8/11/2015

en-US

Excel

1.00

8/12/2015

en-US

Excel

0.94

8/10/2015

en-US

Word

0.68

8/11/2015

en-US

Word

0.88

8/12/2015

en-US

Word

0.93

Table B

Date

LanguageTag

Excel

Word

8/10/2015

en-US

0.96

0.68

8/11/2015

en-US

1.00

0.88

8/12/2015

en-US

0.94

0.93

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s