<jupyter_start><jupyter_text>Working with a Messy CSV File        Here is a CSV file that is an accumulation of seperate test results. The goal is to reorganize            the data so an actual analysis is possible.	  	    	    The most popular Pandas tutorials. Get a total of 7 tutorials!<jupyter_code>import pandas as pd
from io import StringIO
import sys
print('Python version ' + sys.version)
print('Pandas version ' + pd.__version__)<jupyter_output>Python version 3.5.1 |Anaconda custom (64-bit)| (default, Feb 16 2016, 09:49:46) [MSC v.1900 64 bit (AMD64)]
Pandas version 0.20.1<jupyter_text>Open the file from HellI first just opened the file and noticed how the different chunks of data were separated. From the output below, you will see ***\n,,,,,,,,\n*** and this is what we will use as our delimiter.<jupyter_code>with open('test.csv', 'r') as myfile:
    print(myfile.read().split(' '))<jupyter_output>['Wavelength,A1,B1,C1,D1,E1,F1,G1,H1\n300,3359,1395,2079,2890,2085,4918,1596,3069\n303,3879,1746,2391,3453,2500,5820,1961,3669\n306,4230,2010,2801,3688,2883,6554,2402,4136\n309,4452,2116,3270,3931,3137,7437,2711,4504\n312,4811,2333,3382,4025,3412,7919,2768,4764\n315,5080,2417,3658,4348,3535,8617,3023,5261\n318,5505,2725,3898,4723,3796,9350,3220,5642\n321,5895,2700,4286,5031,4075,9976,3357,5994\n324,6554,3023,4560,5656,4492,10964,3637,6437\n327,6955,3303,4929,6014,4714,11902,3821,7280\n330,7647,3541,5417,6640,5300,12998,4252,7879\n333,8349,4024,5848,7276,5751,13415,4661,8547\n336,8932,4317,6290,7784,6318,13981,4969,9279\n339,9570,4855,6944,8342,6795,14064,5444,9824\n342,10222,5143,7131,8944,7239,14165,5937,10456\n345,10730,5644,7578,9615,8021,13708,6433,10971\n348,11533,6141,8028,10175,8688,13547,7103,11357\n351,12340,6715,8320,11056,9386,13436,7653,12326\n354,13080,7360,8830,11567,10379,13658,8210,12690\n357,13612,7869,9657,12615,11159,14157,8942,13392\n360,14862,8345,10027,13564,12197[...]<jupyter_text>Here we separate the different chunks of data and place them in a list<jupyter_code># open file and split
with open ('test.csv', 'r') as myfile:
    data = myfile.read().split('\n,,,,,,,,\n')

# show me the first chunk
data[0]<jupyter_output><empty_output><jupyter_text>Why use StringIO(x)?  Since pd.read_csv() needs an actual csv file as input, we fake it using StringIO.  * In cases where you want a file-like object that ***ACTS*** like a file, but is writing to an in-memory string buffer<jupyter_code># convert each chunk we have parsed thus far and turn it into a dataframe
pieces = [pd.read_csv(StringIO(x),sep=',') for x in data]

# get first df
pieces[0].head()<jupyter_output><empty_output><jupyter_text>Turn the list of dataframes into one big dataframe  [Click Here](http://www.hedaro.com/pandas-concat) if you are unfamilar with the ***Concat*** function.<jupyter_code>merged = pd.concat(pieces)
merged.head()<jupyter_output><empty_output><jupyter_text>Some Pandas magic to shape the data just how we want it<jupyter_code>df = pd.DataFrame(merged.set_index('Wavelength').unstack()).reset_index()

# we rename the columns as Pandas gives them generic names
df.columns = ['index', 'Wavelength', 'values']


df.head()<jupyter_output><empty_output><jupyter_text>Now on to the index CSV file  As you can see, we basically used the same stradegy here<jupyter_code>with open ('index.csv', 'r') as myfile:
    data = myfile.read().split('\n,,,,,,,,,\n')

data
pieces = [pd.read_csv(StringIO(x),sep=',') for x in data]

# We do get a bunch on Unnamed columns
pieces[1]
# we then glue all the pieces and make one big dataframe
merged2 = pd.concat(pieces, axis=1)
merged2<jupyter_output><empty_output><jupyter_text>We rename the columns here because in the next sections, we are going to take advantage of the different groups having identical column names<jupyter_code>merged2 = merged2.rename(columns = {'strain':'index', 'col':'index'})

# Each of the three groups will have identical column names
merged2<jupyter_output><empty_output><jupyter_text>Here is the way I figured out how to reshape the data. You might find there is a beter/easier way.<jupyter_code># get a list of all the column names
col = merged2.columns.unique()

pieces = []

# pick a column name and stick it in a list
# note every iteration will have a df of three columns
for c in col:
    n = merged2[c]
    
    # We make every chunk has the same column names
    # We do this so that when we "concat", we end up with three columns
    n.columns = ['index','strain','col']
    pieces.append(n)
    
pieces[0]   
# combine all of the pieces into one big dataframe
lookup = pd.concat(pieces) 
lookup.head(20)
# We can then join on the "index" columns on both dataframes
df.merge(lookup, how='left', left_on='index', right_on='index')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12777545035609553, 'fraction_numerical': 0.16945957268537915, 'mean_word_length': 6.1375186846038865, 'pattern_counts': {'":': 0, '<': 35, '<?xml version=': 0, '>': 35, 'https://': 0, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '204749', 'license': 'MIT', 'n_tokens_mistral': 2242, 'n_tokens_neox': 1777, 'n_words': 460, 'path': 'jupyter/learn-pandas/notebooks/Working with a Messy CSV File.ipynb', 'repo_name': 'mullenkamp/EcanPythonCourse2019', 'size': '4774'}