I'm having an issue that I can't explain and am hoping I am missing something simple.

I have a large dataset of shape(45Million+, 51) and am loading it in for some analyses (classifiers, deep learning, basically just trying a few different things as some research for work).

I take a few steps when I load it in:

  • dropna() to get rid of all rows with an na (only about 6K out of the 45M)
  • Use pandas get_dummies() to change a categorical variable with about a dozen classes into dummy variables (have also used sklearn's onehotencoder for this and had the same issue outlined below)

When I would run a RandomForest on a subset of the data (about 4 million rows, made using train_test_split) I would get the following error:

<ValueError: Input contains NaN, infinity or a value too large for dtype('float64').>

Since I dropped NAs at the start, this confused me, so I went back and checked the length of each output.

When I drop NAs, I get a length of 45356082 When I split off the categorical variable and one hot encode it, it has a length (and every variable within it does) of 45356082. We'll call this Dummy.

Here's where it gets weird-- when I join Dummy back to my original X as Xnew, Xnew has a length of the same as above, but the dummy variable columns now have length 45351726.

The join process is dropping like 4500 rows from the dummy columns.

Any idea why this would happen?

Here's the code I'm using:

choice_data_sub = pd.read_csv("predData.csv")
# Drop NAs
choice_data_sub = choice_data_sub.dropna()

X = choice_data_sub[["Columns1", "Column2", "Column3", "Categorical"]]
y = choice_data_sub[["NextPurchase"]]

choice_data_sub = choice_data_sub.reset_index()

gametype_df = pd.get_dummies(choice_data_sub.Categorical, prefix="Game")

# merge with X
gametype_df = gametype_df.reset_index()
# X = X.reset_index() <-- This breaks it in a different way, was tried as a fix
X = X.join(gametype_df)

Also! Just discovered that the following code works, but I'd still like to know why this didn't.

X = X.reset_index()
X = X.merge(gametype_df, left_index=True, right_index=True)

2 Answers 2


Can you post your code when you join the datasets? It's possible you're joining in a different way than you had intended. See the following helpful tutorial for figuring out exactly how you want to join datasets: https://pandas.pydata.org/docs/user_guide/merging.html

Additionally - I would check that the X and dummy dataframes have the same indexes. It's possible that in your join these forgotten entries simply don't share the same index in both dataframes.

  • $\begingroup$ Added code, thanks $\endgroup$
    – datahappy
    Sep 2, 2020 at 15:30
  • $\begingroup$ It's possible that (in the original code that didn't work) your dataframe X wasn't set to the same index. You seem to reset index a couple times on the other dataframes you're joining - perhaps your original code would have worked if you had done X = X.reset_index() before joining. Actually now that I'm looking at it - your original X dataframe will have gaps between the indexes because you're dropping the NA rows. After you drop the NA you need to reset the index so it lines up index-wise with the other dataframes that had their indexes reset. $\endgroup$ Sep 2, 2020 at 16:23
  • $\begingroup$ I did try X=X.reset_index() and it didn't produce the same error, but it did break on a different one $\endgroup$
    – datahappy
    Sep 3, 2020 at 17:35
  • $\begingroup$ Indeed, I would expect that removing all reset_index calls should make this work. Then all the frames should have the same--original--index, and joining should work fine. $\endgroup$
    – Ben Reiniger
    Mar 13 at 15:40

I'll answer this with the solution that worked for me (OP here), but should point out that I still do not understand why it did not work as listed. (But, hey, as long as it worked, right?)

Using merge instead of join, and merging on the recently reset indexes got things worked out.

X = X.reset_index()
X = X.merge(gametype_df, left_index=True, right_index=True)

Would still love some feedback from anyone that can explain the above join error!


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.