5

I have a Shapefile with a lot of empty columns but many of them are empty. Using QGIS or ogr2ogr, how do I remove all attribute columns where everything is NULL?

2
  • How automatic should it be? Can you write the list of possibly empty columns by hand or should the procedure also resolve the schema automatically?
    – user30184
    Sep 11 '14 at 9:50
  • 1
    If possible, I would like to run something like: if all rows in a column is NULL, delete column.
    – maning
    Sep 11 '14 at 10:04
7

Follow this workflow:

  1. Open QGIS, load your Shapefile layer, and select it (i.e., make it active) in the ToC.

  2. Open the QGIS Python console.

  3. Copy the following code snippet and paste it in the QGIS Python console:

     from qgis.PyQt.QtCore import QDate, QDateTime
    
     lyr = iface.activeLayer()
     lstDelete = []
    
     for idx in lyr.dataProvider().attributeIndexes():
         uv = list(lyr.dataProvider().uniqueValues( idx ))
         if len( uv ) == 1:
             if uv[0] == NULL or uv[0] == QDateTime() or uv[0] == QDate() or uv[0] == '':
                 lstDelete.append( idx )
    
     if not lyr.dataProvider().capabilities() & QgsVectorDataProvider.DeleteFeatures:
         print "This layer provider does not support deleting attributes."
     else:
         lyr.dataProvider().deleteAttributes( lstDelete )
         lyr.updateFields() # Update the layer structure
    

This should delete all your empty fields for the active layer.

4
  • 1
    Thanks! I will get back to this when I am able to test.
    – maning
    Mar 5 '15 at 1:49
  • This does not work for me on QGIS 2.18.1 Windows and QGIS 2.18.0 Mac.
    – jfact0ry
    Jan 18 '17 at 18:25
  • @jfact0ry I've just edited the answer, please test it (with a backup layer) and let me know if it works in your environment. Jan 18 '17 at 19:43
  • Great! I'm pretty sure I tested the script I first posted and it worked. Some things can change in each QGIS release, this might have been one. Strangely, the uniqueValues method for empty fields returns a list whose only value is either NULL, QDate(), QDateTime() or '' (perhaps I'm missing another value). In my opinion, uniqueValues should be None in that case, not a list. Jan 18 '17 at 23:40
2

You can do it rather handy with ogr2ogr and ogrinfo. Solution is only partial because it does not check when it drops the column if it is empty or not. If you ask to drop a non-empty column it will be dropped.

Test with a shapefile which has 3 features. Column "empty" contains only nulls. This can be verified with ogrinfo. Here are two tests, first proves that there are nulls, the second proves that there are only nulls.

ogrinfo -so alter.shp -sql "select * from alter where empty is null"
INFO: Open of `alter.shp'
      using driver `ESRI Shapefile' successful.

Layer name: alter
Geometry: Point
Feature Count: 3

ogrinfo -so alter.shp -sql "select * from alter where empty is not null"
INFO: Open of `alter.shp'
      using driver `ESRI Shapefile' successful.

Layer name: alter
Geometry: Point
Feature Count: 0

What is not self-evident is that it is possible to update data with SQL by using the ogrinfo tool. Here are three commands: ogrinfo before update, ogrinfo command that drops a column and finally second ogrinfo command that proves that column "empty" really disappeared.

ogrinfo -so -al alter.shp
...
non_empty: String (1.0)
empty: String (1.0)

ogrinfo alter.shp -sql "alter table "alter" drop column "empty""
INFO: Open of `alter.shp'
      using driver `ESRI Shapefile' successful.

ogrinfo -al -so alter.shp
...
non_empty: String (1.0)
2
  • 1
    This only drops columns that I tell it to drop? How do I select all empty fields and drop them?
    – jfact0ry
    Jan 18 '17 at 18:22
  • One by one, command takes only one column name.
    – user30184
    Jan 18 '17 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.