iterate through csv columns to create multiple python dataframe

Question

I am trying to create multiple data frames using the columns of a excel csv file. This is where I have been able to get to

import pandas as pd
file = pd.read_csv('file.csv')
df = pd.DataFrame(file)
cols = df.columns
#column names are 'Date', 'Stock 1', 'Stock 2', etc - I have 1000 columns

for i in range(len(cols)):
    df[i] = df[['Date',b(i)]]

So the end result is I want multiple dataframes. The first dataframe is with columns 1 and 2 (so Date and Stock 1), the second dataframe is with columns 1 and 3 (so Date and Stock 2), the third dataframe is with columns 1 and 3, creating new dataframe all the way to Columns 1 and 1000.

I have tried several ways and either get index in not callable or I tried with usecols and I get usecols must be strings or integers.

Can anyone help me with this. Conceptually it is easy but I can not get the code right. Thank you.

bananafish · Accepted Answer · 2016-12-20 02:27:51Z

1

This does what you are asking:

all_dfs = []
for col in df.columns:
    if col != 'Date':
        df_current = df[['Date', col]]
        all_dfs.append(df_current)

Or as one line:

all_dfs = [df[['Date', col]] for col in df.columns if col != 'Date']

But you probably don't want to do that. There's not much point. What are you really trying to do?

edited Dec 20, 2016 at 2:27

answered Dec 20, 2016 at 2:01

bananafish

2,91722 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ted Petrou Over a year ago

You might need to have an if statement for col != 'Date'

J Westwood Over a year ago

I am ultimately trying to create multiple data dataframes with Date and 1 column from the CSV. THe CSV has 1000 columns all with a stock and column of stock prices Header 1 Header 2 Header 3 Header 4 ======== ======== ======== ======== Date Stock 1 Stock 2 Stock 3 1/2/2001 2.77 6.00 11.00 1/3/2001 2.89 6.08 11.10 1/4/2001 2.86 6.33 11.97 1/5/2001 2.80 6.58 12.40

J Westwood Over a year ago

so I ultimately want to produce 100 dataframes so DF1 would be Date and Stock 1 and DF2 would be Date and Stock2, etc.

Parfait Over a year ago

Did you try @bananafish's solution? all_dfs should return the list of dfs you need with Date and Stock. In fact, it is better to keep as one list object instead of 100s of dfs flooding global environment (especially if same structure). Just reference each by index: all_dfs[1].

bananafish Over a year ago

@JWestwood right, but what are you going to do with these dataframes? Most likely you can do it without creating 100s of dataframes in the first place and just sticking with the one you are loading from the csv.

|

Collectives™ on Stack Overflow

iterate through csv columns to create multiple python dataframe

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related