Creating a column from header data

@SimonM
Are the files you are referencing on the DSS Design Node? Are they stored on some other server? Are you trying to load them into a particular DSS project as managed datasets?

Is this a one-time process? Or are you needing this to occur as part of a flow?

I don't have first hand experience, but I'm happy to try to recreate your use locally to figure it out (if I can)

@SimonM, I am honestly not sure how to do this in a non-hacky way using visual recipes - though I am not saying it can't be done (I just don't know).

In the meantime I have written some logic that can combine a bunch of files (of the format you specified) from a single folder data source and concatenate those in a single step.

Flow:

 Data inside sample_csvs:

 recipe output (processing +stacking the 2 csvs):

Here is the code inside the recipe that I used to actually perform the logic. If this looks like it may work for you, I'm happy to help you tweak the python code for your specific need.

# -*- coding: utf-8 -*-
import dataiku
import pandas as pd, numpy as np
from dataiku import pandasutils as pdu

#------------------Two helper functions ----------------------#
#-------------------------------------------------------------#

import re
import os
from datetime import datetime

def get_date(string, date_pattern):
""" define a regex pattern to extract first date match in a string"""
match = re.search(date_pattern, string)
date = datetime.strptime(match.group(1), '%m/%d/%Y').date()
return date


def process_file(file, header_rows, date_row, date_pattern):
"""
process a single csv. This function will skip `header_rows` rows and create a dataframe
from the remaining data. The date_pattern argument will be used to extract the first date
from date_row and insert it as a new column in the data. The modified dataframe will be
returned
"""
with open(file,'r') as f:
for i, line in enumerate(f):
if i == date_row-1:
date = get_date(line, date_pattern)

df = pd.read_csv(file, skiprows=header_rows)
df['date'] = date
return df

#-------------------------------------------------------------#
#-------------------------------------------------------------#

# Read recipe inputs
sample_csvs = dataiku.Folder("b5vBISdg")
folder_path = sample_csvs.get_path() # path to this data folder
files = sample_csvs.list_paths_in_partition() # file names in this folder
file_paths = [os.path.join(folder_path, file[1::]) for file in files] ## create paths to the actual csvs (will be a list of 2 here)
dfs = [] # empty list to store the results of each processed file

for path in file_paths:
dfs.append(process_file(path, header_rows=3, date_row=2, date_pattern='(\d{2}/\d{2}/\d{4})')) # Process each csv and append to list
stacked_dataset_df = pd.concat(dfs) # stack the processed csvs


# Write recipe outputs
stacked_dataset = dataiku.Dataset("stacked_dataset")
stacked_dataset.write_with_schema(stacked_dataset_df)