Assign Unique Values according Distinct Columns Values
<p>I know the question name is a little ambiguous.</p>

<p>My goal is to assign global key column based on 2 columns + unique value in my data frame.</p>

<p>For example</p>

<pre><code>CountryCode | Accident
   AFG          Car
   AFG          Bike
   AFG          Car
   AFG          Plane
   USA          Car
   USA          Bike
   UK           Car
</code></pre>

<p>Let Car = 01, Bike = 02, Plane = 03</p>

<p>My desire global key format is [Accident][CountryCode][UniqueValue]</p>

<p>Unique value is a count of similar [Accident][CountryCode]</p>

<p>so if Accident = Car and CountryCode = AFG and it is the first occurrence, the global key would be 01AFG01</p>

<p>The desired dataframe would look like this:</p>

<pre><code>CountryCode | Accident | GlobalKey
   AFG          Car        01AFG01
   AFG          Bike       02AFG01
   AFG          Car        01AFG02
   AFG          Plane      01AFG03
   USA          Car        01USA01
   USA          Bike       01USA02
   UK           Car        01UK01
</code></pre>

<p>I have tried running a for loop to append Accident Number and CountryCode together</p>

<p>for example:</p>

<pre><code>globalKey = []

for x in range(0,6):
    string = df.iloc[x, 1]
    string2 = df.iloc[x, 2]
    if string2 == 'Car':
        number = '01'
    elif string2 == 'Bike':
        number = '02'
    elif string2 == 'Plane':
        number = '03'
    #Concat the number of accident and Country Code
    subKey = number + string
    #Append to the list
    globalKey.append(subKey)
</code></pre>

<p>This code will provide me with something like <code>01AFG</code>, <code>02AFG</code> based on the value I assign. but I want to assign a unique value by counting the occurrence of when <code>CountryCode</code> and <code>Accident</code> is similar.</p>

<p>I am stuck with the code above. I think there should be a better way to do it using map function in Pandas.</p>

<p>Thanks for helping guys!
much appreciate!</p>

<p>I don't have any experience of Pandas so this answer may not be what you are looking for. That being said, if the data you have is really that simple (few countries, few accident types) have you considered storing each country|accident combination in their own value?</p>

<p>So as you traverse your input, just increment the counter for that country|accident combination, and then read through those counters at the end to produce the <code>GlobalKeys</code>.</p>

<p>If you have other data to store besides the Global Key, then store the country|accident combinations as lists, and read through them at the end one-at-a-time to produce <code>GlobalKeys</code>.</p>

python pandas dataframe group-by