Change the order of the columns using mapping rules

0

Hi! I have two tables with the same columns but in different order. Let's say Table 1:

IDCustomerDate
1John2020-01-01

Table 2:

IDDateCustomer
12020-01-01John

For both of them I want to generate a CSV file in S3 with the same structure: ID, Customer, Date

Can I use the mapping rules to map the columns of the second table in a different order?

1 Answer
0

So there are a few different ways you could do this depending on what tools/resources you want to use for the transformation.

If you're using AWS Glue or AWS Data Pipeline, you can use the Map class in the AWS Glue ETL library or the Mapping parameter in AWS Data Pipeline to specify the source and target column names in the order you want.

Here's an example of how you might do this in AWS Glue:

from awsglue.transforms import *

# Assume datasource0 is your DynamicFrame
applymapping1 = ApplyMapping.apply(frame = datasource0, mappings = [("ID", "long", "ID", "long"), ("Customer", "string", "Customer", "string"), ("Date", "string", "Date", "string")])

If you're using Python and AWS Lambda to generate the CSV file, you can use the pandas library to reorder the columns. Here's an example:

import pandas as pd
import boto3
from io import StringIO

def lambda_handler(event, context):
    # Assume you have a pandas DataFrame df
    df = pd.DataFrame({
        'ID': [1],
        'Date': ['2020-01-01'],
        'Customer': ['John']
    })

    # Reorder the columns
    df = df[['ID', 'Customer', 'Date']]

    # Convert the DataFrame to a CSV string
    csv_buffer = StringIO()
    df.to_csv(csv_buffer, index=False)

    # Upload the CSV file to S3
    s3_resource = boto3.resource('s3')
    s3_resource.Object('your-bucket-name', 'your-file-name.csv').put(Body=csv_buffer.getvalue())

Hope those are helpful examples!

profile picture
Zac Dan
answered 9 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions