CODEWITHSUNDEEP
pythonsqlpandas
bphi
bphi

Reputation: 3195

Pivot Dataframe for to_sql

I have a Dataframe that looks like this

          id_1    id_2    id_3   ...
date_1       1       3       4
date_2       4       6       3
date_3       5       7       1
...

I am also retrieving test_id from a SQL Server database with SELECT @@IDENTITY;

I want to append this data to an existing table in the same SQL Server database in this format

TEST_ID    DATE    ID    VALUE
test_id   date_1  id_1     1
test_id   date_1  id_2     3
test_id   date_1  id_3     4
test_id   date_2  id_1     4
test_id   date_2  id_2     6
test_id   date_2  id_3     3
test_id   date_3  id_1     5
test_id   date_3  id_2     7
test_id   date_3  id_3     1

How can I prepare the Dataframe so that to sql will write it to the table in the desired format?

I have tried resetting the index and pivoting, but I can't figure it out.

Upvotes: 1

Views: 272

Answers (2)

BENY
BENY

Reputation: 323326

d=d.reset_index().melt('index')
d['TEST_ID']='TEST_ID'
d.rename(columns={'index': 'DATE', 'variable': 'ID'})

    Out[147]: 
     DATE    ID  value  TEST_ID
0  date_1  id_1      1  TEST_ID
1  date_2  id_1      4  TEST_ID
2  date_3  id_1      5  TEST_ID
3  date_1  id_2      3  TEST_ID
4  date_2  id_2      6  TEST_ID
5  date_3  id_2      7  TEST_ID
6  date_1  id_3      4  TEST_ID
7  date_2  id_3      3  TEST_ID
8  date_3  id_3      1  TEST_ID

Upvotes: 1

Zero
Zero

Reputation: 76967

Using stack

In [3023]: (df.stack().reset_index(name='VALUE')
              .rename(columns={'level_0': 'DATE', 'level_1': 'ID'})
              .assign(TEST_ID='test_id'))
Out[3023]:
     DATE    ID  VALUE  TEST_ID
0  date_1  id_1      1  test_id
1  date_1  id_2      3  test_id
2  date_1  id_3      4  test_id
3  date_2  id_1      4  test_id
4  date_2  id_2      6  test_id
5  date_2  id_3      3  test_id
6  date_3  id_1      5  test_id
7  date_3  id_2      7  test_id
8  date_3  id_3      1  test_id

Upvotes: 1

Related Questions