CODEWITHSUNDEEP
pythoncsvpandasjupyter-notebook
yanadm
yanadm

Reputation: 707

Filling a column with data obtained by comparing data from another column

I have a table. I'm creating a new column "Timing". And I need to fill it with numbers, depending on what data is in the column "type". For example, if in the cell of the column "type" is dgv, then in the column "timing" there should be a number 17, if ds, then 8, if psp, then 3, etc. In total there are several conditions.

part of the table and so on

My code:

import csv

with open('C:/Notebook/data1.txt','r') as csvinput:
    with open('C:/Notebook/datawr1.txt', 'w') as csvoutput:
        writer = csv.writer(csvoutput, lineterminator='\n')
        reader = csv.reader(csvinput)

        all = []
        row = next(reader)
        row.append('Timing') # Here I create a column "Timing"
        all.append(row)

        for row in reader:  #I think here should be a condition if
            row.append(' ') 
            all.append(row)           


        writer.writerows(all)

Upvotes: 2

Views: 70

Answers (1)

jezrael
jezrael

Reputation: 863531

I think you can use map by dictionary d, if not match get NaN:

df = pd.DataFrame({'type':['dgv','ds','psp', 'a']})
print (df)
  type
0  dgv
1   ds
2  psp
3    a

d = {'dgv':17,'ds':8,'psp':3}
df['Timing'] = df['type'].map(d)
print (df)
  type  Timing
0  dgv    17.0
1   ds     8.0
2  psp     3.0
3    a     NaN

EDIT:

In pandas for reading files is use read_csv, for writing to_csv (no problem if it is .txt file):

import pandas as pd
from pandas.compat import StringIO

temp=u"""code,type,date,quantity
0,dgv,07.11.2016,1
0,dgv,08.06.2016,1
0,ds,01.07.2016,1
0,ds,03.08.2016,1
0,ds,03.08.2016,1
0,psp,06.03.2016,1
0,a,07.08.2016,1"""
#after testing replace 'StringIO(temp)' to 'filename.txt'
df = pd.read_csv(StringIO(temp))
print (df)
   code type        date  quantity
0     0  dgv  07.11.2016         1
1     0  dgv  08.06.2016         1
2     0   ds  01.07.2016         1
3     0   ds  03.08.2016         1
4     0   ds  03.08.2016         1
5     0  psp  06.03.2016         1
6     0    a  07.08.2016         1

d = {'dgv':17,'ds':8,'psp':3}
df['Timing'] = df['type'].map(d)
print (df)
   code type        date  quantity  Timing
0     0  dgv  07.11.2016         1    17.0
1     0  dgv  08.06.2016         1    17.0
2     0   ds  01.07.2016         1     8.0
3     0   ds  03.08.2016         1     8.0
4     0   ds  03.08.2016         1     8.0
5     0  psp  06.03.2016         1     3.0
6     0    a  07.08.2016         1     NaN

df.to_csv('myfile.txt', index=False)

Upvotes: 1

Related Questions