Adding columns to a pandas dataframe based on values of another column

Question

This is part of an ongoing series of issues I'm having trying to condense a csv file with multiple rows for each client based on the number of medical services they received. For each service, they have a row. I've included the dataframe at the bottom.

I'm trying to calculate how many times a client (identified with an ID_profile number) got each type of service and add that to a column named for the type of service. So, if a client got 3 Early Intervention Services, I would add the number "3" to the "eisserv" column. Once that is done, I want to combine all the client rows into one.

Where I'm getting stuck is populating 3 different columns with data based off one column. I am trying to iterate through the rows using some strings for the function to compare to. The function works, but for reasons I can't understand, all the strings change to "25" as the function works.

import pandas as pd
df = pd.read_csv('fakeRWclient.csv')

df['PrimaryServiceCategory'] = df['PrimaryServiceCategory'].map({'Referral for Health Care/Supportive Services': '33', 'Health Education/Risk reduction': '25', 'Early Intervention Services (Parts A and B)': '11'})

df['ServiceDate'] = pd.to_datetime(df['ServiceDate'], format="%m/%d/%Y")
df['id_profile'] = df['id_profile'].apply(str)
df['served'] = df['id_profile']  + " " + df['PrimaryServiceCategory']

df['count'] = df['served'].map(df['served'].value_counts())
eis = "11"
ref = "33"
her = "25"
print("Here are the string values")
print(eis)
print(ref)
print(her)
df['herrserv']=""
df['refserv']=""
df['eisserv']=""
for index in df.itertuples():
    for eis in df['PrimaryServiceCategory']:
        df['eisserv'] = df['count']
    for her in df['PrimaryServiceCategory']:
        df['herrserv'] = df['count']
    for ref in df['PrimaryServiceCategory']:
        df['refserv'] = df['count']
print("Here are the string values")
print(eis)
print(ref)
print(her)

Here is the output:

Here are the string values
11
33
25
Here are the string values
25
25
25
  id_profile ServiceDate PrimaryServiceCategory     served  count  herrserv  
\
0        439  2017-12-05                     25     439 25      1         1   
1     444654  2017-01-25                     25  444654 25      2         2   
2      56454  2017-12-05                     33   56454 33      1         1   
3      56454  2017-01-25                     25   56454 25      2         2   
4     444654  2017-03-01                     25  444654 25      2         2   
5      56454  2017-01-01                     25   56454 25      2         2   
6      12222  2017-01-05                     11   12222 11      1         1   
7      12222  2017-01-30                     25   12222 25      3         3   
8      12222  2017-03-01                     25   12222 25      3         3   
9      12222  2017-03-20                     25   12222 25      3         3   

   refserv  eisserv  
0        1        1  
1        2        2  
2        1        1  
3        2        2  
4        2        2  
5        2        2  
6        1        1  
7        3        3  
8        3        3  
9        3        3

Why do the string values switch? And is this even the right function to do what I'm hoping to do?

It is changing because you are redefining your variables here for index in df.itertuples(): for eis in df['PrimaryServiceCategory']: df['eisserv'] = df['count'] for her in df['PrimaryServiceCategory']: df['herrserv'] = df['count'] for ref in df['PrimaryServiceCategory']: df['refserv'] = df['count'] — It_is_Chris
– It_is_Chris, Commented Mar 20, 2018 at 16:05

jpp · Accepted Answer · 2018-03-20 17:04:13Z

2

You can use pandas.get_dummies after mapping your integers to categories, then merge with your dataframe.

You can add a 'count' column summing the 3 category counts afterwords.

df = pd.DataFrame({'id_profile': [439, 444654, 56454, 56454, 444654, 56454, 12222, 12222, 12222, 12222],
                   'ServiceDate': ['2017-12-05', '2017-01-25', '2017-12-05', '2017-01-25', '2017-03-01', '2017-01-01', '2017-01-05', '2017-01-30', '2017-03-01', '2017-03-20'],
                   'PrimaryServiceCategory': [25, 25, 33, 25, 25, 25, 11, 25, 25, 25]})

d = {11: 'eis', 33: 'ref', 25: 'her'}
df['Service'] = df['PrimaryServiceCategory'].map(d)

