Spark Dataframe Update Column Value

We all know that UPDATING column value in a table is a pain in HIVE or SPARK SQL especially if you are dealing with non-ACID tables. However in Dataframe you can easily update column values. In the example below we will update State Name with State Abbreviation.

Original Dataframe:

scala> df_pres.show(45)
+——-+——————–+———-+——————-+————–+———-+———-+
|pres_id| pres_name| pres_dob| pres_bp| pres_bs| pres_in| pres_out|
+——-+——————–+———-+——————-+————–+———-+———-+
| 1| George Washington|1732-02-22|Westmoreland County| Virginia|1789-04-30|1797-03-04|
| 2| John Adams|1735-10-30| Braintree| Massachusetts|1797-03-04|1801-03-04|
| 3| Thomas Jefferson|1743-04-13| Shadwell| Virginia|1801-03-04|1809-03-04|
| 4| James Madison|1751-03-16| Port Conway| Virginia|1809-03-04|1817-03-04|
| 5| James Monroe|1758-04-28| Monroe Hall| Virginia|1817-03-04|1825-03-04|
| 6| John Quincy Adams|1767-07-11| Braintree| Massachusetts|1825-03-04|1829-03-04|
| 7| Andrew Jackson|1767-03-15| Waxhaws Region|North Carolina|1829-03-04|1837-03-04|
| 8| Martin Van Buren|1782-12-05| Kinderhook| New York|1837-03-04|1841-03-04|
| 9|William Henry Har…|1773-02-09|Charles City County| Virginia|1841-03-04|1841-04-04|
| 10| John Tyler|1790-03-29|Charles City County| Virginia|1841-04-04|1845-03-04|
| 11| James K. Polk|1795-11-02| Pineville|North Carolina|1845-03-04|1849-03-04|
| 12| Zachary Taylor|1784-11-24| Barboursville| Virginia|1849-03-04|1850-07-09|
| 13| Millard Fillmore|1800-01-07| Summerhill| New York|1850-07-09|1853-03-04|
| 14| Franklin Pierce|1804-11-23| Hillsborough| New Hampshire|1853-03-04|1857-03-04|
| 15| James Buchanan|1791-04-23| Cove Gap| Pennsylvania|1857-03-04|1861-03-04|
| 16| Abraham Lincoln|1809-02-12| Sinking spring| Kentucky|1861-03-04|1865-04-15|
| 17| Andrew Johnson|1808-12-29| Raleigh|North Carolina|1865-04-15|1869-03-04|
| 18| Ulysses S. Grant|1822-04-27| Point Pleasant| Ohio|1869-03-04|1877-03-04|
| 19| Rutherford B. Hayes|1822-10-04| Delaware| Ohio|1877-03-04|1881-03-04|
| 20| James A. Garfield|1831-11-19| Moreland Hills| Ohio|1881-03-04|1881-09-19|
| 21| Chester A. Arthur|1829-10-05| Fairfield| Vermont|1881-09-19|1885-03-04|
| 22| Grover Cleveland|1837-03-18| Caldwell| New Jersey|1885-03-04|1889-03-04|
| 23| Benjamin Harrison|1833-08-20| North Bend| Ohio|1889-03-04|1893-03-04|
| 24| Grover Cleveland|1837-03-18| Caldwell| New Jersey|1893-03-04|1897-03-04|
| 25| William McKinley|1843-01-29| Niles| Ohio|1897-03-04|1901-09-14|
| 26| Theodore Roosevelt|1858-10-27| Manhattan| New York|1901-09-14|1909-03-04|
| 27| William Howard Taft|1857-09-15| Cincinnati| Ohio|1909-03-04|1913-03-04|
| 28| Woodrow Wilson|1856-12-28| Staunton| Virginia|1913-03-04|1921-03-04|
| 29| Warren G. Harding|1865-11-02| Blooming Grove| Ohio|1921-03-04|1923-08-02|
| 30| Calvin Coolidge|1872-07-04| Plymouth| Vermont|1923-08-02|1929-03-04|
| 31| Herbert Hoover|1874-08-10| West Branch| Iowa|1929-03-04|1933-03-04|
| 32|Franklin D. Roose…|1882-01-30| Hyde Park| New York|1933-03-04|1945-04-12|
| 33| Harry S. Truman|1884-05-08| Lamar| Missouri|1945-04-12|1953-01-20|
| 34|Dwight D. Eisenhower|1890-10-14| Denison| Texas|1953-01-20|1961-01-20|
| 35| John F. Kennedy|1917-05-29| Brookline| Massachusetts|1961-01-20|1963-11-22|
| 36| Lyndon B. Johnson|1908-08-27| Stonewall| Texas|1963-11-22|1969-01-20|
| 37| Richard M. Nixon|1913-01-09| Yorba Linda| California|1969-01-20|1974-08-09|
| 38| Gerald R. Ford|1913-07-14| Omaha| Nebraska|1974-08-09|1977-01-20|
| 39| Jimmy Carter|1924-10-01| Plains| Georgia|1977-01-20|1981-01-20|
| 40| Ronald Reagan|1911-02-06| Tampico| Illinois|1981-01-20|1989-01-20|
| 41| George H. W. Bush|1924-06-12| Milton| Massachusetts|1989-01-20|1993-01-20|
| 42| Bill Clinton|1946-08-19| Hope| Arkansas|1993-01-20|2001-01-20|
| 43| George W. Bush|1946-07-06| New Haven| Connecticut|2001-01-20|2009-01-20|
| 44| Barack Obama|1961-08-04| Honolulu| Hawaii|2009-01-20|2017-01-20|
| 45| Donald Trump|1946-06-14| Queens| New York|2017-01-20| null|
+——-+——————–+———-+——————-+————–+———-+———-+

