How to create column for my csv file in python

Question

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

I:30n
J:0n
J:0n
U:1000n
C:0n
I:12n
I:10n
I:10n
I:10n
I:10n

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?

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)

OR

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