What's the difference between these two 1.4f 50mm prime lenses?

You've already identified some of the most striking and significant differences:

• Size/weight
• Price

Not so obvious is that the Nikkor AF-S 50mm f/1.4G is a 2008 design with nine optical elements in six groups based on far older previous lenses¹ while the Sigma 50/1.4 ART is a 2014 design with thirteen optical elements in eight groups. Nikon's more recent 2018 Z-mount Nikkor Z 50mm f/1.8 S with twelve elements in nine groups is more similar in design philosophy to the Sigma 50/1.4 ART.

• Number of optical elements/groups
• Age of design
• Design decisions about what is most important to the lens' performance characteristics

Not only has the state of the art (no pun intended) moved forward during this time period, but what potential buyers want and expect in a lens has also seemed to shift during that relatively short time interval.

What is the difference and for what purpose would anyone buy the more expensive heavier lens?

For most of those who are willing to pay much more for a heavier lens like the Sigma 50mm f/1.4 DG HSM ART, it's so they can have the lens that is one of the best at taking sharp photos of the corners of flat test charts at close distances.

There are times and places, such as flat document archiving or art reproduction, where this is a legitimate reason but for the most part those who buy such lenses do so because they think "sharpest (on the edges and in the corners as well as in the center)" always means "best" for whatever purpose to which they may be intending to put the lens to use. Such is not always the case.

Portraitists, for example, have typically been more concerned with the characteristics of the out of focus areas in the background behind their subjects than with the lens' ability with regard to absolute "sharpness" at the edges of the frame when those edges are generally well outside the depth of field anyway. The same design considerations that give the best flat field performance making those edges and corners of flat test charts sharper also can make out of focus areas look "busy" or "harsh" compared to lenses with less corrected or even uncorrected field curvature that can make the out of focus areas in the background "smooth" and "creamy".

Then there are use cases where compact size and light weight may be more important than ultimate optical performance, such as street photography or landscape photography done at locations difficult to access without hiking for miles or only after climbing a mountain.

For more here at Photography SE about how different lens designs can lead to different characteristics when using them, please see:
What is the advantage of a lens with a curved focal plane?
Why do prime lenses have multiple lens elements?
Why is the Tamron 90mm 2.8 marketed as Macro and not as a "portrait" lens?
In photo taken with a prime lens, what is the cause of the "zoomed" bokeh appearance?
Is Canon 50mm f/1.2 with Canon EOS 80D suitable for portraits, landscapes, travel/nightlife photography?
What are some tips for using a macro lens for non-macro photography?

For more about the history of lens design, please see several excellent entries at Roger Cicala's blog. Roger is the founder and Chief Lens Guru at lensrentals.com:

Lens Genealogy Part 1
Lens Genealogy – Part 2
Fun with Field of Focus Part 1
Fun with Field of Focus II: Copy-to-Copy Variation and Lens Testing

As to whether minor but measurable differences in "sharpness" will make a significant impact on real world photos, please see:
What are the differences between these lenses and how will they affect a beginner?
What should I expect from Canon EF 24-70mm f/2.8L II USM?
Will I see enough improvement moving from EF-S to "L" lenses to warrant the cost?
Will a lens upgrade from the kit lens give me better colors on my backpacking travels?
is there a real difference between "digital" and "film" lenses?
Is replacing all my Fujifilm gear with this Canon zoom lens an upgrade?

For when those differences might only be noticeable, please see:
What is pixel peeping and why do some people say should I avoid it?
Why do some people say to use 0.007 mm (approximate pixel size) for the CoC on a Canon 5DM2?

And, finally, for why most blurry pictures are blurry (hint: it usually isn't the lens' fault) and the best way to improve your images:
How do I diagnose the source of focus problem in a camera?
Focus problem vs. motion blur vs. camera shake - how to tell the difference?

How to improve image sharpness on Canon 700D?

_{¹ Any 50mm lens with six groups is more likely than not to be based on the 1920 modifications by Taylor, Taylor, and Hobson to the classic double Gauss design pioneered in 1817 by Carl Friedrich Gauss for use in telescopes before photography even existed.}

Here is a review and measurement of the Nikon 50mm f/1.4G: https://www.lenstip.com/162.4-Lens_review-Nikon_Nikkor_AF-S_50_mm_f_1.4G_Image_resolution.html As you see, the maximum resolution at any aperture is about 42 lp/mm at the center and 33 lp/mm at the edge at the same aperture.

Here is a review and test of the Sigma 50 mm f/1.4 DG HSM Art: https://www.lenstip.com/400.4-Lens_review-Sigma_A_50_mm_f_1.4_DG_HSM_Image_resolution.html Here, the maximum resolution is 47 lp/mm at the center and 39 lp/mm at the edge.

The Sigma is sharper in every respect than the Nikon. At US$950, you would certainly expect so.

I am quite confident that the difference is noticable, albeit slightly, in 100% crops.

In addition, looking through the remainder of each review, distortion is lower, coma and astigmatism are lower and, in every measure, the Sigma is at least equal and usually superior, to the Nikon.

for what purpose would anyone buy the more expensive heavier lens?

If cost is little or no object and you must have the best available, the you'll get the Sigma. If practicality and price are important, then you'll want the Nikon.

These are intended with different use cases in mind.

Any lens will display a lot of both character and flaws when actually used at apertures as large as f/1.4.

Classic 50mm primes, like the Nikon design, go for making the remaining flaws visually pleasant, and assume that you want to take advantage of the large aperture to work with low depth of field - so the design seeks the best compromise to make both in focus and out of focus areas look great. Also, they seek to do so at reasonable cost and weight.

Some modern designs, like the Sigma in question, go for optimizing the in focus area (at the cost of everything else) even at wide open aperture, more under the assumption that you take advantage of the large aperture for working with less light than you would normally need. One could snarkily say the manufacturer also looks good in benchmarks that are biased towards in-focus performance. Also, such designs put more emphasis on dealing with extreme highlights cleanly (eg when photographing stars) - there are Nikon primes (the original NOCT lenses), far predating the Sigma, built for similar uses, and they are also very expensive.

TL;DR: They are for completely different purposes, any absolute statement of "this or this is better" is disingenous.