Issue
I have pandas dataframe like so:
import pandas as pd
pdata = (
{'id': [26, 26, 26, 26, 26, 12, 12, 12, 12, 12],
'cat': [18, 18, 18, 18, 18, 18, 18, 18, 18, 18],
's43': [7.7, 4.0, 5.8, 5.6, 6.2, 10, 10, 10, 10, 10],
's44': [6.9, 2.4, 5.3, 4.3, 2.0, 9.9, 9.9, 10, 8.2, 10],
's45': [7.7, 3.7, 5.1, 5.5, 5.7, 10, 10, 10, 10, 10],
's46': [7.8, 3.6, 4.9, 5.3, 5.0, 9.9, 9.9, 9.9, 10, 9.9],
's47': [6.7, 1.4, 3.4, 1.9, 1.1, 10, 10, 10, 5.0, 10],
's40': [7.7, 4.0, 5.6, 5.7, 5.0, 10, 10, 10, 10, 10]
}
)
pdata = pd.DataFrame(pdata)
and a pandas series like so:
pseries = ({'pen': 0.450000,
'rs': 0.693650,
's43': 0.142731,
's44': 0.170520,
's45': 0.061518,
's46': 0.014668,
's47': 0.129210,
's40': 0.409219}
)
pseries = pd.Series(pseries)
What I want to do is to multiply the dataframe by series and get back a dataframe with new values for the axis that was multiplied along with the original data in the columns that are showing NaN.
I used pseries.mul(pdata, axis=index, fill_value=1)
but it does not seem to do what I had in mind.
cat id pen rs s40 s43 s44 s45 s46 s47
0 NaN NaN NaN NaN 3.150986 1.099029 1.176588 0.473689 0.114410 0.865707
1 NaN NaN NaN NaN 1.636876 0.570924 0.409248 0.227617 0.052805 0.180894
2 NaN NaN NaN NaN 2.291626 0.827840 0.903756 0.313742 0.071873 0.439314
3 NaN NaN NaN NaN 2.332548 0.799294 0.733236 0.338349 0.077740 0.245499
4 NaN NaN NaN NaN 2.046095 0.884932 0.341040 0.350653 0.073340 0.142131
5 NaN NaN NaN NaN 4.092190 1.427310 1.688148 0.615180 0.145213 1.292100
6 NaN NaN NaN NaN 4.092190 1.427310 1.688148 0.615180 0.145213 1.292100
7 NaN NaN NaN NaN 4.092190 1.427310 1.705200 0.615180 0.145213 1.292100
8 NaN NaN NaN NaN 4.092190 1.427310 1.398264 0.615180 0.146680 0.646050
9 NaN NaN NaN NaN 4.092190 1.427310 1.705200 0.615180 0.145213 1.292100
When I use
col = ['s40', 's43', 's44', 's45', 's46', 's47']
pdata[col] = pdata[col].mul(pseries[col], axis=1)
I get the output below without the 'pen' and 'rs' values from the pseries:
id cat s43 s44 s45 s46 s47 s40
0 26 18 1.099029 1.176588 0.473689 0.114410 0.865707 3.150986
1 26 18 0.570924 0.409248 0.227617 0.052805 0.180894 1.636876
2 26 18 0.827840 0.903756 0.313742 0.071873 0.439314 2.291626
3 26 18 0.799294 0.733236 0.338349 0.077740 0.245499 2.332548
4 26 18 0.884932 0.341040 0.350653 0.073340 0.142131 2.046095
5 12 18 1.427310 1.688148 0.615180 0.145213 1.292100 4.092190
6 12 18 1.427310 1.688148 0.615180 0.145213 1.292100 4.092190
7 12 18 1.427310 1.705200 0.615180 0.145213 1.292100 4.092190
8 12 18 1.427310 1.398264 0.615180 0.146680 0.646050 4.092190
9 12 18 1.427310 1.705200 0.615180 0.145213 1.292100 4.092190
Addendum: sorry, the expected output would look like below:
cat id pen rs s40 s43 s44 s45 s46 s47
0 18.0 26.0 0.45 0.693650 3.150986 1.099029 1.176588 0.473689 0.114410 0.865707
1 18.0 26.0 0.450 0.693650 1.636876 0.570924 0.409248 0.227617 0.052805 0.180894
2 18.0 26.0 0.450 0.693650 2.291626 0.827840 0.903756 0.313742 0.071873 0.439314
3 18.0 26.0 0.450 0.693650 2.332548 0.799294 0.733236 0.338349 0.077740 0.245499
4 18.0 26.0 0.450 0.693650 2.046095 0.884932 0.341040 0.350653 0.073340 0.142131
5 18.0 12.0 0.450 0.693650 4.092190 1.427310 1.688148 0.615180 0.145213 1.292100
6 18.0 12.0 0.450 0.693650 4.092190 1.427310 1.688148 0.615180 0.145213 1.292100
7 18.0 12.0 0.450 0.693650 4.092190 1.427310 1.705200 0.615180 0.145213 1.292100
8 18.0 12.0 0.450 0.693650 4.092190 1.427310 1.398264 0.615180 0.146680 0.646050
9 18.0 12.0 0.450 0.693650 4.092190 1.427310 1.705200 0.615180 0.145213 1.292100
Solution
#Subset, convert to arrays, multiply and reconvert to df
pdata.update(pd.DataFrame(pdata.iloc[:,2:].to_numpy()*pseries.iloc[2:].to_numpy(), columns=pdata.iloc[:,2:].columns))
Assign the missing section of pdseries
pdata=pdata.assign(**pseries.iloc[:2])
Outcome
id cat s43 s44 s45 s46 s47 s40 pen \
0 26 18 1.099029 1.176588 0.473689 0.114410 0.865707 3.150986 0.45
1 26 18 0.570924 0.409248 0.227617 0.052805 0.180894 1.636876 0.45
2 26 18 0.827840 0.903756 0.313742 0.071873 0.439314 2.291626 0.45
3 26 18 0.799294 0.733236 0.338349 0.077740 0.245499 2.332548 0.45
4 26 18 0.884932 0.341040 0.350653 0.073340 0.142131 2.046095 0.45
5 12 18 1.427310 1.688148 0.615180 0.145213 1.292100 4.092190 0.45
6 12 18 1.427310 1.688148 0.615180 0.145213 1.292100 4.092190 0.45
7 12 18 1.427310 1.705200 0.615180 0.145213 1.292100 4.092190 0.45
8 12 18 1.427310 1.398264 0.615180 0.146680 0.646050 4.092190 0.45
9 12 18 1.427310 1.705200 0.615180 0.145213 1.292100 4.092190 0.45
rs
0 0.69365
1 0.69365
2 0.69365
3 0.69365
4 0.69365
5 0.69365
6 0.69365
7 0.69365
8 0.69365
9 0.69365
Answered By - wwnde
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.