If a row under a column in a .csv file contains 1, then the same row in another column should be overwritten using python, else should be left empty

Question

I have a .csv file with about 300,000 rows. I have created a new column manually and I wish to extract information from some older columns but I can't do this manually because of the length of the file. The title of the older columns are Present, Online, Absent and the new column is Attendance. The older columns have values of 1 and 0 where the observation is true or false i.e if an observation came to the event then the "Present" column will have a value of 1 and if anything else, it carries a value of 0. Same with "online" and "absent" column.

I want the new column to take the information from the older ones, i.e if any of the older ones has a value of 1 the same row in "Attendance" should have a value of either "present", "online" or "absent" depending on which is true. I've been trying to do this using Pandas but haven't found a way that works for .csv files.

for i in f['Present']:
    if i == 1:
       f['Attendance'].write("present")

I don't mind if the solution works for one column and is repeated for the others. It doesn't have to work for all at once.

This is what it should look like

Thanks

For each row, will only one of Present, Online and Absent be true ? — Suraj, Mar 26 '20 at 03:44
kindly share a sample of ur dataframe, with ur expected output — sammywemmy, Mar 26 '20 at 04:45

Valdi_Bo · Accepted Answer · 2020-03-26T08:09:02.960

2

A more pandasonic solution (than a loop, list of conditions and loc), based actually on Numpy is:

df['Attendance'] = np.select([df.Present == 1, df.Online == 1, df.Absent == 1],
    choicelist = ['present', 'online', 'absent'], default = '')

It should run substantially quicker than the other solution.

For details see the documentation of np.select.

edited Mar 26 '20 at 08:09

answered Mar 26 '20 at 07:36

Valdi_Bo

30,023
4
23
41

score 1 · Answer 2 · answered Mar 26 '20 at 04:03

1

for i in range(len(df)):
    if int(df.loc[i, 'present']) == 1:
        df.loc[i, 'attend'] = 'present'
    elif int(df.loc[i, 'online']) == 1:
        df.loc[i, 'attend'] = 'online'
    else:
        df.loc[i, 'attend'] = 'absent'

You can traverse each row of the dataframe and set the value of the new column based on the previous 3 columns.

Refer https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

answered Mar 26 '20 at 04:03

Suraj

2,253
3
17
48

thanks. does this work only in the internal python data-frame or is permanent on the .csv file? – Ose Daniel Okojie Mar 26 '20 at 04:31
It changes the dataframe only, you can export it to csv, do refer this https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_csv.html – Suraj Mar 26 '20 at 05:03
I tried it but its running forever...had to stop it myself – Ose Daniel Okojie Mar 26 '20 at 05:15
Refer this https://stackoverflow.com/questions/16923281/writing-a-pandas-dataframe-to-csv-file – Suraj Mar 26 '20 at 05:19

If a row under a column in a .csv file contains 1, then the same row in another column should be overwritten using python, else should be left empty

2 Answers2