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>First of all, don't use for loops if you can help it. For example, you can do your Accident to code mapping with:</p>

<pre><code>df['AccidentCode'] = df['Accident'].map({'Car': '01', 'Bike': '02', 'Plane': '03'})
</code></pre>

<p>To get the unique code, <a href="http://stackoverflow.com/a/39589991/12663">Thanos has shown how to do</a> that using <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.GroupBy.cumcount.html" rel="nofollow">GroupBy.cumcount</a>:</p>

<pre><code>df['CA_ID'] = df.groupby(['CountryCode', 'Accident']).cumcount() + 1
</code></pre>

<p>And then to put them all together into a unique key:</p>

<pre><code>df['NewKey'] = df['AccidentCode'] + df['CountryCode'] + df['CA_ID'].map('{:0&gt;2}'.format)
</code></pre>

<p>which gives:</p>

<pre><code>  CountryCode Accident GlobalKey AccidentCode  CA_ID   NewKey
0         AFG      Car   01AFG01           01      1  01AFG01
1         AFG     Bike   02AFG01           02      1  02AFG01
2         AFG      Car   01AFG02           01      2  01AFG02
3         AFG    Plane   01AFG03           03      1  03AFG01
4         USA      Car   01USA01           01      1  01USA01
5         USA     Bike   01USA02           02      1  02USA01
6          UK      Car    01UK01           01      1   01UK01
</code></pre>

python pandas dataframe group-by