Dataset transpose ideas using AppEngine python without pandas

Asked Oct 28 '16 at 18:01

Active Nov 03 '16 at 16:58

Viewed 75 times

I need to convert this set of data [bigquery response]:

country     metric  quarter
Argentina   34174   1
Argentina   83961   2
Argentina   96373   3
Argentina   103782  4
Chile       7636    1
Chile       23434   2
Chile       19103   3
Chile       21729   4

to this:

Quarter Argentina   Chile
1       83961       19103
2       96373       21729
3       103782      23434
4       34174       7636

I use AppEngine[python]. My idea is use numpy 1.6.1. but I'm open to receive ideas...

Edit query used:

SELECT country, activity, sum(metric), quarter, year 
From *table* where country IN (*countries-parameters*)... 
group by 1,2,4,5   
order by 1,4 ASC

1 Solution from bigquery query:

Select
  quarter,
  SUM(IF(country=*country*,metric,0)) AS *country*,
  ...
From *table*
Where quarter IN (1,2,3,4) and country in (*countries-parameters*)
group by 1
order by 1 ASC;

edited Nov 03 '16 at 16:58

asked Oct 28 '16 at 18:01

Nicolas Bortolotti

hi Nick! how many rows you expect? seems a combination of group-by and self join might do it directly in bigquery. did you with issues with numpy? – Zig Mandel Oct 29 '16 at 05:03
eyy Zig! I added the query used, yes, I did groupby into the query for countries. Actually I used a algorithm with a couple of np-arrays and vstack but... I'm open to new ideas. – Nicolas Bortolotti Oct 29 '16 at 15:29
have you tried simulating a pivot in bigquery? http://stackoverflow.com/questions/19414609/how-to-simulate-a-pivot-table-with-bigquery – Zig Mandel Oct 29 '16 at 16:09
Thanks Zig works perfectly :) – Nicolas Bortolotti Oct 29 '16 at 16:28
but I can't solve the country-duplication I will explore the approach :) – Nicolas Bortolotti Oct 29 '16 at 16:37

Dataset transpose ideas using AppEngine python without pandas

0 Answers0