python dataframe counter on a column

2018-07-11 05:56 user123 imported from Stackoverflow

I column x in dataframe has only 0 and 1. I want to create variable y which starts counting zeros and resets when when 1 comes in x. I'm getting an error "The truth value of a Series is ambiguous."

count=1                   
countList=[0]

for x in df['x']:   
if  df['x'] == 0:     
    count = count + 1
    df['y']= count
else:     
    df['y'] = 1     
    count = 1

1 answer

answered 2018-07-11 06:00 jezrael

First dont loop in pandas, because slow, if exist some vectorized solution.

I think need count consecutive 0 values:

df = pd.DataFrame({'x':[1,0,0,1,1,0,1,0,0,0,1,1,0,0,0,0,1]})

a = df['x'].eq(0)
b = a.cumsum()
df['y'] = (b-b.mask(a).ffill().fillna(0).astype(int))
print (df)

    x  y
0   1  0
1   0  1
2   0  2
3   1  0
4   1  0
5   0  1
6   1  0
7   0  1
8   0  2
9   0  3
10  1  0
11  1  0
12  0  1
13  0  2
14  0  3
15  0  4
16  1  0

Detail + explanation:

#compare by zero
a = df['x'].eq(0)
#cumulative sum of mask
b = a.cumsum()
#replace Trues to NaNs
c = b.mask(a)
#forward fill NaNs
d = b.mask(a).ffill()
#First NaNs to 0 and cast to integers
e = b.mask(a).ffill().fillna(0).astype(int)
#subtract from cumulative sum Series
y = b - e
df = pd.concat([df['x'], a, b, c, d, e, y], axis=1, keys=('x','a','b','c','d','e', 'y'))
print (df)
    x      a   b     c     d   e  y
0   0   True   1   NaN   NaN   0  1
1   0   True   2   NaN   NaN   0  2
2   0   True   3   NaN   NaN   0  3
3   1  False   3   3.0   3.0   3  0
4   1  False   3   3.0   3.0   3  0
5   0   True   4   NaN   3.0   3  1
6   1  False   4   4.0   4.0   4  0
7   0   True   5   NaN   4.0   4  1
8   0   True   6   NaN   4.0   4  2
9   0   True   7   NaN   4.0   4  3
10  1  False   7   7.0   7.0   7  0
11  1  False   7   7.0   7.0   7  0
12  0   True   8   NaN   7.0   7  1
13  0   True   9   NaN   7.0   7  2
14  0   True  10   NaN   7.0   7  3
15  0   True  11   NaN   7.0   7  4
16  1  False  11  11.0  11.0  11  0

See also questions close to this topic

Set header in python list()
How can I set header for a python list?

I'm currently doing it like this:
```
df = list()
df.append('header1')
```
Just wondering if there's a better solution...
Better way to Vlookup

I would like to know if there is a better alternative to Vlookup to find matches between two cells (or Python Dfs).

Say I have the below Dfs,

I want my code to check if the values in DF1 was in DF2, If values exactly match OR if the values partially matche return me the value in the DF2.

Just like the matches in 4th column Row 2,3 returned values.

Thanks Amigo!
How to have topic directive show up in table of contents?