Look-Up Dataframe

scala> df_states.show(50)
+——–+————–+———-+————–+—————+—————-+
|state_id| state_name|state_abbr| state_capital|state_larg_city|state_population|
+——–+————–+———-+————–+—————+—————-+
| 1| Alabama| AL| Montgomery| Birmingham| 4874747|
| 2| Alaska| AK| Juneau| Anchorage| 739795|
| 3| Arizona| AZ| Phoenix| Phoenix| 7016270|
| 4| Arkansas| AR| Little Rock| Little Rock| 3004279|
| 5| California| CA| Sacramento| Los Angeles| 39536653|
| 6| Colorado| CO| Denver| Denver| 5607154|
| 7| Connecticut| CT| Hartford| Bridgeport| 3588184|
| 8| Delaware| DE| Dover| Wilmington| 961939|
| 9| Florida| FL| Tallahassee| Jacksonville| 20984400|
| 10| Georgia| GA| Atlanta| Atlanta| 10429379|
| 11| Hawaii| HI| Honolulu| Honolulu| 1427538|
| 12| Idaho| ID| Boise| Boise| 1716943|
| 13| Illinois| IL| Springfield| Chicago| 12802023|
| 14| Indiana| IN| Indianapolis| Indianapolis| 6666818|
| 15| Iowa| IA| Des Moines| Des Moines| 3145711|
| 16| Kansas| KS| Topeka| Wichita| 2913123|
| 17| Kentucky| KY| Frankfort| Louisville| 4454189|
| 18| Louisiana| LA| Baton Rouge| New Orleans| 4684333|
| 19| Maine| ME| Augusta| Portland| 1335907|
| 20| Maryland| MD| Annapolis| Baltimore| 6052177|
| 21| Massachusetts| MA| Boston| Boston| 6859819|
| 22| Michigan| MI| Lansing| Detroit| 9962311|
| 23| Minnesota| MN| St. Paul| Minneapolis| 5576606|
| 24| Mississippi| MS| Jackson| Jackson| 2984100|
| 25| Missouri| MO|Jefferson City| Kansas City| 6113532|
| 26| Montana| MT| Helena| Billings| 1050493|
| 27| Nebraska| NE| Lincoln| Omaha| 1920076|
| 28| Nevada| NV| Carson City| Las Vegas| 2998039|
| 29| New Hampshire| NH| Concord| Manchester| 1342795|
| 30| New Jersey| NJ| Trenton| Newark| 9005644|
| 31| New Mexico| NM| Santa Fe| Albuquerque| 2088070|
| 32| New York| NY| Albany| New York| 19849399|
| 33|North Carolina| NC| Raleigh| Charlotte| 10273419|
| 34| North Dakota| ND| Bismarck| Fargo| 755393|
| 35| Ohio| OH| Columbus| Columbus| 11658609|
| 36| Oklahoma| OK| Oklahoma City| Oklahoma City| 3930864|
| 37| Oregon| OR| Salem| Portland| 4142776|
| 38| Pennsylvania| PA| Harrisburg| Philadelphia| 12805537|
| 39| Rhode Island| RI| Providence| Providence| 1059639|
| 40|South Carolina| SC| Columbia| Charleston| 5024369|
| 41| South Dakota| SD| Pierre| Sioux Falls| 869666|
| 42| Tennessee| TN| Nashville| Nashville| 6715984|
| 43| Texas| TX| Austin| Houston| 28304596|
| 44| Utah| UT|Salt Lake City| Salt Lake City| 3101833|
| 45| Vermont| VT| Montpelier| Burlington| 623657|
| 46| Virginia| VA| Richmond| Virginia Beach| 8470020|
| 47| Washington| WA| Olympia| Seattle| 7405743|
| 48| West Virginia| WV| Charleston| Charleston| 1815857|
| 49| Wisconsin| WI| Madison| Milwaukee| 5795483|
| 50| Wyoming| WY| Cheyenne| Cheyenne| 579315|
+——–+————–+———-+————–+—————+—————-+

