python
python

read a csv file to DF

Title infm_query
cat_1 {"categoryID":9,"params":{"110000":"MzI5MjAy","110001":"MzMxMDM1","188":"eyJmcm9tIjoyMDMwMywidG8iOjIwMzAzfQ=="}}
cat_2 {"categoryID":9,"params":{"110000":"WzMyOTIwMl0=","110001":"WzMzMTAzNV0=","188":"eyJmcm9tIjoyMDMwMywidG8iOjIwMzAzfQ=="}}
cat_3 {"categoryID":9,"params":{"110000":"MzI5MjAy","110001":"NDM4NzM0","110001":"MzMxMDM1","188":"eyJmcm9tIjoyMDMwMywidG8iOjIwMzAzfQ=="}}
cat_4 {"categoryID":9,"params":{"110000":"MzI5MjAy","110001":"WzMzMTAzNV0=","188":"eyJmcm9tIjoyMDMwMywidG8iOjIwMzAzfQ=="}}
cat_5 {"categoryID":9,"params":{"110000":"MzI5MjAy","110001":"MzMxMDM4","110001":"MzMxMDM1","188":"eyJmcm9tIjoyMDMwMywidG8iOjIwMzAzfQ=="}}
cat_6 {"categoryID":9,"params":{"110000":"MzI5MjAy","110001":"NDc2Mjg0","110001":"MzMxMDM1","188":"eyJmcm9tIjoyMDMwMywidG8iOjIwMzAzfQ=="}}

transform it to DF where

&cd=1&sgtx=06a6f2f79c9ce18b0822053ebd2cca4354e7479e& - fixed
query=%& - fixed

params[110000]=329315 - is a base64 decode "110000":"MzI5MjAy",
Title,Query,URI,infm_query
'cat_1','cat_1',query=%&categoryId=9&cd=1&sgtx=06a6f2f79c9ce18b0822053ebd2cca4354e7479e&params[110000]=329315&params[110001]=329377,"{""categoryId"": ""9"", ""params"": {""110000"": ""329315"", ""110001"": ""329377""}}"







don't forget - in URI formula use

&cd=1&sgtx=06a6f2f79c9ce18b0822053ebd2cca4354e7479e& - constant
query=%& - - constant



in result URI make format look like

&params[110000]=329315&params[110001]=329377



--------------------
import pandas as pd
import base64
from urllib.parse import urlencode

# Read the CSV file into a DataFrame
#df = pd.read_csv('your_file.csv')
df = pd.read_clipboard(sep='\t')



# Define the columns to be transformed
columns_to_transform = ['Title', 'infm_query']

 
 
# Define the columns to be transformed
columns_to_transform = ['Title', 'infm_query']

# Constants for the URI formula
CD_VALUE = 1
SGTX_VALUE = '06a6f2f79c9ce18b0822053ebd2cca4354e7479e'




 

# Function to transform the infm_query column
def transform_cat(row):
    data = eval(row['infm_query'])
    params = data['params']
    transformed_params = {k: base64.b64decode(v).decode() for k, v in params.items()}
    
    # Extract categoryID from infm_query
    category_id = data['categoryID']
    
    # Include categoryID in the query_params
    query_params = {'categoryId': category_id }
    #query_params.update(transformed_params)
    
    # Format the transformed parameters
    #formatted_params = {f'params[{k}]': v for k, v in transformed_params.items()}
    #query_params.update(formatted_params)
    
    query = urlencode(query_params, doseq=True)
    return query

# Add the transformed columns to the DataFrame
 
 


# Function to transform the infm_query column
def transform_infm_query(row):
    data = eval(row['infm_query'])
    params = data['params']
    transformed_params = {k: base64.b64decode(v).decode() for k, v in params.items()}
    query_params = {'categoryId': data['categoryID']}
    query_params.update(transformed_params)
    
    # Format the transformed parameters
    formatted_params = {f'params[{k}]': v for k, v in transformed_params.items()}
    query_params.update(formatted_params)
    
    query = urlencode(query_params, doseq=True)
    return query

# Add the transformed columns to the DataFrame
def construct_query_string(row):
    query_params = f"categoryId={row['categoryID']}&cd=1&sgtx=06a6f2f79c9ce18b0822053ebd2cca4354e7479e"
    
    for param in row['params']:
        decoded_value = base64.b64decode(param['value']).decode('utf-8')
        
        # Check if 'id' key exists before using it
        if 'id' in param:
            query_params += f"&params[{param['id']}]={decoded_value}"
        else:
            # Handle the case where 'id' key is missing
            query_params += f"&params[]={decoded_value}"  # Use an empty key for missing 'id'
    
    return f"query=%{query_params}" 
 
df['categoryID'] = df.apply(transform_cat, axis=1)
 

# Add the transformed columns to the DataFrame
#df['Query'] = df.apply(transform_infm_query, axis=1)
#df['URI'] = 'query=%&cd=' + str(CD_VALUE) + '&sgtx=' + SGTX_VALUE + df['Query']


df['URI']  = df.apply(construct_query_string, axis=1)
 
df['Query']  = df['Title']
# Print the final DataFrame
df['infm_query'] = df['infm_query'].apply(lambda x: str(eval(x)))
(df[["Title" ,   "Query",  "URI" , "infm_query"]]).to_csv('untitled12.txt', index=False)
display(df[['Title', 'Query', 'URI', 'infm_query']])

print(df.iloc[0,3])