Why is my data empty when i add it to my dataframe column?

Question

I use a filter to check for conditions in my dataframe so i can mark them.

filtering = (dfsamen.shift(0).moving=='movingToclose') & (more condtions)
dffilter = pd.Dataframe(data=filtering, columns = ['filter'])
dffilter['DateTime'] = dfsamen['DateTime']

Output:

filtering

4     False
5     False
6      True
7      True

dffilter

4    False 2018-06-03 06:33:38.593
5    False 2018-06-03 06:33:39.197
6     True 2018-06-03 06:33:40.597
7     True 2018-06-03 06:33:41.800

But later I use the same code with different condtions and it doesn't work

filtering2 = (dfsamen.shift(0).Input5==1) | (more conditions)
dffilter2 = pd.DataFrame(data=filtering2, columns=['filter2'])
dffilter2['DateTime'] = dfsamen['DateTime']

Output:

filtering2

4     False
5      True
6      True
7      True

dffilter2 (before the datetime is added)

Empty DataFrame
Columns: [filter2]
Index: []

dffilter2 (with datetime)

4      NaN 2018-06-03 06:33:38.593
5      NaN 2018-06-03 06:33:39.197
6      NaN 2018-06-03 06:33:40.597
7      NaN 2018-06-03 06:33:41.800

So why does my data dissapear in the second filter when i add it to the column eventhough the data exists in the filtering2?

jezrael · Accepted Answer · 2018-12-14 08:53:54Z

1

Problem is your DataFrame constructor, because is created default RangeIndex so possible different indices in both DataFrames, data are not alignes and you get NaNs column for rows with different index values.

Solution is converting values to numpy arrays:

filtering = (dfsamen.shift(0).moving=='movingToclose') & (more condtions)

dffilter = pd.DataFrame(data=filtering.values, columns = ['filter'])
dffilter['DateTime'] = dfsamen['DateTime'].values
print (dffilter)

Sample:

dfsamen = pd.DataFrame({
        'A':list('abc'),
        'DateTime':pd.date_range('2015-01-01', periods=3),
        'C':[7,8,9]
}, index=[4,5,6])

print (dfsamen)
   A   DateTime  C
4  a 2015-01-01  7
5  b 2015-01-02  8
6  c 2015-01-03  9

filtering = dfsamen.A == 'a'

dffilter = pd.DataFrame(data=filtering.values, columns = ['filter'])
dffilter['DateTime'] = dfsamen['DateTime'].values
print (dffilter)
   filter   DateTime
0    True 2015-01-01
1   False 2015-01-02
2   False 2015-01-03

Or use Series.to_frame for converting Series to DataFrame with one column:

filtering = dfsamen.A == 'a'

dffilter = filtering.to_frame('filter')
dffilter['DateTime'] = dfsamen['DateTime'].values
print (dffilter)
   filter   DateTime
4    True 2015-01-01
5   False 2015-01-02
6   False 2015-01-03

edited Dec 14, 2018 at 8:53

answered Dec 14, 2018 at 8:43

jezrael

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

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

6 Comments

Martijn van Amsterdam Over a year ago

I don't fully understand your explanation, since filtering and filtering2 have the exact same amount of rows and index. Only the ordering of the True and False are different. So why does it work in the first case and not in the second?

jezrael Over a year ago

@MartijnvanAmsterdam - So what is print (dffilter.index) and print (dffilter2.index) ? Is it difference with print (dfsamen.index) ?

Martijn van Amsterdam Over a year ago

I worded my previous comment uncorrectly, i meant to say: "they should have the same index" print (dffilter.index) gives me Int64index([0, 1, to 31]) print (dffilter2.index) gives me Intdex([], dtype='object') Since dffilter2 is empty this is expected. But print(filtering2) gives me a True/False column with the an index [0, 1, to 31]. print (dfsamen.index) also gives me Int64index([0, 1, to 31])

jezrael Over a year ago

@MartijnvanAmsterdam - so problem is empty dataframe, so no index values.

jezrael Over a year ago

I think there is problem passing Series to DataFrame, it create one column DataFrame - pd.DataFrame(data=filtering). But if pass columns parameter it failed - dffilter = pd.DataFrame(data=filtering, columns = ['filter']) - I think problem is different name of filtering.name what is converting to DataFrame column and columns = ['filter'] - and it return empty DataFrame. Correct passing is dffilter = pd.DataFrame({'filter':filtering}) or converting to numpy array dffilter = pd.DataFrame(data=filtering.values, columns = ['filter'])

|

Collectives™ on Stack Overflow

Why is my data empty when i add it to my dataframe column?

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