Now we will Join Original Dataframe with Lookup Dataframe and if State Name exists then we will update it with State Abbr. Let’s see it below:

val upd_df_pres = df_pres.as(“tb1”).join(df_states.as(“tb2″),$”tb1.pres_bs” === $”tb2.state_name”,”leftouter”).withColumn(“pres_bs”,when ($”tb1.pres_bs” === $”tb2.state_name”,$”tb2.state_abbr” ).otherwise($”tb1.pres_bs”)).select($”pres_id”,$”pres_name”,$”pres_dob”,$”pres_bp”,$”pres_bs”,$”pres_in”,$”pres_out”)

scala> upd_df_pres.show(45)
+——-+——————–+———-+——————-+——-+———-+———-+
|pres_id| pres_name| pres_dob| pres_bp|pres_bs| pres_in| pres_out|
+——-+——————–+———-+——————-+——-+———-+———-+
| 1| George Washington|1732-02-22|Westmoreland County| VA|1789-04-30|1797-03-04|
| 2| John Adams|1735-10-30| Braintree| MA|1797-03-04|1801-03-04|
| 3| Thomas Jefferson|1743-04-13| Shadwell| VA|1801-03-04|1809-03-04|
| 4| James Madison|1751-03-16| Port Conway| VA|1809-03-04|1817-03-04|
| 5| James Monroe|1758-04-28| Monroe Hall| VA|1817-03-04|1825-03-04|
| 6| John Quincy Adams|1767-07-11| Braintree| MA|1825-03-04|1829-03-04|
| 7| Andrew Jackson|1767-03-15| Waxhaws Region| NC|1829-03-04|1837-03-04|
| 8| Martin Van Buren|1782-12-05| Kinderhook| NY|1837-03-04|1841-03-04|
| 9|William Henry Har…|1773-02-09|Charles City County| VA|1841-03-04|1841-04-04|
| 10| John Tyler|1790-03-29|Charles City County| VA|1841-04-04|1845-03-04|
| 11| James K. Polk|1795-11-02| Pineville| NC|1845-03-04|1849-03-04|
| 12| Zachary Taylor|1784-11-24| Barboursville| VA|1849-03-04|1850-07-09|
| 13| Millard Fillmore|1800-01-07| Summerhill| NY|1850-07-09|1853-03-04|
| 14| Franklin Pierce|1804-11-23| Hillsborough| NH|1853-03-04|1857-03-04|
| 15| James Buchanan|1791-04-23| Cove Gap| PA|1857-03-04|1861-03-04|
| 16| Abraham Lincoln|1809-02-12| Sinking spring| KY|1861-03-04|1865-04-15|
| 17| Andrew Johnson|1808-12-29| Raleigh| NC|1865-04-15|1869-03-04|
| 18| Ulysses S. Grant|1822-04-27| Point Pleasant| OH|1869-03-04|1877-03-04|
| 19| Rutherford B. Hayes|1822-10-04| Delaware| OH|1877-03-04|1881-03-04|
| 20| James A. Garfield|1831-11-19| Moreland Hills| OH|1881-03-04|1881-09-19|
| 21| Chester A. Arthur|1829-10-05| Fairfield| VT|1881-09-19|1885-03-04|
| 22| Grover Cleveland|1837-03-18| Caldwell| NJ|1885-03-04|1889-03-04|
| 23| Benjamin Harrison|1833-08-20| North Bend| OH|1889-03-04|1893-03-04|
| 24| Grover Cleveland|1837-03-18| Caldwell| NJ|1893-03-04|1897-03-04|
| 25| William McKinley|1843-01-29| Niles| OH|1897-03-04|1901-09-14|
| 26| Theodore Roosevelt|1858-10-27| Manhattan| NY|1901-09-14|1909-03-04|
| 27| William Howard Taft|1857-09-15| Cincinnati| OH|1909-03-04|1913-03-04|
| 28| Woodrow Wilson|1856-12-28| Staunton| VA|1913-03-04|1921-03-04|
| 29| Warren G. Harding|1865-11-02| Blooming Grove| OH|1921-03-04|1923-08-02|
| 30| Calvin Coolidge|1872-07-04| Plymouth| VT|1923-08-02|1929-03-04|
| 31| Herbert Hoover|1874-08-10| West Branch| IA|1929-03-04|1933-03-04|
| 32|Franklin D. Roose…|1882-01-30| Hyde Park| NY|1933-03-04|1945-04-12|
| 33| Harry S. Truman|1884-05-08| Lamar| MO|1945-04-12|1953-01-20|
| 34|Dwight D. Eisenhower|1890-10-14| Denison| TX|1953-01-20|1961-01-20|
| 35| John F. Kennedy|1917-05-29| Brookline| MA|1961-01-20|1963-11-22|
| 36| Lyndon B. Johnson|1908-08-27| Stonewall| TX|1963-11-22|1969-01-20|
| 37| Richard M. Nixon|1913-01-09| Yorba Linda| CA|1969-01-20|1974-08-09|
| 38| Gerald R. Ford|1913-07-14| Omaha| NE|1974-08-09|1977-01-20|
| 39| Jimmy Carter|1924-10-01| Plains| GA|1977-01-20|1981-01-20|
| 40| Ronald Reagan|1911-02-06| Tampico| IL|1981-01-20|1989-01-20|
| 41| George H. W. Bush|1924-06-12| Milton| MA|1989-01-20|1993-01-20|
| 42| Bill Clinton|1946-08-19| Hope| AR|1993-01-20|2001-01-20|
| 43| George W. Bush|1946-07-06| New Haven| CT|2001-01-20|2009-01-20|
| 44| Barack Obama|1961-08-04| Honolulu| HI|2009-01-20|2017-01-20|
| 45| Donald Trump|1946-06-14| Queens| NY|2017-01-20| null|
+——-+——————–+———-+——————-+——-+———-+———-+

You can see State Name is updated with short name like Virginia changed to VA.

Leave a Reply

Your email address will not be published. Required fields are marked *