Bottom Page

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
Quote
#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
Quote
#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
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  How to pivot a dat UGuntupalli 0 170 Oct-17-2019, 11:13 PM
Last Post: UGuntupalli
  pivot error shyamdba 1 723 Feb-02-2018, 11:12 PM
Last Post: klllmmm
  Should it be pivot or unstack for this sample shyamdba 0 647 Feb-02-2018, 05:50 PM
Last Post: shyamdba

Forum Jump:


Users browsing this thread: 1 Guest(s)