python dataframe counter on a column
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
0values: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 0Detail + 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
-
Trying to run a custom cli application by specifying it in "scripts" in setup.py ends in ImportError
I have made a simple weather rest api that I want to call from terminal and all it should do is print the current weather.
While learning to package my module my first step was to make it install-able and run as soon as you imported it. And it worked perfectly.$ python3 Python 3.6.6 (default, Sep 12 2018, 18:26:19) [GCC 8.0.1 20180414 (experimental) [trunk revision 259383]] on linux Type "help", "copyright", "credits" or "license" for more information. >>> import weather {'weather': 'weather', 'temperature': 20}The next stop was to NOT invoke
pythonandimport weather, but to directly call it from cli as$ weather.
Taking help from the doc here I have the following structure and code that gives me an error -
Folder structureweather_rest_api |-- setup.py |-- weather | |-- get_weather.py | |-- __init__.pyContents of
setup.py:from setuptools import setup, find_packages setup(name='weather', version='0.0', description='Weather api', author='Shantam Raj', author_email='xyz@zyx.com', license='MIT', packages=['weather'], scripts=['weather/__init__.py'], install_requires=[ 'requests', ], zip_safe=False)Contents of
get_weather.py(commented the actual code, mimicking the output by hardcoding the results):import requests #id = xyz #appid = 'zyx' #r = requests.get(f'http://api.openweathermap.org/data/2.5/weather?id={id}&appid={appid}') #content = r.json() #weather = content['weather'][0]['main'] #temperature = content['main']['temp'] #print({"weather": weather, "temperature": temperature}) print({"weather": "Thunderstorm", "temperature": "20"}Contents of
__init__.py:from weather import get_weatherInstalling:
Then I didsudo python3 setup.py installError message when I execute
weatheron terminal -$ weather Traceback (most recent call last): File "/usr/local/bin/weather", line 11, in <module> load_entry_point('weather==0.0', 'console_scripts', 'weather')() File "/home/walker/.local/lib/python3.6/site-packages/pkg_resources/__init__.py", line 484, in load_entry_point return get_distribution(dist).load_entry_point(group, name) File "/home/walker/.local/lib/python3.6/site-packages/pkg_resources/__init__.py", line 2713, in load_entry_point raise ImportError("Entry point %r not found" % ((group, name),)) ImportError: Entry point ('console_scripts', 'weather') not foundHow to get my script to work by just calling
weatherfrom cli?NOTE:
I cannot bind it to a function as this is a stripped down version (honestly, contrived) of something big that I am trying. So the idea is that if I am able to call this script from cli then I will also be able to call the bigger script from cli. -
Solving a string maths equation without the eval function (python)
Currently i am working a way to evaluate math equations without the use of inbuilt functions or external libraries,i initially thought of splitting the equations by + and - then calculate the inner value which would be * and / but i am currently stuck on how to proceed. The current code works only if + and - exists.
def evaluate(expression): answer = expression actual_plus = 0 index_of_times = [] index_of_divide = [] answer = answer.replace("-","+-") split_by_plus = answer.split("+") for line in split_by_plus : print(line) if "*" in line or "/" in line : print("work in progress") else : actual_plus += float(line) #add values answer = actual_plus return answer result = evaluate("1+2-3+4") result = evaluate("3.5+4*3.0/4-12/5+1.2") -
finding the index of previous element in loop
I had changed the structure of my code and this is a separate question from the previous one.
I am currently writing a program that demonstrates the Gale-Shapley algorithm.
Description of Gale Shapley Algorithm' I have two lists as input.
For list preference_boys, the elements in the nested lists are each boy's preference of the girls
from highest to lowest. For list preference_girls, the nested lists are each girl's preference of the boys.
In the code, the first ones to choose are the boys and I have successfully managed to pair each boy
with his first choice of the girls.
''' preference_boys = [[2, 0, 1, 3], [1, 2, 3, 0], [0, 3, 1, 2], [2, 3, 0, 1]] preference_girls = [[0, 2, 1, 3], [2, 3, 0, 1], [3, 1, 2, 0], [0, 3, 1, 2]] ''' def couple_maker(preference_boys, preference_girls): # stores the couples based on boys first choice of girls temporary_couples = [] single_boys = [] single_girls = [] for boy in range(len(preference_boys)): single_boys.append(boy) for girl in range(len(preference_girls)): single_girls.append(girl) # single_boys = [0, 1, 2, 3] # single_girls = [0, 1, 2, 3] while len(single_boys) > 0: for boy in single_boys: for girl in preference_boys[single_boy]: if girl in single_girls: temporary_couple = (boy, girl) single_boys.remove(boy) temporary_couples.append(temporary_couple) break elif girl not in single_girls: #current_boy = preference_girls[girl].index(???) ################################################################ I am in the middle of writing a case where the girl in iteration is no longer a single girl. I just cannot figure out a way to find the index of the boy that is already paired up with the girl ############################################################### potential_boy = preference_girls[girl].index(boy)With the above preferences,
I know that in temporary_couples = [(0, 2), (1, 1), (2, 0)] for the case where the iterated girls are in single_girls.
When boy(3) tries to couple with his first choice, which is girl(2), we go through the case where the iterated girl is not in single_girls.
Now, here is my problem. I can immediately find the girl's index of preference of boy(3), but how can I find the the girl's index of preference of boy(0) who was iterated before boy(3)?
-
Pandas- Create new column based on multiple conditions
I am trying to create a new column which has a number of conditions:
For each 'Deal' - so it would have to be a groupby for Deal & TYPE
The first rule is: the first time
RunningTotal(ANNUAL) is >= 20,000 & TYPE == 'ANNUAL'for EACH deal, multiplyRunning Total(ANNUAL) by .06, then after that while conditionRunningTotal(ANNUAL) is >= 20,000 & 'TYPE'=='ANNUAL'still holds, multiply'Amount' by .06(to each row after the initial row whereRunningTotal(ANNUAL) is >= 20,000is satisfied &'TYPE'=='ANNUAL')The second rule: if
TYPE == 'MONTHLY' & Amount >=2000, then multiply'Amount' by .06g= df.groupby(['Deal','Type']) g['Amount'].apply(lambda x: x.first_valid_index() * .06 if x>=20000 \ else (x['Amount'] * .06 if x>=20000 else x['Amount'] * .06 if \ x['Type']=='MONTHLY' and x['Amount']>=1500)also tried with with
g.apply(lambda x: x['RunningTotal(ANNUAL)'].first_valid_index() * .06\ if x['RunningTotal(ANNUAL)']>=20000 else (x['Amount'] * .06 if\ x>=20000 else x['Amount'] * .06 if x['Type']=='MONTHLY' and \ x['Amount']>=1500)This if what my DF looks like + the Desired Column
Deal TYPE Month Amount RunningTotal(ANNUAL) Desired A ANNUAL April 1000 1000 0 A ANNUAL April 2000 3000 0 A MONTHLY June 2000 3000 120 B MONTHLY April 11150 0 669 B ANNUAL July 700 700 0 B ANNUAL August 303.63 1003.63 0 C ANNUAL April 25624.59 25624.59 1537.47 D ANNUAL June 5000 5000 0 D ANNUAL July 5000 10000 0 D ANNUAL August 5000 15000 0 D ANNUAL September5000 20000 1200 D ANNUAL October 3000 23000 180 D MONTHLY November 2000 23000 120 E ANNUAL April 10 10 0 E MONTHLY May 1000 10 0 E ANNUAL May 500 510 0 E MONTHLY June 500 510 0 E ANNUAL June 600 1110 0 E MONTHLY July 300 1110 0 E MONTHLY July 8200 1110 492 E ANNUAL August 20000 21110 1266.6 E ANNUAL September 1000 22110 60 -
Improving performance of XML to dataframe parsing
I have a long xml with the following structure:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <SoccerFeed timestamp="20180212T2228+0000"> <Game id="3323" home_team_id="83" home_team_name="CFC" away_team_id="335" away_team_name="WCFC" game_date="22180212T2000+0000" competition_id="18" competition_name="DCF" season_id="2011" season_name="Season 2017/2018" matchday="27" period_1_start="22180212T2000+0000" period_2_start="22180212T2105+0000" Status="FullTime"> <Event id="1182774006" event_id="10223" type_id="37" period_id="14" min="0" sec="0" player_id="1212" team_id="83" outcome="1" x="45.7" y="59.0" timestamp="2018-02-12 21:52:45" last_modified="2018-02-12 21:52:48" version="15184723628801"> <Q id="16517621865" qualifier_id="302"/> </Event> <Event id="120438223891" event_id="8726" type_id="30" period_id="14" min="0" sec="0" player_id="" team_id="335" outcome="0" x="" y="" timestamp="2018-02-12 21:52:46" last_modified="2018-02-12 21:52:46" version="15128472366490"> <Q id="125850245236" qualifier_id="57" value="1.0"/> <Q id="1775059222" qualifier_id="209"/> </Event> </Game> </SoccerFeed>The file normally has 1500+ Event nodes.
My current code parses the file in 20-21 seconds but I feel like there are some obvious performance improvements that I am missing. Any tips would be appreciated.
I am pretty sure there are better ways to access all of the attributes and their values without having to define each one individually.
Current Code
import xml.etree.ElementTree as ET import pandas as pd # define the location of your file file_name = "possessions.xml" # parse the xml and conver to a tree and root tree = ET.parse(file_name) root = tree.getroot() ## get the main game info from the single 'Game' node gameinfo = root.findall('Game') gameinfo = gameinfo[0] game_id = gameinfo.get('id') home_team_id = gameinfo.get('home_team_id') home_team_name = gameinfo.get('home_team_name') away_team_id = gameinfo.get('away_team_id') away_team_name = gameinfo.get('away_team_name') competition_id = gameinfo.get('competition_id') competition_name = gameinfo.get('competition_name') season_id = gameinfo.get('season_id') ## create an empty dataframe to store the per event information catcher = pd.DataFrame() # loop through each event node and grab the information for i in root.iter('Event'): # get the info from the Event node main chunk id = i.get('id') event_id = i.get('event_id') type_id = i.get('type_id') period_id = i.get('period_id') min = i.get('min') sec = i.get('sec') player_id = i.get('player_id') team_id = i.get('team_id') x = i.get('x') y = i.get('y') possession_id = i.get('possession_id') sequence_id = i.get('sequence_id') # combine the event main node data into a dataframe Edata = pd.DataFrame( {'id': id, 'event_id': event_id, 'type_id': type_id, 'period_id': period_id, 'min': min, 'sec': sec, 'player_id': player_id, 'team_id': team_id, 'x': x, 'y': y, 'sequence_id': sequence_id, 'possession_id':possession_id }, index = [0]) # find all of the Q information for that file Qs = i.findall("./Q") # create some empty lists to append the results to qualifier_id = [] Q_value = [] # loop through all of the Qs and grab the info for child in Qs: qualifier_id.append(child.get('qualifier_id')) Q_value.append(child.get('value')) # create a dataframe from the two lists Qdata = pd.DataFrame( {'qualifier_id': qualifier_id, 'value': Q_value }) # transform the dataframe, 1st row to the headers, # drop the extra row, reset the index Qdata = Qdata.T Qdata.columns = Qdata.iloc[0] Qdata = Qdata.reindex(Qdata.index.drop('qualifier_id')) Qdata = Qdata.reset_index(drop=True) # combine the event info and qualifer info result = pd.concat([Qdata, Edata], axis=1) # bind to the catcher dataframe catcher = catcher.append(result) # Add all of the the game information to each row catcher['competition_id'] = competition_id catcher['game_id'] = game_id catcher['home_team_id'] = home_team_id catcher['home_team_name'] = home_team_name catcher['away_team_id'] = away_team_id catcher['away_team_name'] = away_team_name catcher['competition_id'] = competition_id catcher['competition_name'] = competition_name catcher['season_id'] = season_id catcher['competition_id'] = competition_id -
Pandas - different set of keywords for different IDs, search if string contains the keyword
I have 2 sets of dataframes
IDs Keywords 0 1234 APPLE ABCD 1 1234 ORANGE 2 1234 LEMONS 3 5346 ORANGE 4 5346 STRAWBERRY 5 5346 BLUEBERRY 6 8793 TEA COFFEESecond Dataframe:
IDs Name 0 1234 APPLE ABCD ONE 1 5346 APPLE ABCD 2 1234 STRAWBERRY YES 3 8793 ORANGE AVAILABLE 4 8793 TEA AVAILABLE 5 8793 TEA COFFEEI would like to search for the keywords based on IDs level, use it for the second dataframe and search for a column:Name if the keywords contains in the Name for the same IDs exist, provide any indicator True, otherwise False.
For Example: For IDs 1234, APPLE ABCD, ORANGE, LEMONS are the keywords. So in the second dataframe: Index Row 0 with APPLE ABCD ONE will be True as "APPLE ABCD" is part of the keywords
For IDs 5346, ORANGE, STRAWBERRY, BLUEBERRY are the keywords. So in the second dataframe, Index Row 1 with APPLE ABCD will be False.
IDs Name Indicator 0 1234 APPLE ABCD ONE True 1 5346 APPLE ABCD False 2 1234 STRAWBERRY YES False 3 8793 ORANGE AVAILABLE False 4 8793 TEA AVAILABLE False 5 8793 TEA COFFEE True -
Optimising a code for bar chart creation from different data frames
I am new to Python and I am learning to create charts from data frames. I have few data frames that look like this:
Company Case 1 Case 2 Case 3 Max value Min value A -15 25 28 28 -15 B 20 26 21 26 21 C 18 -22 10 22 -22 Company Case 1 Case 2 Case 3 Max value Min value A -15 28 26 28 -15 B 12 26 11 26 11 C 12 -25 10 12 -25Where three first columns are just some values and the last two columns correspond to max and mix values for each company. I have created two codes for a bar chart for each data frame. Codes are the following:
graph_A = df1[['Case 1', 'Case', 'Case 3']].plot(kind = 'bar', width=.33, legend = True, color=['green', 'blue', 'red'], alpha = .7)plt.suptitle("Scenario 1")graph_C.yaxis.set_major_formatter(mpl.ticker.StrMethodFormatter('{x:,.0f}'))plt.legend(frameon = False)plt.xticks(rotation = 0)plt.xlabel("Company name")plt.ylabel("Value")graph_B = df2[['Case 1', 'Case', 'Case 3']].plot(kind = 'bar', width=.33, legend = True, color=['green', 'blue', 'red'], alpha = .7)plt.suptitle("Scenario 2")graph_C.yaxis.set_major_formatter(mpl.ticker.StrMethodFormatter('{x:,.0f}'))plt.legend(frameon = Falseplt.xticks(rotation = 0)plt.xlabel("Company name")plt.ylabel("Value")My question is how could I optimise my code? For example, imagine I had 100 similar looking tables and repeating the same code just with few changes would make no sense. Could I use a loop in this case or something?
Thank you.
-
Find count of each group based on continuous occurrence of a number with condition with another columns in pandas dataframe
I have a data frame like this
col1 col2 col3 A 1 2 B 0 1 B 0 0 B 0 0 A 1 1 B 1 0 A 0 0 A 0 1 B 0 2 B 0 1If there are continuous 0 in col2 for at least 2 times and for those continuous zeros if the sum of the col3 is less than 2, how to find the total count of those events for each group ?
for example:
col1 count A 2 B 3