I have a CSV file having these values (without column):-


I want to add a column name for these rows values.

Suppose all I (i.e: I:30n, I:12n, etc)value record should be in one column and likewise all J (i.e J:0n, J:0n) should be in one column and vise-versa using python.

Can anyone help?


1 Answer 1


You can use Pandas for this, your file format isn't exactly comma-separated values file. But still you can use pandas read_csv() method. Suppose your file name is test_file

import pandas as pd
df = pd.read_csv('test_file', sep=':', header=None)
>>> df
   0      1
0  I    30n
1  J     0n
2  J     0n
3  U  1000n
4  C     0n
5  I    12n
6  I    10n
7  I    10n
8  I    10n
9  I    10n

Then you can use the pivot() function:

>>> df.pivot(columns=0)
\     1                 
0    C    I    J      U
0  NaN  30n  NaN    NaN
1  NaN  NaN   0n    NaN
2  NaN  NaN   0n    NaN
3  NaN  NaN  NaN  1000n
4   0n  NaN  NaN    NaN
5  NaN  12n  NaN    NaN
6  NaN  10n  NaN    NaN
7  NaN  10n  NaN    NaN
8  NaN  10n  NaN    NaN
9  NaN  10n  NaN    NaN

If your intention is to write it back to a file you can use the to_csv() method.

# this row eliminates the level headers of the columns at level 0
>>> df.columns=df.columns.get_level_values(1)
>>> df
0   C    I   J      U
0      30n           
1           0n       
2           0n       
3               1000n
4  0n                
5      12n           
6      10n           
7      10n           
8      10n           
9      10n           
>>> df.to_csv('new_test_file', index=False)


If you wish to make it less sparse, you can first turn it into a dict and then back to DataFrame:

>>> _dict = df.groupby(0)[1].apply(list).to_dict()
>>> _dict
{'C': ['0n'], 'I': ['30n', '12n', '10n', '10n', '10n', '10n'], 'J': ['0n', '0n'], 'U': ['1000n']}
>>> pd.DataFrame.from_dict(_dict, orient='index')
       0     1     2     3     4     5
C     0n  None  None  None  None  None
I    30n   12n   10n   10n   10n   10n
J     0n    0n  None  None  None  None
U  1000n  None  None  None  None  None

>>> pd.DataFrame.from_dict(_dict, orient='index').T
      C    I     J      U
0    0n  30n    0n  1000n
1  None  12n    0n   None
2  None  10n  None   None
3  None  10n  None   None
4  None  10n  None   None
5  None  10n  None   None

pd.Series.to_dict() pd.DataFrame.from_dict() pd.DataFrame.T


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.