The topic directive (found here: http://docutils.sourceforge.net/docs/ref/rst/directives.html#topic) is said to be used as a "self-contained section".

How can I make the title of each topic box show up in the table of contents, like a normal section title would?
How to merge pandas dataframe into existing reportlab table?
```
example_df = [[1,2,3,4,5],[1,2,3,4,5],[1,2,3,4,5]]
```
I want to integrate example_df pandas data frame into an existing Reportlab table - where the number of rows is changing (could be 3 as shown in the example, or it could be 20):
```
rlab_table(['Mean','Max','Min','TestA','TestB'],
['','','','',''], 
['','','','',''], 
['','','','','']])
```
I have tried:
```
np.array(example_df).tolist() 
```
but I get this error (AttributeError: 'int' object has no attribute 'wrapOn')

I am able to manually add each row into the report lab table by doing:
```
rlab_table(['Mean','Max','Min','TestA','TestB'],
np.array(example_df).tolist()[0],
np.array(example_df).tolist()[1], 
np.array(example_df).tolist()[2]])
```
However, the issue is that the number of rows in the dataframe is constantly changing, so I am seeking a solution similar to:
```
rlab_table(['Mean','Max','Min','TestA','TestB'],
np.array(example_df).tolist()[0:X])] 
#Where X is the number of rows in the dataframe
```
Pandas python Aggregation and Grouping - I want to show the sum on top of each different type

enter image description here

I want the result become

enter image description here

bins - Categorize column values using bins for ages

I have a .CSV file a snippet of which looks like this:

ID,SN,          Age,Gender,Item ID,Item Name,               Price

0,Lisim78,       20, Male,  108,   Extraction Quickblade,    3.53
1,Lisovynya38,   40, Male,  143,   Frenzied Scimitar,        1.56
2,Ithergue48,    24, Male,   92,   Final Critic,             4.88
3,Chamassasya86, 24, Male,  100,   Blindscythe,              3.27
4,Iskosia90,     23, Male,  131,   Fury,                     1.44
5,Yalae81,       22, Male,   81,   Dreamkiss,                3.61
6,Itheria73,     36, Male,  169,   Interrogator,             2.18
7,Iskjaskst81,   20, Male,  162,   Abyssal Shard,            2.67
8,Undjask33,     22, Male,   21,   Souleater,                 1.1
9,Chanosian48,   35, Other, 136,   Ghastly Adamantite,       3.58
10,Inguron55,     23, Male,   95,   Singed Onyx Warscythe,    4.74

I need to establish bins for the 'Age' column which I have done like so:

bins = [0, 10, 15, 20, 25, 30, 35, 40, 45]
names = ['<10', '10-14', '15-19', '20-24', '25-29', '30-34', '35-39', '40+']
df_bins = pd.cut(df['Age'], bins, labels=names)

How do I use the bins to categorize other columns like column 'SN'? I wanna be able to get a count of all players in 'SN' column who are <10, 10-14, 15-19 years... and so on.

Any help is greatly appreciated!

dtype is ignored when using multilevel columns

When using DataFrame.read_csv with multi level columns (read with header=) pandas seems to ignore the dtype= keyword. Is there a way to make pandas use the passed types? I am reading large data sets from CSV and therefore try to read the data already in the correct format to save CPU and memory.

I tried passing a dict using dtype with tuples as well as strings. It seems that dtype expects strings. At least I observed, that if I pass the level 0 keys the types are assigned, but unfortunately that would mean that all columns with the same level 0 label would get the same type. In the esample below columns (A, int16) and (A, int32) would get type object and (B, float32) and (B, int16) would get float32.

import pandas as pd df= pd.DataFrame({ ('A', 'int16'): pd.Series( [1, 2, 3, 4], dtype='int16'), ('A', 'int32'): pd.Series( [132, 232, 332, 432], dtype='int32'), ('B', 'float32'): pd.Series( [1.01, 1.02, 1.03, 1.04], dtype='float32'), ('B', 'int16'): pd.Series( [21, 22, 23, 24], dtype='int16')}) print(df) df.to_csv('test_df.csv') print(df.dtypes) # full column name tuples with level 0/1 labels don't work df_new= pd.read_csv('test_df.csv', header=list(range(2)), dtype={ ('A', 'int16'): 'int16', ('A', 'int32'): 'int32'}) print(df_new.dtypes) # using the level 0 labels for dtype= seems to work df_new2= pd.read_csv('test_df.csv', header=list(range(2)), dtype={ 'A':'object', 'B': 'float32'}) print(df_new2.dtypes)

I'd expect the second print(df.dtypes) to output the same column types as the first print(df.dtypes), but it does not seem to use the dtype= argument at all and infers the types resulting in much more memory intense types.

Was I missing something?

Thank you in advance Jottbe
Show Pandas Dataframe in for loop
I have a for-loop in which I build a pandas dataframe and everytime the loop starts over, the dataframe is updated. What I would like to do is to depict this table before being updated again and showing it again, of course with updated values. It is possible to do such if I was going to plot some value in each iteration and the plots would show up one after each other. But I seem not to be able to do the same for a dataframe or basically table.
```
df = pd.DataFrame(index = x, columns=y)
for i in range(df.shape[0]):
    for j in range(df.shape[1]):
        if condition is True:
            df.iloc[i,j] = 'True'
        else:
            df.iloc[i,j] = 'False'

    Show df!
```

Related questions

python dataframe counter on a column

1 answer

See also questions close to this topic