Skip to main content
Question

Removing empty spaces and formatting data from rows to columns

gisgeek
Contributor
Forum|alt.badge.img+9

I have data in the the following format I want to get from rows to columns so that the rows are displayed as columns like this. Hoping someone can help me with this.

FID Loc_name Status

35 NZL.PointAddress M

NZL.PointAddress FID 35 Loc_name NZL.PointAddress Status M Score 100 Match_type A Match_addr 40 Maui St, Pukete, Hamilton, 3200 Addr_type PointAddress Type PlaceName Place_addr Phone URL Rank AddBldg AddNum 40

    3 replies

    takashi
    Evangelist
    • takashiEvangelist
    • October 7, 2016

    Hi @gisgeek, summary of a possible way is:

    1. Translate the data to a set of features each of which has an attribute representing a pair of a desired column name and corresponding value. e.g. 1st feature has an attribute called "FID" which stores "35"; 2nd feature has an attribute called "Loc_name" which stores "NZL.PointAddress"; and so on.
    2. Aggregate all the features into a single feature, with the Aggregator (Accumulation Mode: Merge Incoming Attributes).
    3. Expose the column names ("FID", "Loc_name", etc.), with the AttributeExposer.

    The concrete method for the step 1 may vary depending on how the source data would be supplied.

    M
    Forum|alt.badge.img+2
    • markatsafe
    • October 11, 2016
    @gisgeek I think if we'll be able to offer more help if you can define the structure of your data a little more clearly. How do you identify a new record? It looks like AddBldg is an attribute name, so is that value <null>? Are the number of attributes predicatable?

     

    M
    Forum|alt.badge.img+2
    • markatsafe
    • October 11, 2016

    Assuming that the data repeats in a consistent way, you can use a combination of the ModuloCounter and the AttributeCreator with the Enable Adjacent Feature Attributes option to create the name/value pairs and then aggregate those to create a single feature. See the attached workspacerows-to-columns.fmwt

    Reply


    Helpful Members This Week

    Community Stats

    31,479
    Posts
    119,341
    Replies
    39,098
    Members

    Latest FME

    Terms & ConditionsCookie settingsAccessibility statement

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings
    A product of
    Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.