Vfrdtyky

I am pretty new to Python, so I am possibly looking over an easy solution, but everything I have tried thus far has been fruitless.

I have hundreds of CSV files with identical format. The format I have is

--Name of File (unimportant)

--Single Number Value (unimportant)

--Important Row of Column Names

--Two More Rows of Unimportant Formatting Garbage

--Thousands of Rows of Important Data

--Several Blank Rows

--Thousands of Rows of Unimportant Garbage Again

I need to format it so that I am able to easily grab the Column Names and the Important Data underneath. The format is set so that the column names are always on row 5 and that the data always starts on row 8, but the amount of data can very from several hundred to several thousand.

EDIT: I got the exact row number of the heading wrong. Also, I forgot to mention that I need to save the result to a dataframe for future analysis.

This is an image of the top of the csv file

This is an image of the bottom of the csv file. Note that when it switches from 'important data' to 'unimportant data' the number of columns increases, which might make programming difficult.

You can use the below code. I got the column names with the line number =5, and data starting from line number =8 and stopped where we encounter a blank line.

import csv,pandas as pd

Space_encounter_linenum_flag=0

index_df=-1

#This flag is set when it encounters first blank line after the data values end

with open("C:/Users/user/PycharmProjects/spacysample/MrX.csv", 'r') as csvfile:

     csvreader = csv.reader(csvfile, delimiter=',')

     for row in csvreader:

         index_df=index_df+1

         if csvreader.line_num==5:

             #To get column names

             print("THE COLUMN NAMES IN LINE NUMBER 5 ARE...........")

             print(', '.join(row))

             df_col=pd.DataFrame(row)

         if csvreader.line_num==8:

             #To get data values

             print("**********************************************************")

             print("THE DATA VALUES STARTING FROM LINE NUMBER 8 ARE...........")

             while row[-1] is '':

                 row.pop()

             print(', '.join(row))

             df_col.append(row)

         if (csvreader.line_num>8) and max(row, key=len)=='':

             #set flag when blank line is encountered

             Space_encounter_linenum_flag=1

         if (csvreader.line_num>8 and row is not '') and (row is not '') and Space_encounter_linenum_flag!=1:

             #stop where blank line is encountered

             while row[-1] is '':

                 row.pop()

             print(', '.join(row))

             df_val=pd.DataFrame(row)

             df_col.append(df_val)

         if (csvreader.line_num>8) and Space_encounter_linenum_flag==1:

             print('Loop breaks at, line number: '+str(csvreader.line_num))

             break

Hope this does exactly what you want.

import pandas as pd

df = pd.read_csv('path_to_your_csv', header=5)[7:]

# List Columns

df.columns

In case you don't have pandas : pip install pandas

read_csv docs : https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html