creating an empty data frame and filling with existing column in another file with conditions

Question

I am trying to create an empty data frame and filling the empty data frame with columns existing in another file. It works when i use this simple code.

InputData['Quote'] = store['QUOTE_ID']

but when i add some conditions before the code then it does not accept the conditions and gives same values as in store(original)file. below is my code i am trying to use .

original data set

InputData = pd.read_csv('datalink')

creating empty data frame

 OutputData=pd.DataFrame()

code with conditions

for i in xrange(len(InputData.index)):
  if (i % 5000) == 0:
      print i,
  if ((InputData.ix[i,'WIN']=='Y') and          ((InputData.ix[i,'COM_C']=='H') or (InputData.ix[i,'COM_C']=='S'))    and(InputData.ix[i,'COM_L']!=0)):
        OutputData['Quote']=InputData['QUOTE_ID']
        OutputData['ComList']=InputData['COM_LISTPR']
        OutputData['WIN']=1
        OutputData['COM_C']=InputData['COM_C']


 OutputData.to_csv(link,index=False)

original data set

QUOTE_ID    WIN COM_C   COM_L
1400453-IT  N   H   1.46E+05
1400453-IT  N   H   7.12E+04
1400453-IT  N   H   2.74E+04
1403796-IT  Y   S   3.11E+04
1400453-IT  N   M   3.12E+02
1403796-IT  Y   H   3.97E+04
1403796-IT  Y   H   3.97E+04
1403796-IT  Y   M   1.99E+02
1403796-IT  Y   M   1.99E+02
1403796-IT  Y   H   7.40E+04
1403796-IT  Y   H   7.40E+04
1403796-IT  Y   M   3.19E+02
1403796-IT  Y   M   3.19E+02
1403796-IT  Y   H   9.56E+04

expected data set

require only Y from InputData and replace to 1 if Y

        Quote   WIN COM_C   COM_LISTPR
1403796-IT  1   S   3.11E+04
1403796-IT  1   H   3.97E+04
1403796-IT  1   H   3.97E+04
1403796-IT  1   H   7.40E+04
1403796-IT  1   H   7.40E+04
1403796-IT  1   H   9.56E+04

many thanks in advance

Vedang Mehta · Accepted Answer · 2016-05-20 13:14:12Z

1

Python code -

import pandas as pd

df = pd.read_csv('a.csv', delim_whitespace=True)  # reading file

modified_df = df[(df['WIN'] == 'Y') & ((df['COM_C'] == 'S') | (df['COM_C'] ==
                                                               'H')) &
                 (df['COM_L'] !=
                  0)].copy()

modified_df['WIN'] = 1

print(modified_df)

Output -

      QUOTE_ID  WIN COM_C  COM_L
3   1403796-IT    1     S  31100
5   1403796-IT    1     H  39700
6   1403796-IT    1     H  39700
9   1403796-IT    1     H  74000
10  1403796-IT    1     H  74000
13  1403796-IT    1     H  95600

edited May 20, 2016 at 13:14

answered May 20, 2016 at 12:32

Vedang Mehta

2,25211 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ysearka Over a year ago

I didn't know dataframe had a replace attribute, good to know. But there, since there are only 'Y' in the column 'WIN' you might as well use: modified_df['WIN'] = 1

Vedang Mehta Over a year ago

I got a warning when I tried to do that -

SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_index,col_indexer] = value instead

ysearka Over a year ago

Yes, because the way you defined modified_df, it's an image of df, not a whole new object. If you add .copy() when you first define modified_df, the warning won't appear anymore.

Collectives™ on Stack Overflow

creating an empty data frame and filling with existing column in another file with conditions

original data set

creating empty data frame

code with conditions

original data set

expected data set

require only Y from InputData and replace to 1 if Y

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

original data set

creating empty data frame

code with conditions

original data set

expected data set

require only Y from InputData and replace to 1 if Y

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related