import gradio as gr
import pandas as pd
def create_datafeed(file, translation_files, language):
# Process the primary input file (CSV or Excel)
file_name = file.name.lower()
if file_name.endswith('.csv'):
df = pd.read_csv(file.name)
elif file_name.endswith(('.xls', '.xlsx', '.xlsm')):
try:
df = pd.read_excel(file.name)
except Exception as e:
return f"Error reading main file: {str(e)}", None
else:
df = pd.read_csv(file.name)
# Filter out rows based on conditions:
df = df[df["Title"].notna() & (df["Title"].astype(str).str.strip() != "")]
df = df[df["Published"].astype(str).str.upper() != "FALSE"]
df = df[df["Google Shopping / Custom Label 1"].astype(str).str.strip() != "Not List"]
# Define output headers
output_headers = [
"title",
"id",
"price",
"availability",
"condition",
"brand",
"color",
"description",
"google_product_category",
"gtin",
"identifier_exists",
"mpn",
"shipping_weight",
"link",
"image_link",
"unit_pricing_measure"
]
# Create output DataFrame with same index as df
out_df = pd.DataFrame(index=df.index, columns=output_headers)
# Fixed mappings for every row
out_df["availability"] = "In Stock"
out_df["condition"] = "New"
out_df["identifier_exists"] = "Yes"
# Map columns that are independent of translation:
out_df["id"] = df["Variant SKU"]
out_df["mpn"] = df["Variant SKU"]
out_df["price"] = df["Variant Price"].apply(lambda x: f"{x} USD")
out_df["brand"] = df["Vendor"]
out_df["color"] = df["Color (product.metafields.custom.color)"]
out_df["gtin"] = df["Variant Barcode"]
out_df["shipping_weight"] = df["Variant Grams"]
out_df["image_link"] = df["Image Src"]
out_df["google_product_category"] = df["Type"].apply(
lambda x: "2923" if str(x).strip() == "Contact Lenses" else "3021"
)
out_df["link"] = df["Handle"].apply(lambda x: f"https://trendysweet.com/products/{x}.html")
out_df["unit_pricing_measure"] = ""
# Process translation files: combine all into one DataFrame
translation_dfs = []
for t_file in translation_files:
t_name = t_file.name.lower()
try:
if t_name.endswith('.csv'):
t_df = pd.read_csv(t_file.name)
elif t_name.endswith(('.xls', '.xlsx', '.xlsm')):
t_df = pd.read_excel(t_file.name)
else:
t_df = pd.read_csv(t_file.name)
translation_dfs.append(t_df)
except Exception as e:
continue
if translation_dfs:
translation_df = pd.concat(translation_dfs, ignore_index=True)
# Ensure column names are stripped of whitespace
translation_df.columns = translation_df.columns.str.strip()
else:
translation_df = pd.DataFrame() # Empty DataFrame if no translation files provided
def get_translated_values(handle):
"""
Given a handle from the main file, look up in translation_df:
- Find the row where "Default content" equals the handle.
- Get the corresponding "Identification" value.
- Using that identification, look up the row with Field "title" for the title translation.
- Similarly, look up the row with Field "body_html" for the description translation.
Returns (title_translation, description_translation)
"""
if translation_df.empty:
return "", ""
# Normalize handle and Default content for comparison.
handle_norm = str(handle).strip().lower()
sub = translation_df[translation_df["Default content"].astype(str).str.strip().str.lower() == handle_norm]
if sub.empty:
return "", ""
identification = sub.iloc[0]["Identification"]
# Get title translation
title_rows = translation_df[
(translation_df["Identification"] == identification) &
(translation_df["Field"].astype(str).str.strip().str.lower() == "title")
]
title_translated = title_rows.iloc[0]["Translated content"] if not title_rows.empty else ""
# Get body_html translation (for description)
desc_rows = translation_df[
(translation_df["Identification"] == identification) &
(translation_df["Field"].astype(str).str.strip().str.lower() == "body_html")
]
desc_translated = desc_rows.iloc[0]["Translated content"] if not desc_rows.empty else ""
return title_translated, desc_translated
# Map "title" and "description" based on language selection.
if language == "English":
out_df["title"] = df["Title"]
out_df["description"] = df["SEO Description"]
else:
# For non-English, use the translation lookup based on the "Handle" column.
translated_titles = []
translated_descriptions = []
for handle in df["Handle"]:
title_translated, desc_translated = get_translated_values(handle)
translated_titles.append(title_translated)
translated_descriptions.append(desc_translated)
out_df["title"] = translated_titles
out_df["description"] = translated_descriptions
# Ensure that "title" and "description" are strings, then fill NaN and trim whitespace.
out_df["title"] = out_df["title"].fillna("").astype(str).str.strip()
out_df["description"] = out_df["description"].fillna("").astype(str).str.strip()
# Remove rows where "title" or "description" is blank.
out_df = out_df[(out_df["title"] != "") & (out_df["description"] != "")]
# Determine output filename based on language selection
output_file_name = "datafeed.csv"
if language == "Spanish":
output_file_name = "datafeed-spanish.csv"
elif language == "Arabic":
output_file_name = "datafeed-ar.csv"
elif language == "Deutsch":
output_file_name = "datafeed-de.csv"
elif language == "French":
output_file_name = "datafeed-french.csv"
elif language == "Italian":
output_file_name = "datafeed-it.csv"
elif language == "Portuguese":
output_file_name = "datafeed-portuguese.csv"
elif language == "Turkish":
output_file_name = "datafeed-tr.csv"
# Save output DataFrame to CSV with UTF-8 encoding
out_df.to_csv(output_file_name, index=False, encoding='utf-8')
return out_df, output_file_name
with gr.Blocks(title="Google Merchant Product Feed") as demo:
gr.Markdown("# Google Merchant Product Feed Generator")
with gr.Row():
file_input = gr.File(label="Upload Main Input File (CSV or Excel)")
translation_files = gr.File(label="Upload Translation File(s)", file_count="multiple")
language_selector = gr.Dropdown(
choices=["English", "Spanish", "Arabic", "Deutsch", "French", "Italian", "Portuguese", "Turkish"],
label="Select Language"
)
with gr.Row():
load_button = gr.Button("Generate Datafeed")
with gr.Row():
output_df = gr.DataFrame()
output_file_component = gr.File(label="Download Datafeed CSV")
# Updated hyperlink block with separate sections for Shipping Tools and Administration Tools.
gr.HTML(
"""
"""
)
load_button.click(
fn=create_datafeed,
inputs=[file_input, translation_files, language_selector],
outputs=[output_df, output_file_component]
)
demo.launch()