Formatting data in pandas

Question

I attempted to run the code:

import pandas as pd

df = pd.read_csv('test.csv', sep=',', header=None, names=['datatable', 'col'])

def replace_letter(group):
    letters = group.isin(['T', 'Q'])              # select letters
    group[letters] = int(group[~letters].max()) + 1  # replace by next max
    return group


df['col'] = df.groupby('datatable').transform(replace_letter)
print df

On the data:

DatatableA,1
DatatableA,2
DatatableA,3
DatatableA,4
DatatableA,5
DatatableB,1
DatatableB,6
DatatableB,T
DatatableB,3
DatatableB,4
DatatableB,5
DatatableB,2
DatatableC,3
DatatableC,4
DatatableC,2
DatatableC,1
DatatableC,Q
DatatableC,5
DatatableC,T

Hoping to produce the following

DatatableA,1
DatatableA,2
DatatableA,3
DatatableA,4
DatatableA,5
DatatableB,1
DatatableB,6
DatatableB,7
DatatableB,3
DatatableB,4
DatatableB,5
DatatableB,2
DatatableC,3
DatatableC,4
DatatableC,2
DatatableC,1
DatatableC,6
DatatableC,5
DatatableC,6

and I received the traceback:

Traceback (most recent call last):
  File "C:/test.py", line 11, in <module>
    df['col'] = df.groupby('datatable').transform(replace_letter)
  File "C:\Python27\lib\site-packages\pandas\core\groupby.py", line 1981, in transform
    res = path(group)
  File "C:\Python27\lib\site-packages\pandas\core\groupby.py", line 2006, in <lambda>
    slow_path = lambda group: group.apply(lambda x: func(x, *args, **kwargs), axis=self.axis)
  File "C:\Python27\lib\site-packages\pandas\core\frame.py", line 4416, in apply
    return self._apply_standard(f, axis)
  File "C:\Python27\lib\site-packages\pandas\core\frame.py", line 4491, in _apply_standard
    raise e
ValueError: ("invalid literal for int() with base 10: 'col'", u'occurred at index col')

I am trying to replace the letter T or any other letter for that matter with the next highest integer for that table. The first table contains no errors, the second table contains 1 T and the third contains 2 x t's. Is there something I have used in correctly, but I have been meaning to use pandas more, as the library seems so useful for data manipulations.

Edit 1

It was indeed an issue with having a header, simply changing header to =True made it work. However I still cannot get this code to do what I want it to do.

import pandas as pd

df = pd.read_csv('test.csv', sep=',', header=True, names=['datatabletest', 'col'])

def replace_letter(group):
    letters = group.isin(['T', 'Q'])              # select letters
    group[letters] = int(group[~letters].max()) + 1  # replace by next max
    return group

df["duplicate"] = df['col']
print df
df['col'] = df.groupby('datatabletest').transform(replace_letter)
print df

I was hoping to duplicate the column so I have a copy of the original, changing the letters in only one of the columns. Can you see what I have done wrong?

Answer 1

I guess your csv file in fact contains a header, that is its first line is datatable,col. Then, when you specify header=None, this header is loaded as a first row of a dataframe. You should either skip header with skiprows paramether, or read it from file removing header=None.

Compare those two examples:

>>> s = "DatatableA,1"
>>> df = pd.read_csv(StringIO(s),  sep=',', header=None, 
...          names=['datatable', 'col'])
>>> df['col'] = df.groupby('datatable').transform(replace_letter)
>>> df
    datatable  col
0  DatatableA    1    

But

>>> df = pd.read_csv(StringIO('datatable,col\n'+s),  sep=',',
...          header=None, names=['datatable', 'col'])
>>> df['col'] = df.groupby('datatable').transform(replace_letter)
Traceback (most recent call last):
  ...
ValueError: ("invalid literal for int() with base 10: 'col'", u'occurred at index col')