0

Hi I am looking for a fastest solution to process loads of csv files.

Situation: I have multiple csv files in a single folder with different headers

I have already done pre-processing on them to remove garbage lines on top so all has a Standard Header.

I would like to MERGE GROUP OF CSV FILES with EXACT SAME HEARERS to a New folder

Single Folder:
    Tree 
    ├── 161598827330618_data_aa.csv 
    ├── ..............  
    ├── ............... 
    ├── ................ 
    ├── 161598852706227_data_bh.csv 
Note: Filenames are Random with no pattern*

Sample File-1.csv

School Name,Project Description,Construction Award,Project type,Building ID,Building Address,City,Postcode
George HS - QUEENS,New,76850000,CAP,Q298,50-51 98TH STREET,Queens,11368 
MARBLE HILL INTERNATIONAL HS -,EXT MASONRY/FLOOD/PARAPETS/ROOFS,10490000,CIP,X475,99 TERRACE VIEW AVENUE,Bronx,10463
NEW DORP HS - STATEN ISLAND,PARTIAL ACCESSIBILITY,488000,CIP,R435,465 NEW DORP LANE,Staten Island,10306

Sample File-2.csv

School Name,Project Description,Construction Award,Project type,Building ID,Building Address,City,Postcode
EAST SIDE COMMUNITY SCHOOL,FIFTH FLOOR CEILING REPLACEMENT,150000,CIP,M060,420 EAST 12 STREET,Manhattan,10009
RICHMOND HILL HS - QUEENS,STEEL DETERIORATED COLUMS & COLUMN,1064400,CIP,Q475,89-30 114 STREET,Queens,11418
SUCCESS ACADEMY CHARTER SCHOOL,INTERIOR STAIRS,2045000,CIP,M099,410 EAST 100 STREET,Manhattan,10029

Sample File-3.csv

Reporting Period,Project Number,City,County,Zip Code,Sector,Solicitation,Electric Utility
02/28/2021,2453,Youngstown,,14174,Non-Residential,ARRA Projects,National Grid
02/28/2021,218852,Queens,Queens,11356,Residential,PON 2112,Consolidated Edison
02/28/2021,220037,Warwick,Orange,10990,Residential,PON 2112,Orange and Rockland Utilities
02/28/2021,2011-230103-SLPR,Center Moriches,Suffolk,11934,Residential,Solar ARRA Funding,Long Island Power Authority

Sample File-4.csv

Reporting Period,Project Number,City,County,Zip Code,Sector,Solicitation,Electric Utility
02/28/2021,2453,Youngstown,,14174,Non-Residential,ARRA Projects,National Grid
02/28/2021,218852,Queens,Queens,11356,Residential,PON 2112,Consolidated Edison
02/28/2021,220037,Warwick,Orange,10990,Residential,PON 2112,Orange and Rockland Utilities
02/28/2021,2011-230103-SLPR,Center Moriches,Suffolk,11934,Residential,Solar ARRA Funding,Long Island Power Authority

Sample File-5.csv

OBJECTID,Borough,PSSite,ParkName,ParkZone,PSStatus,GlobalID,CreatedDate,UpdatedDate
283721,Brooklyn,Street,,,Populated,C90AAD08-D99E-4759-A64C-219D6143BFB3,07-08-15 13:10,12/20/2019 04:34:58 PM
7669836,Queens,Park,Astoria Park,Q004-ZN02,Empty,AB55A658-8276-4734-A698-5FFCAE96578E,08/13/2020 01:18:00 PM,08/20/2020 06:15:32 PM
7123408,Brooklyn,Park,Asser Levy Park,,Populated,B32D93C9-5958-4129-A87A-FA7C9A5A4E87,01-09-20 13:15,01-09-20 13:17

Sample File-6.csv

OBJECTID,Borough,PSSite,ParkName,ParkZone,PSStatus,GlobalID,CreatedDate,UpdatedDate
6036681,Manhattan,Park,Riverside Park,,Populated,6A3E747D-CD5E-43EB-9789-67DB2064E878,04-11-18 11:11,08-06-20 21:21
7170578,Bronx,Park,Garden Of Eden,,Populated,B1E8B660-4B65-437F-B61F-06B1B71A4E1C,01/28/2020 03:18:00 PM,01/28/2020 03:19:26 PM
740416,Bronx,Park,Mullaly Park,X034-ZN02,Populated,E8F51E3B-CC6F-46A3-AF17-02B6BE8DCC57,08/26/2015 04:34:00 PM,01/30/2020 04:10:41 PM
5004669,Queens,Street,,,Populated,20157769-88EC-4867-9F50-852EF4814BF0,11-02-16 16:56,08-03-20 13:12:00 AM

Sample File-7.csv

Indicator,Group,State,Subgroup,Phase,Time Period,Time Period Label,Value,Low CI,High CI,Confidence Interval
Private Health Insurance Coverage,National Estimate,United States,United States,1,1,Apr 23 - May 5,75.4,74.7,76.2,74.7 - 76.2
Public Health Insurance Coverage,By Age,United States,18 - 24 years,1,1,Apr 23 - May 5,19.5,15.4,24.3,15.4 - 24.3
Uninsured at the Time of Interview,By Gender,United States,Female,1,1,Apr 23 - May 5,11,10.3,11.7,10.3 - 11.7

