Pandas – dataframe to BigQuery

May 31, 2022

I have a df like the attached one:

I’d like to iter over this df and write data into bq, the table’s schema is something like: |coicop|unit|geo\time|period|value|
Where "period" and "value", in this case, would be "2021M05" and "108.36" or "2021M06" and "107.36".
I’m struggling to rotate these fields in order to be merged into the table

>Solution :

Use df.melt:

In [965]: df = df.melt(id_vars=['coicop', 'unit', 'geo\time'], var_name='period')

In [966]: df
Out[966]: 
  coicop unit geo\time   period   value
0   CP03  I15       AT  2021M05  108.36
1   CP03  I15       BE  2021M05  106.66
2   CP03  I15       BG  2021M05   97.33
3   CP03  I15       CH  2021M05  112.49
4   CP03  I15       CY  2021M05  101.30
5   CP03  I15       AT  2021M06  107.36
6   CP03  I15       BE  2021M06  106.65
7   CP03  I15       BG  2021M06   97.06
8   CP03  I15       CH  2021M06  110.42
9   CP03  I15       CY  2021M06  103.56

Then write this df into BQ.