df = df.set_index('id_profile')\
       .join(pd.get_dummies(df.drop('PrimaryServiceCategory', 1), columns=['Service'])\
               .groupby(['id_profile']).sum())

#            ServiceDate  PrimaryServiceCategory Service  Service_eis  \
# id_profile                                                            
# 439         2017-12-05                      25     her            0   
# 12222       2017-01-05                      11     eis            1   
# 12222       2017-01-30                      25     her            1   
# 12222       2017-03-01                      25     her            1   
# 12222       2017-03-20                      25     her            1   
# 56454       2017-12-05                      33     ref            0   
# 56454       2017-01-25                      25     her            0   
# 56454       2017-01-01                      25     her            0   
# 444654      2017-01-25                      25     her            0   
# 444654      2017-03-01                      25     her            0   

#             Service_her  Service_ref  
# id_profile                            
# 439                   1            0  
# 12222                 3            0  
# 12222                 3            0  
# 12222                 3            0  
# 12222                 3            0  
# 56454                 2            1  
# 56454                 2            1  
# 56454                 2            1  
# 444654                2            0  
# 444654                2            0

edited Mar 20, 2018 at 17:04

answered Mar 20, 2018 at 16:02

jpp

166k37 gold badges301 silver badges362 bronze badges

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

5 Comments

EduardoG Over a year ago

I get an error with this: ValueError: columns overlap but no suffix specified: Index(['ServiceDate'], dtype='object')

EduardoG Over a year ago

I'm on .22.0. I wonder if they're different enough to produce different results.

EduardoG Over a year ago

I also dropped the ServiceDate column and that fixed the error, but this code did not then produce the 3 columns.

jpp Over a year ago

@EduardoX, I added my setup above, building the dataframe from scratch. Try copy/pasting and see if it works.

EduardoG Over a year ago

Let us continue this discussion in chat.

jjsantoso · Accepted Answer · 2018-03-20 16:26:48Z

1

The values of eis, ref and her switch to "25" because you are looping over the variable PrimaryServiceCategory, and the last value in that serie is "25". You are using eis, ref and her as the names of the iterator variable, so they change in every loop. I think this is an inefficient way to do it. It's better if you use groupby and transform:

df['count'] = df.groupby(['id_profile','PrimaryServiceCategory']).transform('count')

answered Mar 20, 2018 at 16:26

jjsantoso

1,7582 gold badges15 silver badges18 bronze badges

3 Comments

EduardoG Over a year ago

Here is the dataframe I get from this line of code: id_profile ServiceDate PrimaryServiceCategory count 0 439 2017-12-05 25 1970-01-01 00:00:00.000000001 1 444654 2017-01-25 25 1970-01-01 00:00:00.000000002 2 56454 2017-12-05 33 1970-01-01 00:00:00.000000001 3 56454 2017-01-25 25 1970-01-01 00:00:00.000000002 4 444654 2017-03-01 25 1970-01-01 00:00:00.000000002

EduardoG Over a year ago

Why is it bringing in the service date here?

jjsantoso Over a year ago

If you have more than one variable after grouping, you have to specify which variable you want to count, by default pandas count all the DataFrame. You could modify: df['count'] = df.groupby(['id_profile','PrimaryServiceCategory'])['ServiceDate'].transform('count') In this case it will count all the rows that have a not-null value in ServiceDate.

MSS · Accepted Answer · 2018-03-20 16:14:19Z

1

I have made changes to your existing code only.

    import pandas as pd
    df = pd.read_csv('fakeRWclient.csv')

    df['PrimaryServiceCategory'] = df['PrimaryServiceCategory'].map({'Referral for Health Care/Supportive Services': '33', 'Health Education/Risk reduction': '25', 'Early Intervention Services (Parts A and B)': '11'})

    df['ServiceDate'] = pd.to_datetime(df['ServiceDate'], format="%m/%d/%Y")
    df['id_profile'] = df['id_profile'].apply(str)

    print(df.groupby('id_profile').PrimaryServiceCategory.count())

Above code will give output like this:

id_profile
439       1
12222     4
56454     3
444654    2

answered Mar 20, 2018 at 16:14

MSS

3,6832 gold badges28 silver badges57 bronze badges

Collectives™ on Stack Overflow

Adding columns to a pandas dataframe based on values of another column

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related