#!/usr/bin/python3

import openpyxl
from array import *

wb = openpyxl.load_workbook("test.xlsx")  
sheet = wb.active  
 # find where the data starts and delete the random text
for i, row in enumerate(sheet): 
    name = row[0].value 
    if name == "Action":
       sheet.delete_rows(1, i)
       break
col1=[]
col2=[]
for column in sheet.iter_cols(): 
    column_name = column[0].value 
    if column_name == "Dpt": 
       for i, cell in enumerate(column): 
           col1.append(cell.value) 
    if column_name == "Total":
       for i, cell in enumerate(column): 
           col2.append(cell.value) 
print(col1)
print(col2)

df.loc[ : , [(df[col].str.contains('Total|Team', case=False)).any() for col in df.columns]]

#!/bin/env bash
# One-liner (without mkdir and file rename)
# for FILE in *.csv; do for i in $(seq $(csvcut -n "$FILE" | wc -l)); do csvcut -H -c $i "$FILE" > columns/$i; done && grep -H -m 1 -i "Total\|Team" columns/* | awk 'length < 50' | cut -d "/" -f 2 | cut -d ":" -f 1 | while read line; do csvcut -c $line "$FILE" > ./sheets/$line; done && for n in $(seq 9); do if [ -f $i ]; then mv $i 0$i; fi; done && paste sheets/* > "$FILE.temp"; done

mkdir -p columns
mkdir -p sheets
mkdir -p newcsv

# For each csv file in current directory
for FILE in *.csv; do
  # Find number of columns in current file using csvcut and wc
  for i in $(seq $(csvcut -n "$FILE" | wc -l)); do
    # Separate all columns into files into the column directory
    csvcut -H -c $i "$FILE" > columns/$i
  done

  ## Should probably use variables and separate the next line of code
  # a. For each column, search for terms 'Total' and 'Team', but exclude results > 50 characters as likely irrelevant comments
  # b. Cut the output to make a list of column numbers
  # c. For each line of the list, use csvcut to write matching columns to sheets directory
  grep -H -m 1 -i "Total\|Team" columns/* | awk 'length < 50' | cut -d "/" -f 2 | cut -d ":" -f 1 | while read line; do csvcut -c $line "$FILE" > ./sheets/$line; done

  # Prepend a zero to files named as integers <= 9; e.g. 3 -> 03; so that paste joins columns in order
  # (Assume there must be a better way than having to do this)
  for n in $(seq 9); do if [ -f $n ]; then mv $n 0$n; fi; done

  # Join columns together
  paste sheets/* > "$FILE.temp"

done

# Rename files and move to new directory
for file in *.csv.temp; do
  mv -- "$file" "${file%.csv.temp}_filtered.csv"
done

mv *_filtered.csv newcsv