Problem in Pandas: Merging correctly & iteratively averaging whilst loading into df?
0
I have a problem with my data set that I have merged. I have two sets, of which I have to combine on a specific key identifier, called "msno". Not all values are present, and someone can be in the dataset multiple times.
Code example:
colnamesa = ['msno','date','num_25','num_50','num_75','num_985']
colnamesb = ['msno','city','bd','gender',
'registered_via','registration_init_time']
a = pandas.read_csv('userlogs.csv', names= colnamesa, skiprows=[0])
b = pandas.read_csv('members.csv', names= colnamesb,skiprows=[0])
c = a.merge(b, how='outer', on ='msno')
df = c.dropna(thresh=4)`
Output
msno date num_25 num_50 num_75 num_985 num_100 num_unq total_secs city bd gender registered_via registration_init_time
0 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170331.0 8.0 4.0 0.0 1.0 21.0 18.0 6309.273 1.0 0.0 NaN 7.0 20161220.0
1 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170316.0 6.0 4.0 1.0 3.0 26.0 31.0 7926.107 1.0 0.0 NaN 7.0 20161220.0
2 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170325.0 6.0 4.0 2.0 1.0 65.0 58.0 17148.343 1.0 0.0 NaN 7.0 20161220.0
3 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170310.0 10.0 2.0 1.0 5.0 35.0 39.0 10519.150 1.0 0.0 NaN 7.0 20161220.0
4 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170328.0 101.0 1.0 3.0 6.0 34.0 80.0 11046.850 1.0 0.0 NaN 7.0 20161220.0
5 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170307.0 13.0 2.0 3.0 2.0 45.0 55.0 12581.496 1.0 0.0 NaN 7.0 20161220.0
6 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170321.0 13.0 3.0 2.0 1.0 41.0 31.0 11806.946 1.0 0.0 NaN 7.0 20161220.0
7 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170315.0 14.0 7.0 3.0 11.0 24.0 41.0 10153.821 1.0 0.0 NaN 7.0 20161220.0
8 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170330.0 0.0 0.0 1.0 0.0 24.0 2.0 5773.754 1.0 0.0 NaN 7.0 20161220.0
Desired output
For all of the entries with the same msno (they are the same person), I would like to average the scores in num_25,...., total_seconds, but not the date. Is this doable?
python pandas merge perceptron
add a comment |
0
I have a problem with my data set that I have merged. I have two sets, of which I have to combine on a specific key identifier, called "msno". Not all values are present, and someone can be in the dataset multiple times.
Code example:
colnamesa = ['msno','date','num_25','num_50','num_75','num_985']
colnamesb = ['msno','city','bd','gender',
'registered_via','registration_init_time']
a = pandas.read_csv('userlogs.csv', names= colnamesa, skiprows=[0])
b = pandas.read_csv('members.csv', names= colnamesb,skiprows=[0])
c = a.merge(b, how='outer', on ='msno')
df = c.dropna(thresh=4)`
Output
msno date num_25 num_50 num_75 num_985 num_100 num_unq total_secs city bd gender registered_via registration_init_time
0 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170331.0 8.0 4.0 0.0 1.0 21.0 18.0 6309.273 1.0 0.0 NaN 7.0 20161220.0
1 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170316.0 6.0 4.0 1.0 3.0 26.0 31.0 7926.107 1.0 0.0 NaN 7.0 20161220.0
2 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170325.0 6.0 4.0 2.0 1.0 65.0 58.0 17148.343 1.0 0.0 NaN 7.0 20161220.0
3 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170310.0 10.0 2.0 1.0 5.0 35.0 39.0 10519.150 1.0 0.0 NaN 7.0 20161220.0
4 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170328.0 101.0 1.0 3.0 6.0 34.0 80.0 11046.850 1.0 0.0 NaN 7.0 20161220.0
5 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170307.0 13.0 2.0 3.0 2.0 45.0 55.0 12581.496 1.0 0.0 NaN 7.0 20161220.0
6 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170321.0 13.0 3.0 2.0 1.0 41.0 31.0 11806.946 1.0 0.0 NaN 7.0 20161220.0
7 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170315.0 14.0 7.0 3.0 11.0 24.0 41.0 10153.821 1.0 0.0 NaN 7.0 20161220.0
8 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170330.0 0.0 0.0 1.0 0.0 24.0 2.0 5773.754 1.0 0.0 NaN 7.0 20161220.0
Desired output
For all of the entries with the same msno (they are the same person), I would like to average the scores in num_25,...., total_seconds, but not the date. Is this doable?
python pandas merge perceptron
Please provide some sample of your dataframes so we can reproduce your code
– yatu
Nov 26 '18 at 12:45
Output is quite difficult to format here, but I'll provide it in the OP. @nixon
– user10702603
Nov 26 '18 at 12:47
There is no attempt at the averaging here. So what is the problem? Read docs on pandas'groupby.
– Parfait
Nov 26 '18 at 17:45
add a comment |
0
0
0
I have a problem with my data set that I have merged. I have two sets, of which I have to combine on a specific key identifier, called "msno". Not all values are present, and someone can be in the dataset multiple times.
Code example:
colnamesa = ['msno','date','num_25','num_50','num_75','num_985']
colnamesb = ['msno','city','bd','gender',
'registered_via','registration_init_time']
a = pandas.read_csv('userlogs.csv', names= colnamesa, skiprows=[0])
b = pandas.read_csv('members.csv', names= colnamesb,skiprows=[0])
c = a.merge(b, how='outer', on ='msno')
df = c.dropna(thresh=4)`
Output
msno date num_25 num_50 num_75 num_985 num_100 num_unq total_secs city bd gender registered_via registration_init_time
0 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170331.0 8.0 4.0 0.0 1.0 21.0 18.0 6309.273 1.0 0.0 NaN 7.0 20161220.0
1 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170316.0 6.0 4.0 1.0 3.0 26.0 31.0 7926.107 1.0 0.0 NaN 7.0 20161220.0
2 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170325.0 6.0 4.0 2.0 1.0 65.0 58.0 17148.343 1.0 0.0 NaN 7.0 20161220.0
3 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170310.0 10.0 2.0 1.0 5.0 35.0 39.0 10519.150 1.0 0.0 NaN 7.0 20161220.0
4 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170328.0 101.0 1.0 3.0 6.0 34.0 80.0 11046.850 1.0 0.0 NaN 7.0 20161220.0
5 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170307.0 13.0 2.0 3.0 2.0 45.0 55.0 12581.496 1.0 0.0 NaN 7.0 20161220.0
6 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170321.0 13.0 3.0 2.0 1.0 41.0 31.0 11806.946 1.0 0.0 NaN 7.0 20161220.0
7 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170315.0 14.0 7.0 3.0 11.0 24.0 41.0 10153.821 1.0 0.0 NaN 7.0 20161220.0
8 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170330.0 0.0 0.0 1.0 0.0 24.0 2.0 5773.754 1.0 0.0 NaN 7.0 20161220.0
Desired output
For all of the entries with the same msno (they are the same person), I would like to average the scores in num_25,...., total_seconds, but not the date. Is this doable?
python pandas merge perceptron
I have a problem with my data set that I have merged. I have two sets, of which I have to combine on a specific key identifier, called "msno". Not all values are present, and someone can be in the dataset multiple times.
Code example:
colnamesa = ['msno','date','num_25','num_50','num_75','num_985']
colnamesb = ['msno','city','bd','gender',
'registered_via','registration_init_time']
a = pandas.read_csv('userlogs.csv', names= colnamesa, skiprows=[0])
b = pandas.read_csv('members.csv', names= colnamesb,skiprows=[0])
c = a.merge(b, how='outer', on ='msno')
df = c.dropna(thresh=4)`
Output
msno date num_25 num_50 num_75 num_985 num_100 num_unq total_secs city bd gender registered_via registration_init_time
0 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170331.0 8.0 4.0 0.0 1.0 21.0 18.0 6309.273 1.0 0.0 NaN 7.0 20161220.0
1 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170316.0 6.0 4.0 1.0 3.0 26.0 31.0 7926.107 1.0 0.0 NaN 7.0 20161220.0
2 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170325.0 6.0 4.0 2.0 1.0 65.0 58.0 17148.343 1.0 0.0 NaN 7.0 20161220.0
3 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170310.0 10.0 2.0 1.0 5.0 35.0 39.0 10519.150 1.0 0.0 NaN 7.0 20161220.0
4 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170328.0 101.0 1.0 3.0 6.0 34.0 80.0 11046.850 1.0 0.0 NaN 7.0 20161220.0
5 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170307.0 13.0 2.0 3.0 2.0 45.0 55.0 12581.496 1.0 0.0 NaN 7.0 20161220.0
6 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170321.0 13.0 3.0 2.0 1.0 41.0 31.0 11806.946 1.0 0.0 NaN 7.0 20161220.0
7 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170315.0 14.0 7.0 3.0 11.0 24.0 41.0 10153.821 1.0 0.0 NaN 7.0 20161220.0
8 u9E91QDTvHLq6NXjEaWv8u4QIqhrHk72kE+w31Gnhdg= 20170330.0 0.0 0.0 1.0 0.0 24.0 2.0 5773.754 1.0 0.0 NaN 7.0 20161220.0
Desired output
For all of the entries with the same msno (they are the same person), I would like to average the scores in num_25,...., total_seconds, but not the date. Is this doable?
python pandas merge perceptron
python pandas merge perceptron
edited Nov 26 '18 at 17:13
asked Nov 26 '18 at 12:37
user10702603
Please provide some sample of your dataframes so we can reproduce your code
– yatu
Nov 26 '18 at 12:45
Output is quite difficult to format here, but I'll provide it in the OP. @nixon
– user10702603
Nov 26 '18 at 12:47
There is no attempt at the averaging here. So what is the problem? Read docs on pandas'groupby.
– Parfait
Nov 26 '18 at 17:45
add a comment |
Please provide some sample of your dataframes so we can reproduce your code
– yatu
Nov 26 '18 at 12:45
Output is quite difficult to format here, but I'll provide it in the OP. @nixon
– user10702603
Nov 26 '18 at 12:47
There is no attempt at the averaging here. So what is the problem? Read docs on pandas'groupby.
– Parfait
Nov 26 '18 at 17:45
Please provide some sample of your dataframes so we can reproduce your code
– yatu
Nov 26 '18 at 12:45
Please provide some sample of your dataframes so we can reproduce your code
– yatu
Nov 26 '18 at 12:45
Output is quite difficult to format here, but I'll provide it in the OP. @nixon
– user10702603
Nov 26 '18 at 12:47
Output is quite difficult to format here, but I'll provide it in the OP. @nixon
– user10702603
Nov 26 '18 at 12:47
There is no attempt at the averaging here. So what is the problem? Read docs on pandas'
groupby.– Parfait
Nov 26 '18 at 17:45
There is no attempt at the averaging here. So what is the problem? Read docs on pandas'
groupby.– Parfait
Nov 26 '18 at 17:45
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53481289%2fproblem-in-pandas-merging-correctly-iteratively-averaging-whilst-loading-into%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53481289%2fproblem-in-pandas-merging-correctly-iteratively-averaging-whilst-loading-into%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Please provide some sample of your dataframes so we can reproduce your code
– yatu
Nov 26 '18 at 12:45
Output is quite difficult to format here, but I'll provide it in the OP. @nixon
– user10702603
Nov 26 '18 at 12:47
There is no attempt at the averaging here. So what is the problem? Read docs on pandas'
groupby.– Parfait
Nov 26 '18 at 17:45