How To Create Custom DataFrame From Subset Of CSV File

Question

I have a CSV file that looks like this:

    1, 2, 3, 4, 5 ...
    0, 1, 0, 1, 0 ...
    0, 1, 0, 1, 0 ...
    str1, str2, str3, str4, str5 ...
    val1, val1.1, val1.2, val1.3, val1.4 ...
    val2, val2.1, val2.2, val2.3, val2.4 ...
...

and I want to generate a dataframe that looks like this:

str2, str5
val1.1 val2.1
val2.1 val2.2
...

Here is my attempt:

for f in files:
    data = pd.read_excel(f)
    df = df.append(data)

df[5:10] //only care about values in rows below [str1, str2, ..., strn].
d = df.ix[:, '2' : '5']
d.columns = ['str2', 'str3', 'str4', 'str5'] //rename columns, reduce table size.

this produces:

str2 str3 str4 str5 
val1.1 val1.2 val1.3 val1.4 ...
...

How do I eliminate str3 and str4 to get my original intended dataframe?

Answer 1

IMO this can be done much more efficiently.

Parse (read) only the data that you really need - this will be much faster and will reduce the memory consumption dramatically. Beside that using pd.concat() once instead of adding each new DF using df.append() should be much faster.

Code:

import glob
import pandas as pd


def get_merged(files, **kwargs):
    return pd.concat([pd.read_csv(f, **kwargs) for f in files], ignore_index=True)


fmask = r'd:/temp/__0?.csv'
files = glob.glob(fmask)

df = get_merged(files, sep='\s*,\s*', usecols=['str2', 'str5'],
                skiprows=3, nrows=2)

print(df)

Output:

      str2     str5
0   val1.1   val1.4
1   val2.1   val2.4
2  val21.1  val21.4
3  val22.1  val22.4

If you are working with Excel files just change read_csv to read_excel in the get_merged() function and check whether nrows parameter is working in read_excel() function

Files:

__01.csv:

1, 2, 3, 4, 5
0, 1, 0, 1, 0
0, 1, 0, 1, 0
str1, str2, str3, str4, str5
val1, val1.1, val1.2, val1.3, val1.4
val2, val2.1, val2.2, val2.3, val2.4
..................
.................
..................
..................

__02.csv:

1, 2, 3, 4, 5
0, 1, 0, 1, 0
0, 1, 0, 1, 0
str1, str2, str3, str4, str5
val21, val21.1, val21.2, val21.3, val21.4
val22, val22.1, val22.2, val22.3, val22.4
.................................

Answer 2

You can simply delete any undesired columns. Check out this discussion on deleting a Pandas DataFrame column: Delete column from pandas DataFrame

Then cruise on over to the documentation: http://pandas.pydata.org/pandas-docs/stable/dsintro.html#column-selection-addition-deletion

df = df.drop('str3', 1)
df = df.drop('str4', 1)