How to merge DataFrame in for loop?

Question

Am trying to merge the multiindexed dataframe in a for loop into a single dataframe on index.

i have a reproducible code at https://gist.github.com/RJUNS/f4ad32d9b6da8cf4bedde0046a26f368#file-prices-py I wanted to post the code here, but i got an error 'your post has lots of code' therefore i posted it on gist.

But it produces this:

                          CLOSE    HIGH    LOW    OPEN   VOLUME
2017-09-08 09:30:00 VEDL  330.2  330.40  328.3  329.10  1873261
2017-09-08 09:45:00 VEDL  333.1  333.15  329.5  330.15  1643970
2017-09-08 10:00:00 VEDL  332.4  333.20  331.4  333.10   767922
                           CLOSE   HIGH    LOW    OPEN  VOLUME
2017-09-08 09:30:00 INFY  892.65  898.6  892.6  898.05  163020
2017-09-08 09:45:00 INFY  892.45  893.6  891.4  892.80  152179
2017-09-08 10:00:00 INFY  891.55  892.5  891.1  892.40  104931

Am expecting the following output:

                          CLOSE    HIGH    LOW    OPEN   VOLUME
2017-09-08 09:30:00 VEDL  330.2  330.40  328.3  329.10  1873261
                    INFY  892.65  898.6  892.6  898.05  163020
2017-09-08 09:45:00 VEDL  333.1  333.15  329.5  330.15  1643970
                    INFY  892.45  893.6  891.4  892.80  152179
2017-09-08 10:00:00 VEDL  332.4  333.20  331.4  333.10   767922
                    INFY  891.55  892.5  891.1  892.40  104931

I tried using .join method, but i couldn't make it work. does anyone have any solution please?

jezrael · Accepted Answer · 2017-09-08 13:45:02Z

4

I think you need append df to list of DataFrames and then use concat with sort_index:

dfs =[]
for security in stocks:
        dfs.append(get_google_data(security,900, 1))

df = pd.concat(dfs).sort_index()
print(df)
                           CLOSE    HIGH     LOW    OPEN   VOLUME
2017-09-08 06:00:00 INFY  892.65  898.60  892.60  898.05   163020
                    VEDL  330.20  330.40  328.30  329.10  1873261
2017-09-08 06:15:00 INFY  892.45  893.60  891.40  892.80   152179
                    VEDL  333.10  333.15  329.50  330.15  1643970
2017-09-08 06:30:00 INFY  891.55  892.50  891.10  892.40   104931
                    VEDL  332.40  333.20  331.40  333.10   767922
2017-09-08 06:45:00 INFY  891.10  891.55  889.55  891.55   282589
                    VEDL  332.10  332.80  331.30  332.40   384417
2017-09-08 07:00:00 INFY  890.90  891.60  890.25  891.10   119252
                    VEDL  332.15  332.70  331.65  332.05   345358

List comprehension version for create list of DataFrames:

df = pd.concat([get_google_data(x,900, 1) for x in stocks]).sort_index()
print(df)
                           CLOSE    HIGH     LOW    OPEN   VOLUME
2017-09-08 06:00:00 INFY  892.65  898.60  892.60  898.05   163020
                    VEDL  330.20  330.40  328.30  329.10  1873261
2017-09-08 06:15:00 INFY  892.45  893.60  891.40  892.80   152179
                    VEDL  333.10  333.15  329.50  330.15  1643970
2017-09-08 06:30:00 INFY  891.55  892.50  891.10  892.40   104931
                    VEDL  332.40  333.20  331.40  333.10   767922
2017-09-08 06:45:00 INFY  891.10  891.55  889.55  891.55   282589
                    VEDL  332.10  332.80  331.30  332.40   384417
2017-09-08 07:00:00 INFY  890.90  891.60  890.25  891.10   119252
                    VEDL  332.15  332.70  331.65  332.05   345358

edited Sep 8, 2017 at 13:45

answered Sep 8, 2017 at 13:26

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

6 Comments

BlueQuant Over a year ago

Awesome man, that works.. but what if i need to drop NaNs from some row which could make pd.concat to not work, so can u please provide an example with .merge or .join or any other workaround if dimensions do not matches?

jezrael Over a year ago

Not sure if understand your question - if need merge by index with list of dataframes concat is implemnted for this. Can you eplain more NaN problem?

BlueQuant Over a year ago

suppose if INFY has 20 rows and VEDL has 15 rows, will pd.concat work? :)

jezrael Over a year ago

Yes, sure. very nice. Now each MultiIndex has 2 rows, if merge 15 with 20 then get 15 pairs and 5 single rows (if same dates in both)

BENY Over a year ago

or you can try reduce with merge

|

Collectives™ on Stack Overflow

How to merge DataFrame in for loop?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related