CODEWITHSUNDEEP
pythonpandascsv
Echchama Nayak
Echchama Nayak

Reputation: 933

Pandas Map creating NaNs

My intention is to replace labels. I found out about using a dictionary and map it to the dataframe. To that end, I first extracted the necessary fields and created a dictionary which I then fed to the map function.

My programme is as follows:

factor_name = 'Help in household'
df = pd.read_csv('dat.csv')
labels = pd.read_csv('labels.csv')
fact_df = labels.loc[labels['Column'] == factor_name]
fact_dict = dict(zip(fact_df['Level'], fact_df['Rename']))
print df.index.to_series().map(fact_dict)

My labels.csv is as follows:

Column,Name,Level,Rename
Help in household,Every day,4,Every day
Help in household,Never,1,Never
Help in household,Once a month,2,Once a month
Help in household,Once a week,3,Once a week
State,AN,AN,Andaman & Nicobar
State,AP,AP,Andhra Pradesh
State,AR,AR,Arunachal Pradesh
State,BR,BR,Bihar
State,CG,CG,Chattisgarh
State,CH,CH,Chandigarh
State,DD,DD,Daman & Diu
State,DL,DL,Delhi
State,DN,DN,Dadra & Nagar Haveli
State,GA,GA,Goa
State,GJ,GJ,Gujarat
State,HP,HP,Himachal Pradesh
State,HR,HR,Haryana
State,JH,JH,Jharkhand
State,JK,JK,Jammu & Kashmir
State,KA,KA,Karnataka
State,KL,KL,Kerala
State,MG,MG,Meghalaya
State,MH,MH,Maharashtra
State,MN,MN,Manipur
State,MP,MP,Madhya Pradesh
State,MZ,MZ,Mizoram
State,NG,NG,Nagaland
State,OR,OR,Orissa
State,PB,PB,Punjab
State,PY,PY,Pondicherry
State,RJ,RJ,Rajasthan
State,SK,SK,Sikkim
State,TN,TN,Tamil Nadu
State,TR,TR,Tripura
State,UK,UK,Uttarakhand
State,UP,UP,Uttar Pradesh
State,WB,WB,West Bengal

My dat.csv is as follows:

Id,Help in household,Maths,Reading,Science,Social
11011001001,4,20.37,,27.78,
11011001002,3,12.96,,38.18,
11011001003,4,27.78,70,,
11011001004,4,,56.67,,36
11011001005,1,,,14.55,8.33
11011001006,4,,23.33,,30
11011001007,4,40.74,70,,
11011001008,3,,26.67,,22.92

Intended result is as follows:

4 Every day
1 Never
2 Once a month
3 Once a week

The mapping fails. The result always causes NaNs to appear which I do not want. Can anyone tell me why?

Upvotes: 0

Views: 95

Answers (1)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210912

Try this:

In [140]: df['Help in household'] \
            .astype(str) \
            .map(labels.loc[labels['Column']=='Help in household',['Level','Rename']]
                       .set_index('Level')['Rename'])
Out[140]:
0      Every day
1    Once a week
2      Every day
3      Every day
4          Never
5      Every day
6      Every day
7    Once a week
Name: Help in household, dtype: object

You may also consider using merge:

In [147]: df.assign(Level=df['Help in household'].astype(str)) \
            .merge(labels.loc[labels['Column']=='Help in household',['Level','Rename']],
                   on='Level')
Out[147]:
            Id  Help in household  Maths  Reading  Science  Social Level       Rename
0  11011001001                  4  20.37      NaN    27.78     NaN     4    Every day
1  11011001003                  4  27.78    70.00      NaN     NaN     4    Every day
2  11011001004                  4    NaN    56.67      NaN   36.00     4    Every day
3  11011001006                  4    NaN    23.33      NaN   30.00     4    Every day
4  11011001007                  4  40.74    70.00      NaN     NaN     4    Every day
5  11011001002                  3  12.96      NaN    38.18     NaN     3  Once a week
6  11011001008                  3    NaN    26.67      NaN   22.92     3  Once a week
7  11011001005                  1    NaN      NaN    14.55    8.33     1        Never

Upvotes: 1

Related Questions