Panda Bears and Pythons

I’ve seen import pandaas and and numpy around the traps and thought ‘geez, that’ll be a long install. Today I saw why it’s gooda.

Some things I learnt:

  • Python on windows is crap. Try conda or linux
  • Read the excellent docs
  • Create test data to understand your problem
  • Review read_csv() result to see of Pandas go the timestamp parsed correctly. Otherwise if it thinks it’s a string or object, you can’t work with timeseries data.
  • Set the index with set_index()
  • Once in a DataFrame, resampling and merging is easy!

# import data with weird date formats and timestamp from many csv
import pandas as pd
import os

bin = "1h"

def weird_dates(date_str):
    for fmt in ("%d/%m/%Y %H:%M", "%d/%m/%Y %I:%M %p"):  # %I for 12-hour clock, %p for AM/PM
            return pd.to_datetime(date_str, format=fmt)
        except ValueError:
    raise ValueError("no valid date format found")

# Function to read CSV files and collate data
def collate_data(csv_files):
    # Initialize an empty DataFrame
    collated_data = None
    failures = []
    # Iterate through each CSV file
    for file in csv_files:
            print(f"Reading {file}")
            df = pd.read_csv(file,skiprows=2,parse_dates=['Timestamp'],date_parser=weird_dates)
            print(f"Resampling to {bin}")
            pretty_df = df.resample(bin).mean()
            pretty_df = pretty_df.round(decimals=1)
            if collated_data is None:
                collated_data = pretty_df
                collated_data = pd.merge(collated_data, pretty_df, on='Timestamp', how='outer')
        except Exception as exc:
    collated_data = collated_data.sort_values(by="Timestamp")
    print(f"Yeah, so these failed: {failures}")
    return collated_data

# Seperate into csv files by column name
def to_csv_selected(df,search_strings):
    for search_string in search_strings:
        matching_columns = [col for col in df.columns if search_string.lower() in col.lower()]
        print(f"{search_string} matched {matching_columns}")

def main():
    # Directory containing CSV files
    csv_directory = 'alldata'
    # List CSV files in the directory
    csv_files = [ f for fin os.listdir(csv_directory) if file.endswith('.csv')]
    collated_data = collate_data(csv_files)


if __name__ == "__main__":

Leave a Reply

Your email address will not be published. Required fields are marked *