Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
SQL Pivot EAV
#1
I am very new to Python but I have a programming background.
I can read data from SQL query in EAV format (I am stuck with the data).

HOLEID, PROJECT, name, value
123, RH, a, b
123, RH, x, y
234, RH, a, c

I would like to pivot the data to a flat table and display it as

holeid, project, a, x
123, RH, b, y
234, RH, c, NULL

Is this possible in Python?

Thanks in advance
Reply
#2
definitely possible. the exact approach may vary - e.g. what packages you use/are ready to use, etc.
for example, using pandas

import pandas
data = [(123, 'RH', 'a', 'b'), (123, 'RH', 'x', 'y'), (234, 'RH', 'a', 'c')]

df = pandas.DataFrame(data, columns=['HOLEID', 'PROJECT', 'name', 'value'])
pivot = df.pivot_table(index=('HOLEID', 'PROJECT'), columns=('name'), values=['value'], aggfunc=lambda x: x)
print(pivot)
Output:
name a x HOLEID PROJECT 123 RH b y 234 RH c NaN
Of course you can always iterate over data and construct appropriate data structure yourself
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#3
Thanks Buran,

I now have the data but how do I then get it to display as per a normal table so I can write it to an SQL Table.

Current Output:

name a x
HOLEID PROJECT
123 RH b y
234 RH c NaN

Expected Output:

HOLEID PROJECT a x
123 RH b y
234 RH c NaN

Or is that just the way it is displayed and I can then write back to a new SQL Table using a different method?

Regards
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Need Help! Pandas EXCEL PIVOT psb3958 1 915 Nov-13-2022, 10:37 PM
Last Post: deanhystad
  group by create pivot table python dawid294 1 1,258 Jun-22-2022, 06:13 PM
Last Post: Larz60+
  How to pivot a dat UGuntupalli 0 1,855 Oct-17-2019, 11:13 PM
Last Post: UGuntupalli
  pivot error shyamdba 1 2,419 Feb-02-2018, 11:12 PM
Last Post: klllmmm
  Should it be pivot or unstack for this sample shyamdba 0 2,084 Feb-02-2018, 05:50 PM
Last Post: shyamdba

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020