Sample File-8.csv

Year, dtmSurveyDate, ColonyID, strAOUCode, Type, strPhotoInterpreters, strColonyName, strCounty, strState, strCountry
2014,03-Jun-14,219-001,COMU,Image Check - No Birds,Kirsten Bixler,"""Tillamook Head Rocks"" (Eastern Rocks)",Clatsop County,Oregon,United States
2014,03-Jun-14,219-002,COMU,Image Check - No Birds,Kirsten Bixler,"""Tillamook Head Rocks"" (Northern Rock)",Clatsop County,Oregon,United States
2014,03-Jun-14,219-003,COMU,Shapefile-RawCount,Kirsten Bixler,"""Tillamook Head Rocks"" (Southwestern Rocks)",Clatsop County,Oregon,United States
2014,03-Jun-14,219-005,COMU,Shapefile,Shawn W. Stephensen,Tillamook Rock,Clatsop County,Oregon,United States

Desired Result:

Sample File-1.csv }
Sample File-2.csv } Header check > Same Header found> MERGE > GROUP-1.csv

Sample File-3.csv }
Sample File-4.csv } Header check > Same Header found> MERGER > GROUP-2.csv

Sample File-5.csv }
Sample File-6.csv } Header check > Same Header found > MERGER > GROUP-3.csv

Sample File-7.csv } Header check > No Similar Headers > NOTHING> GROUP-5.csv
Sample File-8.csv } Header check > No Similar Headers > NOTHING> GROUP-6.csv

Preferable Solution: Bash Script and Linux Commands Solutions Tried:

#!/bin/bash
awk '
  FNR==1{
    if (!($0 in h)||file!=h[$0]){close(file)}
    if (!($0 in h)){file=h[$0]=i++}
    else{file=h[$0];next}
  }
  {print >> (file)}
' ./*.csv

https://unix.stackexchange.com/a/602291/459978 The above method work but I am not sure if it can handle 1000s of files for processing and categorizing. I need Group*.csv formats to be created in an different folder.

Shortest Time of Completion is important https://stackoverflow.com/a/51921621/3088275

Looking for an Op code with Awk or Sed or Linux commands for bash script which ever is fastest to achieve the desired output

9
  • If the solution fromthe referenced question works, why don't you simply try if it works with a large amount of data and fulfils your time requirements? If you want to create the output in a different folder, cd to the output folder and specify the input files like ../input/*.csv or /path/to/input/*.csv
    – Bodo
    Mar 17, 2021 at 16:00
  • want an improvement of the Op code or new code with speed improvisation which is reliable for realtime
    – BetaCoder
    Mar 17, 2021 at 16:08
  • Your answer doesn't make your requirements clear. Please explain in your question why you think that you need speed improvement when you apparently did not even check if the existing code runs too slow. Please explain what exactly you mean with "reliable for realtime". You can edit your question to add information.
    – Bodo
    Mar 17, 2021 at 16:16
  • @Bodo Requirement is mentioned clearly in 'Desired output' Heading, the code creates empty files with no extension, i really dont understand the exact functioning of code and hence need an explanation, alternative code preferable which is human readable/understandable, as per the speed looking for benchmarks like this -stackoverflow.com/a/51921621/3088275, if you can improvise the code let me know
    – BetaCoder
    Mar 17, 2021 at 16:20
  • 1
    Then please edit your question and show or explain the actual output you get and the expected output you want instead. I also suggest to remove everything related to speed because it distracts from your real problem. If you later find out that the script might run too slow then write a separate question where you clearly state your speed requirements.
    – Bodo
    Mar 17, 2021 at 16:25

1 Answer 1

0

Is it guaranteed that the duplicate headers are found only in consecutive files? With this answer I wanted to handle if that is not the case:

#!/bin/bash

# Declare header_list[] to be an associative array
declare -A header_list

# Read the first line from every *.csv file in $1
# Each filename is added to the appropriate entry in header_list[]
for f in "${1:?}"/*.csv; do
  echo "### Reading header from $f"
  header_list[$(head -1 "$f")]+="${IFS}$f"
done

# Handle the list of files for each entry in header_list[]
group_id=1
for key in "${!header_list[@]}"; do
  value="${header_list[$key]}"
  groupfile="${2:?}/GROUP-${group_id}.csv"
  echo "### Header: ${key}"
  echo "### Group File: ${groupfile}"
  
  # Echo the header as the first line of $groupfile
  echo "${key}" > "${groupfile}"
  
  # Skip the first line, but echo every other line from each file with this header
  for file in ${value}; do
  echo "# File: ${file}"
    tail --lines=+2 "$file" >> "${groupfile}"
  done
  
  # Increment group_id
  (( group_id++ ))
done

Save to a file and then run with two arguments: The directory containing the source files, and the output directory.

Some caveats:

  • The output directory must exist.
  • Filenames with characters in $IFS will not be handled properly.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .