Related questions

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

  • Date Quarters in 'Series' object python

    I'm trying to count the value in each date in order to count witch quarter is high frequency I try these methods not working so far... please help :) thanks. Note: stime is 'Series' object 

    stime=df['timestamp']
#print (df['timestamp'].filter(like='08', axis=0)
#stime.filter(like='2018-07-18')
#stime.between_date('2018-01-01','2018-02-01', include_start=True, include_end=True)
#stime.month
  • Python to sort tag with different attributes

    I have a list from links = set(soup.findAll('a')), now I want to sort them, but links = sorted(links) got error, what can I sort it?

    The original list and any else attributes:

    <a href="//weathernews.jp/typhoon/">台風</a>
<a class="list" href="https://weathernews.jp/s/topics/201809/190035/?fm=tp_index"></a>
<a class="list" href="https://weathernews.jp/s/topics/201809/130155/?fm=tp_index"></a>
<a href="//weathernews.jp/warning/">警報・注意報</a>
<a class="list" href="https://weathernews.jp/s/topics/201809/140125/?fm=tp_index"></a>
<a class="list" href="https://weathernews.jp/s/topics/201809/170145/?fm=tp_index"></a>

    I want to sort it to

    <a class="list" href="https://weathernews.jp/s/topics/201809/190035/?fm=tp_index"></a>
<a class="list" href="https://weathernews.jp/s/topics/201809/170145/?fm=tp_index"></a>
<a class="list" href="https://weathernews.jp/s/topics/201809/140125/?fm=tp_index"></a>
<a class="list" href="https://weathernews.jp/s/topics/201809/130155/?fm=tp_index"></a>
<a href="//weathernews.jp/typhoon/">台風</a>
<a href="//weathernews.jp/warning/">警報・注意報</a>
  • Changing the encoding of a .csv in Python 3

    I have a .csv file whose encoding is cp1252 and I want to transform it into utf8. To the new .csv I want to apply a function and analyze the data. My attempt was to iterate through the rows and get a list of cells:

    def convertEncoding(path):
with open(path, 'r', encoding = 'cp1252') as file:
    csv_2 = csv.reader(file, delimiter='\t', quotechar='"')
    for row in csv_2:
        f = ', '.join(row)
        print(f)

    How do I create and use the .csv document with the right encoding in a big function that processes the entire file (and it even uses the name of the file)?

    def function(myfile):
    string_array = myfile.split('_')
    data_start_string = string_array[1].split('-')
    data_end_string = string_array[3].split('-')
    ...
  • Converting a multiple datatype dataframe to integer coded dataframe in python using pandas

    I have a dataframe like this in python - 

      INSTRUMENT_TYPE_CD     RISK_START_DT     ...      FIN_POS_IND PL_FINAL_IND
0     Physical Index  01-03-2017 00:00     ...                0           No
1     Fin Basis Swap  01-09-2018 00:00     ...                0           No
2     Physical Index  01-09-2017 00:00     ...                0           No
3     Physical Index  01-12-2016 00:00     ...                0           No
4     Fin Basis Swap  01-02-2018 00:00     ...                0           No

    as you can see, the values of elements in the columns are repetitive and generally string. I want to convert this dataframe into a integer coded dataframe that maps each unique string in a column to some unique integer/number.

    So far I have come up with this (normalise method) but it doesn't work.

    normalise(dataframe)

def normalise(dataframe):
    for column in dataframe:
        dataframe[column] = dataframe.apply(unique_code_mapper(dataframe[column]))
    return dataframe

def unique_code_mapper(column):
    unique_array = []
    for val in column:
        if val in unique_array:
            column.loc[val] = unique_array.index(val)
        else:
            unique_array.append(val)
            column.loc[val] = unique_array.index(val)
    return column

    It returns the following error:

    TypeError: ("'Series' object is not callable", 'occurred at index INSTRUMENT_TYPE_CD')
  • How to add constant string in each elements of list in python?

    I want to add a constant value as prefix in each elements of list. I want to do something similar to this post . But above answers are using for loop. I want to aviod for loops in my program.

    My objective is I have to create a list it values should be "unknown contact number 0", "unknown contact number 1","unknown contact number 2"..."unknown contact number n". here unknown contact number is my prefix. I want to add this element in my list.

    So far I tried this,

    x=pd.DataFrame(index=range(val))
print ('unknown contact number '+x.index.astype(str)).values.tolist()

    My question is Am I adding more complex to my code to avoid for loops? or any other better approaches to solve this problem?

    Thanks in advance.

  • Validate Avro file using JSON schema

    We have requirement of validating avro file data using Json schema in Spark 2.0 JSON schema is of type draft 4.

    Requirement is validate every column from row with multiple rules like integer range, in between values, greater than a value etc. on same column and collect all errors. Can you please help on this.

  • Iterate over series in python

    I have column in my data frame (having semicolon separated values),I want to split this data in different columns and place these numbers index wise. i.e number 1 should be at index 1, 2 at index 2....5 at index 5..rest of the places should be blank.

    code:

    a_1=[]
a_2=[]
a_3=[]
a_4=[]
a_5=[]

for indx,i in range(dp_con['IDENTIFICATION']).iteritems():
    if '1' in i:
        a_1.append(1)
    else:
        a_1.append(0)
    if '2' in dp_con['IDENTIFICATION'][i]:
        a_2.append(2)
    else:
        a_2.append(0)
    if '3' in dp_con['IDENTIFICATION'][i]:
        a_3.append(3)
    else:
        a_3.append(0)
    if '4' in dp_con['IDENTIFICATION'][i]:
        a_4.append(4)
    else:
        a_4.append(0)
    if '5' in dp_con['IDENTIFICATION'][i]:
        a_5.append(5)
    else:
        a_5.append(0)

    column looks like:

    1;2;
5;
5;
1;3;
1;3;
1;2;3;
5;
5;
1;2;
1;3;