Questions tagged [excel]
Excel is a spreadsheet based software, for numerical input, developed by Microsoft and part of the broader set of the Microsoft Office Suite.
74
questions
0
votes
0
answers
9
views
Write logs into excel sheet in sharepoint
I am trying to write data ("log" kind of data) onto an excel sheet in MS SharePoint.
I have previous experience in writing onto google spreadsheets using python. However, I don't know how to ...
4
votes
1
answer
183
views
How to interpret standard deviation calculated by excel
I have some percentage values in my excel spreadsheet like:
When i use the STDEV function to calculate the standard deviation with these values, i get ...
- 395
0
votes
0
answers
8
views
How can I create multiparametric diagram in excel or g.sheets?
So here is my data. For all of four segments I want to build a single diagram which contains all four parameters for each segment. How can it be done in excel or Google Sheets?
- 1
1
vote
1
answer
29
views
Markov Process and transition matrix
I would like to find some good courses but also a quick response on how to model transition matrix given the states.
Imagine having 4 states and the following array [1,2,4,1,3,4,2 etc etc]. What ...
- 106
0
votes
2
answers
64
views
How to find duplicate rows in a column then find out if two cells in another column sum up to a third cell in an Excel tab in Python?
I need to find all duplicate rows (string values) in "Name" column and then find out if two numerical values in "Amount" column sum up to a third value also in the "Amount&...
0
votes
0
answers
20
views
Setting Time and Date Format in Python
I have a dataset with a time column and a separate date column in .xlsx format.
time column has values in the below format:
12:32:21.499145197
12:32:21.499145197
date column has values in the below ...
- 13
0
votes
1
answer
52
views
Deleting a Column in a csv or Excel file using Pandas
I am trying to delete a column from my csv file (column 'A' called "Film Number") but have tried numerous variations of code and while it deletes the column in the dataFrame it doesn't do so ...
- 1
1
vote
1
answer
29
views
How to make the values in different columns in the correct order based on another data frame (mapping) in Python Pandas
I am pretty new to Python and Pandas and I struggle with combining a messy dataframe from excel with a mapping. I have tried to find some solutions on the Internet, however with no success.
My first ...
- 11
0
votes
0
answers
27
views
Retail Channel Performance Analysis
I have some real time sales and revenue data for a retailer for each day across customer type, state and channel. Sample data shared below:
Sales
Date
Region
State
Customer Type
Sales
Channel
1/1/...
- 1
0
votes
1
answer
29
views
How can I extract an optimized matrix of correlations from a larger data set?
Consider an Excel sheet containing a matrix of correlations between individual stocks and the combined portfolio as a whole:
How can I extract an optimized matrix such that most stocks have a low ...
- 103
1
vote
0
answers
19
views
Why is a column detected as text and not as categorical when opening excel file?
I have the following values in an excel sheet
Aguascalientes
Baja California
Baja California Sur
Campeche
Chiapas
Chihuahua
Ciudad de México
Coahuila
Colima
Durango
Estado de México
Guanajuato
...
1
vote
0
answers
12
views
Evaluation of reliability of suppliers by on time delivery
I am trying to create a weight that can help me properly evaluate the reliability of a company’s on time delivery ratios. I’m using historic delivery data. For example, comparing company A that ...
- 11
0
votes
3
answers
85
views
Calculating optimal 'phase shift' when comparing two linear datasets to maximise correlation?
Let's say I have two columns of data, when graphed they look like this:
This does not have a particularly wonderful correlation.
On the other hand if I shift one graph 10 datapoints relative to the ...
- 53
0
votes
0
answers
13
views
Importing data directly into excel sheet, from data accessed through Material Project opensource API's
I had a general doubt. If I want to extract some data, say the information regarding a particular chemical using API of material projects(a site that has open-source info on, say elements) available ...
- 1
1
vote
0
answers
43
views
How to maximize a log linear regression equation satisfying a constraint?
I have a log linear equation of the form $y = w_1(\log{X1}) + w_2(\log{X2}) + ... + w_n(\log{Xn})$.
How can I find the value of X's that maximize the value of y subject to a constraint $(X_1+X_2+...+...
- 11
0
votes
0
answers
17
views
I want to prepare meaningful charts and polarization chart with the data I have
I am a bachelor student that is preparing a project for my network science course. I have some data that I got via questionnaire. This data shows people that they prefer studying online or in presence,...
- 101
1
vote
0
answers
21
views
Using VLOOKUP to match each value of cell (cell has multiple values separated by comma) in table A with values in table B
Problem Statement:
Table 1: It has multiple values in each cell of Column B.
Table 2: Is the reference table Employees and their category is mentioned
Output Required:
Table 3: This is what the ...
- 11
0
votes
0
answers
1k
views
Excel error may be caused by pandas writing or big data? advise needed
I am reading multiple xml files extracting some data then forming a pandas Dataframe with my data. These are the main steps that I do:
open an xml file
extract some elements
create a pandas dataframe ...
- 1
-1
votes
1
answer
850
views
How can I get all column values from a panda dataframe and save them into one string [closed]
this question has been ask couple of times but none of the answer is working for that what I want.
For example I have this excel Dokument:
...
- 27
0
votes
3
answers
100
views
Perform fuzzy lookup to partially match the address column
I have two separate datasets (eg. dataset1.xlsx and dataset2.xlsx). Dataset1 has 2 columns, serial number and service address. Similarly dataset2 has 2 columns service address and customer number. The ...
2
votes
1
answer
42
views
Cleaning data with two fields mixed in the same column?
I am working on a template for a dashboard that is giving me some serious trouble. The format the data comes in is highly unstructured. In the image below, circled items are "Site Names", ...
- 143
0
votes
2
answers
128
views
Calculating Median and Mode of numerical variables for different subgroups in R
I have customer call data and I want to get the median and mode for the call success rate for different subgroups.
My variables are: Customer ID, Employment Status (Retired, Employed, Unemployed), ...
- 53
0
votes
1
answer
46
views
CSVs: how to output missing data to make processing easier?
If supplying data (that can be either string or numeric) via CSVs, what's a good strategy for marking that a value is missing?
Some non-empty sentinel, like NA
...
- 103
1
vote
1
answer
1k
views
Help improving my "read_excel" execution time in python. My code reads slowly
My first question here so please bare with me.
I'm trying to feed my neural network with training data read in from an excel file. It works perfectly fine when i have less than 50 rows in the sheet. ...
- 13
0
votes
2
answers
41
views
How does Excel obtain the R² determination coefficient in an exponetial regression?
How does Excel obtain the R² determination coefficient in an exponetial regression? This may seem a silly question but Excel actually shows a R² coefficient for non-linear regressions. How does Excel ...
- 101
0
votes
1
answer
482
views
I have a pandas dataframe and i need to clear all the special characters other than space
Input:
import pandas as pd
df=pd.read_excel("OCRFinal.xlsx")
df['OCR_Text']=df['OCR_Text'].str.replace(r'\W+'," ")
print(df['OCR_Text'])
Output:
The excel ...
- 33
1
vote
1
answer
144
views
Comparing excel data sets in Pandas
Pretty new to Python, but as an SEO I'm looking at the benefits of using notebooks in my workflow.
I've got two excel files which I've cleaned and imported into a new notebook using pandas.
I'm ...
1
vote
0
answers
20
views
On ttest why t-stat is greater than one?
I have my data in two culomns as below:
On excel did this ttest and this is my result:
I can not Understand why Hypothesized Mean Difference =0 but tStat = -1.6367...?
I mean my NULL/Zero ...
- 221
1
vote
1
answer
23
views
How to group by ID, and output without repeating IDs? [closed]
Input
UserID Profile
101 apple
102 banana
103 pie
101 banana
102 pine
103 berry
Output
...
- 11
1
vote
1
answer
69
views
Relationship between two sets of dates
My wife has seizures a few times a month and takes medication for the seizures once a month. So I have two sets of dates - the dates of the seizures and the dates she got her medication.
I'm trying ...
- 11
3
votes
2
answers
4k
views
Pandas datetime error when reading from excel file
I am trying to read an excel file that has two columns using pandas.
This is how the data looks in excel file:
...
- 172
0
votes
3
answers
161
views
What is the better to pre-process data, MS-Excel or Python/R [closed]
While handling data, many anomalies occur that might change the results. For example, outliers, missing values, etc. So in this context, what is the better tool to pre process data? A visual tool like ...
2
votes
0
answers
195
views
Tools for reading data from large, irregular csv files (aka excel file hell)
I have a csv file with 1000 columns and 50 rows that was collected over a few months. Embedded randomly in the file are ~1000 small datasets with semi-standard formats (some columns differ, so does ...
- 141
1
vote
1
answer
172
views
Starting search from rightmost column and moving left pandas
I'm very new to pandas and I'm trying to implement the code to search for something on an excel spreadsheet.
Basically, the most recent price can be found on the furthest right column named ...
- 21
1
vote
1
answer
779
views
how to implement Excel vlookup on Python
I have a dataset A and dataset B, both data has a column in common which is the ID, i want to check if dataset A ID values can be found in dataset B ID values. Also get the records of dataset B that ...
0
votes
1
answer
2k
views
VlookUp: how to loop it? [closed]
I am not an experienced user of Excel because I usually use python but I am obliged to use it this time.
This is the big picture of my case.
The problem is:
I have a sheet with a column with some ...
- 135
2
votes
1
answer
111
views
Adding Group Average Line to Bar Chart
I am trying to create a PivotChart in the form of a bar chart and I would like to add a group average line on the chart.
I have this (sample) table in my PowerPivot Data Model:
Group averages are A: ...
- 23
1
vote
1
answer
5k
views
Changing Timestamp format for Date-Time in Excel/Pandas/Python?
I have a excel data with time stamp format like this "2019-06-10T14:05:00+05:30"
[YYYY-MM-DDTHH:MM:SS], I want it to be converted into "2019-06-10 14:05" [YYYY-MM-DD HH:MM],
Is it possible to convert ...
- 172
2
votes
5
answers
98
views
30 Years Of Excel Test Data
I am a CS intern at an industrial company that has 30 years of excel files that need to be analyzed. Looking at the data, only a fraction of the files need to be looked at and used. After those files ...
1
vote
1
answer
38
views
Different t-test results from R and LibreCalc
while I was trying to apply one-tailed t-test to two vector of data on R, I just wanted to apply same thing on librecalc also and then I noticed that p-values are different. Here is a summary what I ...
- 113
0
votes
4
answers
100
views
How should we manage large database
We currently manage a large volume of economic data in Excel in my organisation. All of the data is downloaded from different online databases into Excel spreadsheets (one for each data frequency ...
- 1
2
votes
1
answer
45
views
Analyze additions and subtractions to/from a data set
I have a data set in the following form:
...
- 121
1
vote
1
answer
263
views
Importing Excel format data into R/R Studio and using glmnet package?
I have no problem importing Excel formatted data into R/R Studio and use all other R packages that I use. But, when I want to use the glmnet package to develop a regularization model, I invariably ...
- 121
1
vote
0
answers
14k
views
Pandas Excel writer update existing excel file [closed]
How do I update an excel file and not overwrite it?
...
- 11
11
votes
4
answers
54k
views
How to Write Multiple Data Frames in an Excel Sheet
I have multiple data frames with same column names. I want to write them together to an excel sheet stacked vertically on top of each other. And between each, there will be a text occupying a row. ...
- 283
0
votes
1
answer
1k
views
Categorical data into numeric in excel
I have a large dataset and I would like to convert these categorical data into numeric in binary form to perform k means clustering in R. However, I get an error in value. This is the formula that I ...
- 27
3
votes
2
answers
139
views
Expectations of the skill set of a Data Scientist [closed]
Although there are more powerful tools than Excel which a data scientist should know how to use, would it be expected that someone indicating they are a Data Scientist know how to use Excel?
- 31
1
vote
0
answers
62
views
Excel All Possible Combinations for MacOS
Is there a formula in Excel to calculate all possible combinations for multiple lists for MacOS. For instance, I am trying to draft a fantasy team and I have the following combinations:
...
4
votes
3
answers
1k
views
How to predict whether or not a customer will renew
I have a dataset of customer contracts that specify a start date and if applicable an end date. Each month a customer is up for renewal. Below is an example of how the data is organized in excel:
<...
- 41
1
vote
1
answer
297
views
Drop NA Values with SAS
I have an xlsx file that I wish to delete the NA values in SAS. I'm new to ...
- 135