CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
user1718097
user1718097

Reputation: 4292

Appending Pandas dataframes in for loop results in ValueError

I want to generate a dataframe that is made up of separate dataframes generated in a for loop. Each individual dataframe consists of a name column, a range of integers and a column identify a category to which the integer belongs (e.g. quintile 1 to 5). If I generate each dataframe individually and then append one to the other to create a 'master' dataframe then there are no problems. However, when I use a loop to create each individual dataframe (as I will need to do in my real life situation) then trying to append a dataframe to the master dataframe results in:

ValueError: incompatible categories in categorical concat

I've written a simplified loop to illustrate:

import numpy as np
import pandas as pd

# Define column names
colNames = ('a','b','c')

# Define a dataframe with the required column names
masterDF = pd.DataFrame(columns = colNames)

# A list of the group names
names = ['Group1','Group2','Group3']

# Create a dataframe for each group
for i in names:
    tempDF = pd.DataFrame(columns = colNames)
    tempDF['a'] = np.arange(1,11,1)
    tempDF['b'] = i
    tempDF['c'] = pd.cut(np.arange(1,11,1),
                        bins = np.linspace(0,10,6),
                        labels = [1,2,3,4,5])
    print(tempDF)
    print('\n')

    # Try to append temporary DF to master DF
    masterDF = masterDF.append(tempDF,ignore_index=True)

print(masterDF)

I would expect a dataframe that looked like:

     a       b  c
 0   1  Group1  1
 1   2  Group1  1
 2   3  Group1  2
 3   4  Group1  2
 4   5  Group1  3
 5   6  Group1  3
 6   7  Group1  4
 7   8  Group1  4
 8   9  Group1  5
 9  10  Group1  5
10  11  Group2  1
11  12  Group2  1
12  13  Group2  2
13  14  Group2  2
...
28  29  Group3  5
29  30  Group3  5

It seems that a partial solution can be obtained by typecasting the categories as they are added to the tempDF as follows:

tempDF['c'] = pd.cut(np.arange(1,11,1),
                     bins = np.linspace(0,10,6),
                     labels = [1,2,3,4,5]).astype('int')

However, in this case, the categories (column 'c') are now displayed as 1.0, 2.0, etc. rather than 1, 2, etc. so is not ideal.

Can anyone please explain why this happens and suggest a more satisfactory solution.

Upvotes: 2

Views: 7532

Answers (1)

jezrael
jezrael

Reputation: 862561

You can first append all DataFrames to list dfs and then concat:

dfs = []
# Create a dataframe for each group
for i in names:
    tempDF = pd.DataFrame(columns = colNames)
    tempDF['a'] = np.arange(1,11,1)
    tempDF['b'] = i
    tempDF['c'] = pd.cut(np.arange(1,11,1),
                        bins = np.linspace(0,10,6),
                        labels = [1,2,3,4,5])
    print(tempDF)
    print('\n')

    # Try to append temporary DF to master DF
    dfs.append(tempDF)

masterDF = pd.concat(dfs, ignore_index=True)

print(masterDF)
     a       b  c
0    1  Group1  1
1    2  Group1  1
2    3  Group1  2
3    4  Group1  2
4    5  Group1  3
5    6  Group1  3
6    7  Group1  4
7    8  Group1  4
8    9  Group1  5
9   10  Group1  5
10   1  Group2  1
11   2  Group2  1
12   3  Group2  2
13   4  Group2  2
14   5  Group2  3
15   6  Group2  3
16   7  Group2  4
17   8  Group2  4
18   9  Group2  5
19  10  Group2  5
20   1  Group3  1
21   2  Group3  1
22   3  Group3  2
23   4  Group3  2
24   5  Group3  3
25   6  Group3  3
26   7  Group3  4
27   8  Group3  4
28   9  Group3  5
29  10  Group3  5

Upvotes: 1

Related Questions